Precise 1Z0-064 Study Guides 2021

NEW QUESTION 1
Examine the command to change a parameter value from the default to 50: SQL> ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ = 50;
What is the effect of changing the value of the parameter? (Choose the best answer.)

• A. It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced.
• B. It influences the optimizer to use bitmap indexes as the estimated cost of conversion from bitmap to rowid is reduced.
• C. It influences the optimizer to always use fast full index scans as the estimated cost of using an index is reduced.
• D. It influences the optimizer to use indexes instead of full table scans as the estimated cost of using an index is reduced.

Answer: A

Explanation: Reference: http://www.dba-oracle.com/oracle_tips_cost_adj.htm

NEW QUESTION 2
In which three situations does DB time always increase? (Choose three.)

• A. when the host is CPU bound for foreground processes
• B. when I/O wait time increases for foreground processes
• C. when more connections are made to a database instance
• D. when CPU consumption by background processes increases
• E. when wait time for data to be sent over a network increases

Answer: ABC

Explanation: Reference: http://www.oracle.com/technetwork/oem/db-mgmt/s317294-db-perf-tuning-with- db-time-181631.pdf (page 21)

NEW QUESTION 3
Which three methods can you use to create a pre-change SQL trial to capture performance data by using SQL Performance Analyzer? (Choose three.)

• A. executing SQL statements in an SQL Tuning Set (STS) on a test database by using database links to the production database.
• B. generating only execution plans on a test database without actually running SQL statements.
• C. generating an execution plan and statistics for selective SQL statements captured in an STS
• D. loading performance data and execution plans from an STS.
• E. generating both execution plans and statistics for each SQL statement in an STS by actually running the SQL statements on a test database.

Answer: BDE

Explanation: Reference: https://docs.oracle.com/cd/E11882_01/server.112/e41481/spa_pre_change.htm#RATUG1 81

NEW QUESTION 4
Examine the partial TKPROF output for an SQL statement:

Which two inferences can definitely be made from this output? (Choose two.)

• A. Array fetch operations were not performed for this query.
• B. No hard parse was performed for this query.
• C. The number of logical I/Os is almost equal to the number of physical I/Os.
• D. Another transaction held a shared lock on the table, thereby causing a significant delay.

Answer: BD

NEW QUESTION 5
Which two actions can reduce library cache latch contention for an OLTP application that repeatedly executes queries containing a mix of literals and bind variables? (Choose two.)

• A. setting the OPEN_CURSORS parameter to hold a sufficient number of concurrently open cursors
• B. coding the application such that a frequently executed statement is parsed only onceand executed repeatedly as required
• C. setting the CURSOR_SHARING parameter to EXACT
• D. avoiding the granting of privileges on objects associated with cursors during peak load
• E. enabling Automatic Memory Management and allocating at least 50% of the available memory for SHARED_POOL_SIZE
• F. configuring shared server connections

Answer: BE

Explanation: Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28274/memory.htm

NEW QUESTION 6
Which two statements are true about Compare Period ADDM? (Choose two.)

• A. It is automatically invoked whenever the AWR Compare Period report is invoked.
• B. It is automatically invoked whenever ADDM is run by default.
• C. It verifies if there is any change in the workload or average resource consumption by the SQL executed during the two specified time periods, to ensure 100% accuracy.
• D. It can be used to create a comparison report between the Database Replay workload capture report and the replay report.

Answer: CD

NEW QUESTION 7
Examine the parameters:

Your database supports a mixed workload and users have dedicated server connections. Users complain about the increased response time of a few queries that are performing large sort operations. On investigation, you notice an increase in the number of multipass work area executions and high number of direct path write wait events.
Which two actions could improve the performance? (Choose two.)

• A. increasing the value of the SORT_AREA_SIZE parameter
• B. increasing the value of the PGA_AGGREGATE_TARGET parameter
• C. enabling Automatic Memory Management for the instance
• D. increasing the size of the default temporary tablespace
• E. using parallel hint in queries performing large sort operations
• F. enabling Automatic Shared Memory Management for the instance

Answer: AF

NEW QUESTION 8
Examine the parameters set for a database instance supporting a mixed workload:

The database instance supports shared server and dedicated server connections simultaneously. Users complain about increased response times of a few DSS queries. During investigation, you execute the queries:

Based on the output, which two courses of action would you recommend to improve query performance? (Choose two.)

• A. Use a parallel hint in the queries.
• B. Increase the number of DBWn processes.
• C. Increase the value of the SORT_AREA_SIZE initialization parameter.
• D. Increase the size of the temporary tablespace or add a new temporary tablespace.
• E. Increase the value of the PGA_AGGREGATE_TARGET initialization parameter.
• F. Increase the size of the large pool.

Answer: CF

NEW QUESTION 9
Identify two effects of the DB_FILE_MULTIBLOCK_READ_COUNT parameter on the optimizer. (Choose two.)

• A. Decreasing the value of DB_FILE_MULTIBLOCK_READ_COUNT from the default increases the cost of index probes for DSS workloads.
• B. A full table scan can become cheaper than index scans if the database instance has a high enough DB_FILE MULTIBLOCK_READ_COUNT for both OLTP and DSS workloads.
• C. Increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT within OS limits lowers the costing of an index probe that is done in conjunction with a nested loop for OLTP workloads.
• D. In DSS workloads where full table scans may run in parallel and bypass the buffer cache, decreasing the value of DB_FILE_MULTIBLOCK_READ_COUNT from the default increases the cost of full table scans.
• E. Increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT within OS limits lowers the cost of full table scans and can result in the optimizer choosing a full table scan over an index scan for both OLTP and DSS workloads.

Answer: BE

NEW QUESTION 10
You want to generate statistics for new objects added to an OLTP application without affecting old statistics and the sessions that currently use them.
Which two tasks would you perform to test the new statistics? (Choose two.)

• A. Set the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to TRUE for the session.
• B. Set the STALE_PERCENT statistics preference to zero and then gather statistics.
• C. Set the PUBLISH statistics preference to FALSE and then gather statistics.
• D. Use the DBMS_STATS.PUBLISH_PENDING_STATS procedure to make pending statistics the current statistics.
• E. Set the NO_INVALIDATE statistics preference to FALSE and gather statistics without affecting old statistics.

Answer: AB

NEW QUESTION 11
Which three statements are true about using Real-Time Database Operations? (Choose three.)

• A. The STATISTICS_LEVEL initialization parameter must be set to ALL to enable automatic SQL monitoring for all long-running queries.
• B. The CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter must be set to DIAGNOSTIC+TUNUNG to use Real-Time Database Operations.
• C. The STATISTICS_LEVEL initialization parameter can be set to TYPICAL or ALL to enable Real-Time Database Operations.
• D. Real-Time Database Operations can be enabled only at the system level.
• E. Real-Time Database Operations can be created by using the DBMS_MONITOR or DBMS_SESSION packages.
• F. Database operation monitoring starts automatically when a database operation consumes at least five seconds of the CPU or I/O time in a single execution.

Answer: BCF

NEW QUESTION 12
You are administering a database that supports a mixed workload. You upgrade your database from Oracle Database 11g to 12c and after the upgrade, users complain about degraded performance of some queries. The SQL plan baselines imported from the previous version are present for the queries and are loaded to the SQL Management Base as accepted plans. On further investigation, you find that better plans are generated but not used by the optimizer.
Examine the parameters set for the instance:

Which three tasks would you perform to improve the performance of these queries? (Choose three.)

• A. Gather statistics for the objects used in the queries.
• B. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve new plans and fix the plans for the statements.
• C. Create an SQL Tuning Set (STS) and run it through the SQL Access Advisor to generate recommendations.
• D. Create an STS and run it through the SQL Tuning Advisor to generate recommendations.
• E. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE.
• F. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to alter the acceptedplans as fixed plans.

Answer: ABC

NEW QUESTION 13
Examine the parameters set for a database instance:

The database supports a mixed workload. Users complain about the increased response time of a few DSS queries. During investigation, you execute the query:

Based on the output, which two are possible ways to improve the performance of the queries? (Choose two.)

• A. Enable temporary undo.
• B. Enable Automatic Memory Management.
• C. Increase the number of DBWn processes.
• D. Enable Automatic Shared Memory Management.
• E. Increase the value of the SGA_TARGET parameter.
• F. Increase the value of the PGA_AGGREGATE_TARGET parameter.

Answer: CE

NEW QUESTION 14
A senior DBA asks you to decrease the values of the connect_time_scale and think_time_scale replay processing parameters to 50 to preprocess the workload for replay.
What three could be reasons for this change? (Choose three.)

• A. to reduce the elapsed time between two successive user calls from a session.
• B. to decrease the number of concurrent users during replay
• C. to increase the number of concurrent users during replay
• D. to reduce the time of replay
• E. to decrease the wait for a query, caused by noncommitted transactions

Answer: CDE

NEW QUESTION 15
Examine the output of the query executed to diagnose the reason for performance degradation of queries:

Which three factors will you investigate further to identify the cause of the performance degradation? (Choose three.)

• A. Check the number of disk sorts.
• B. Check for the causes of the full table scans.
• C. Check the number of chained or migrated rows.

Answer: ABC

NEW QUESTION 16
Your database supports a DSS workload. In an application, a few complex queries that contain multiple functions and expressions are using materialized views. You notice that some queries are performing poorly because they are not benefiting from query rewrites.
Which three actions would you take to improve the performance of queries? (Choose three.)

• A. Create an SQL Tuning Set (STS) and submit as input to the SQL Access Advisor to generate recommendations about query rewrite and fast refresh for materialized views.
• B. Use the DBMS_MVIEW.EXPLAIN_REWRITE procedure to analyze why a query failed to rewrite.
• C. Create an STS and submit as input to the SQL Performance Analyzer to get recommendations about improving the performance of queries.
• D. Use the DBMS_ADVISOR.TUNE_MVIEW procedure to get recommendations about rewriting materialized views.
• E. Use the DBMS_ADVISOR.QUICK_TUNE procedure to analyze queries based on the usage of query rewrite with materialized views.

Answer: ACE

NEW QUESTION 17
Examine the query and its output:

Which two statements are true? (Choose two.)

• A. Session 2832 had to wait 2029 seconds for a message to arrive because of a network bottleneck.
• B. Session 4208 is either idle or experiencing poor response time due to a network or resource bottleneck on the client process.
• C. Session 3346 is in wait state because it wants to lock a row in a block in which other sessions have already locked rows, and there is no free ITL slot available in this block.
• D. Session 3346 is in wait state because either it is waiting to update a row that is locked by another session or another session is trying to insert the same key value in a UNIQUE index.
• E. Session 4208 is definitely idle and should be killed to free network resources.

Answer: AD