Oracle Oracle Database 1Z0-064 Questions & Answers

• Question 41:

  You are administering a database that supports a mixed workload. The CURSOR_SHARING parameter is set to the default value. While analyzing the latest Automatic Workload Repository (AWR) report, you find a large number of cursor: pin S wait on X, cursor: pin X wait on S, and library cache mutex waits in the Top 10 foreground events section. Examine the Instance Efficiency Percentages section in the AWR report:

  

  Which three statements are true in this scenario? (Choose three.)

  A. Sessions are waiting for mutexes in share mode on cursors but other sessions are holding the mutexes in exclusive mode.

  B. The CPU is spending more time in finding cursors in the library cache.

  C. Cursors are not getting shared, resulting in a large number of hard parses.

  D. Sessions are waiting for mutexes in exclusive mode on cursors but other sessions are holding the mutexes in share mode.

  E. The buffers required by queries are not found in the buffer cache, thereby increasing expensive disk I/

  O.

  Correct Answer: BDE

• Question 42:

  Your database supports an online transaction processing (OLTP) workload. The database uses ASM storage. One of the ASM disks goes offline because of hardware failure. When the disk is replaced and then added back to the diskgroup, database performance is affected by rebalance operations.

  Which two actions would you recommend to lower the impact of rebalance operations on the performance of the database? (Choose two.)

  A. Increase the number of ASMB processes.

  B. Decrease the value of the ASM_POWER_LIMIT parameter.

  C. Set the DISK_REPAIR_TIME disk attribute to a lower value.

  D. Specify the POWER clause with a lower value in an ALTER DISKGROUP statement.

  E. Set the DISK_REPAIR_TIME disk attribute to a higher value.

  Correct Answer: BD

• Question 43:

  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 a 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

  Correct Answer: AF

• Question 44:

  Examine the partial Activity Over Time section of an Active Session History (ASH) report:

  

  Which two inferences are correct? (Choose two.)

  A. In the first time slot, five different sampled sessions were connected to the database instance.

  B. In the second time slot, out of the nine sampled sessions connected to the database instance, only one sampled session was idle at the time of report generation.

  C. In the first time slot, only one sampled session was using the CPU.

  D. In the second time slot, five different sampled sessions were using the CPU.

  E. In the second time slot, 0.14% of the time was spent on the CPU.

  Correct Answer: AE

• Question 45:

  You are administering a database that supports an OLTP workload. RESULT_CACHE_MODE is set to the default value and a result cache is configured for the instance. Multiple sessions execute syntactically similar queries without dblinks, containing functions and expressions, on tables with no DML activity. Some users complain about poor performance of these queries.

  You investigate and find that the queries are frequently performing physical I/O, even though the results fetched by the queries are similar.

  Which two actions do you recommend to overcome the problem affecting these queries? (Choose two.)

  A. Set the RESULT_CAHCE_MODE parameter to FORCE for the instance.

  B. Use the result cache hint in the queries.

  C. Use bind variables for similar queries instead of literals.

  D. Set the RESULT_CACHE_REMOTE_EXPIRATION parameter to a nonzero value.

  E. Configure the KEEP pool and cache the queried tables used in the KEEP pool.

  Correct Answer: AB

• Question 46:

  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

  Correct Answer: CDE

• Question 47:

  Examine the parameters set for your database instance: You are administering a database that supports an OLTP workload. Users complain about the degraded performance of some queries. While diagnosing, you notice a large number of hard parses occurring for several syntactically almost identical SQL statements that differ only in literal values in the WHERE clause.

  

  Which two actions would you recommend to improve performance? (Choose two.)

  A. Create the KEEP cache and cache the tables used in the queries.

  B. Set the CURSOR_SHARING parameter to FORCE.

  C. Use bind variables instead of literals.

  D. Create SQL plan baselines for the almost identical SQL statements and load them into the cursor cache.

  E. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE.

  Correct Answer: BE

• Question 48:

  Which three actions should you perform to reduce shared pool fragmentation and avoid the "ORA-04031: unable to allocate .... bytes of shared memory" error for the shared pool? (Choose three.)

  A. Configure the Server Result Cache.

  B. Configure shared server mode.

  C. Identify the packages or procedures that are causing the "ORA-04031:unable to allocate....bytes of shared memory" error and use the DBMS_SHARED_POOL.KEEP to keep them in the shared pool.

  D. Use DBMS_SHARED_POOL.KEEP to keep the SYS.STANDARD, SYS.DBMS_STANDARD, and SYS.DIUTIL packages and frequently executed compiled triggers.

  E. Use more anonymous procedures.

  F. Standardize the type, size, and naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks.

  Correct Answer: ABD

• Question 49:

  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 a 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 accepted plans as fixed plans.

  Correct Answer: ABC

• Question 50:

  Examine the partial PLAN_TABLE output:

  

  Which is the correct sequence of execution? (Choose the best answer.)

  A. 3, 2, 1, 4, 0

  B. 0, 1, 2, 3, 4, 1

  C. 0, 4, 1, 3, 2, 1

  D. 3, 2, 4, 1, 0

  E. 3, 2, 4, 1, 0, 2

  Correct Answer: A