Oracle Oracle Database 1Z0-064 Questions & Answers

• Question 31:

  The SALES table has over one million rows. Statistics for the SALES table and its dependent objects are gathered by the Oracle AutoTask task during a predefined maintenance window.

  Examine the query:

  

  Which two actions can improve the accuracy of cardinality estimates for this query?

  A. ensuring that histograms exist for the CUST_ID and PROD_ID columns

  B. setting the OPTOMIZER_DYNAMIC_SAMPLING parameter to 4 to augment the default standard statistics

  C. setting the FORCE parameter of the GATHER_*_STATS procedure to TRUE and gathering statistics for the SALES table and its dependent objects

  D. gathering system statistics during peak workload time to augment the standard statistics

  Correct Answer: AD

• Question 32:

  Which three statements are true about the TEST EXECUTE method of the SQL Performance Analyzer?

  A. The SQL statements in the STS are compared only on elapsed time for the pre-change and post-change trials.

  B. The execution statistics generated for SQL statements are analyzed to check whether they have improved or regressed.

  C. An execution plan and statistics are generated for SQL statements only in the pre-change trial.

  D. The SQL statements in the SQL Tuning Set (STS) are executed at least twice for both pre-change and post-change trials.

  E. An execution plan and statistics are computes for each SQL statement in the STS.

  Correct Answer: CDE

  Reference: https://docs.oracle.com/cd/E11882_01/server.112/e41481/spa_intro.htm#RATUG170

• Question 33:

  You upgrade your database from Oracle Database 11g to 12c. You capture execution plans for a resource-intensive SQL workload before the upgrade. After the upgrade, you load the plans into the SQL Management Base.

  Examine the parameters set for the instance:

  

  Which two statements are true?

  A. A better execution plan may be found for the SQL statements and can be evolved manually.

  B. New execution plans generated will always be added to the SQL plan baselines as fixed plans.

  C. New execution plans cannot be evolved until execution plans from the previous version are dropped.

  D. New SQL plan baselines might be generated automatically for the repeatable SQL statements.

  E. New execution plans generated will always be added to the SQL plan baselines as accepted plans.

  Correct Answer: AD

  Reference: https://blogs.oracle.com/optimizer/upgrade-to-oracle-database-12c-and-avoid-queryregression-v2

• Question 34:

  You are administrating a database on a symmetric multiprocessing (SMP) machines. The database supports a mixed workload. You notice a large number of free buffer waits and buffer busy waits that are affecting the performance of the database. To mitigate the issue, you increase the buffer cache size, but after some time, you notice the same wait events with a high number.

  Examine the parameters set for the instance:

  

  Which two actions would you recommend to resolve the issue?

  A. decreasing the idle time resource limit in the profile assigned to users

  B. enabling asynchronous I/O for the file system C. increasing the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter

  D. configuring the KEEP buffer pool

  E. increasing the number of DBWn processes

  Correct Answer: AC

• Question 35:

  Examine the Load Profile section of an AWR report: Which two inferences can you derive from the details in this section? (Choose two.)

  

  A. The values for Redo size and Block changes imply that only updates were performed by transactions.

  B. The values for Parses (SQL) and Hard parses (SQL) imply that cursor sharing occurred quite often.

  C. The values for DB Time and DB CPU imply that the database had a high proportion of idle time during the specified snapshot interval.

  D. The values for SQL Work Area and User calls imply that only sort-based operations were performed.

  E. The values for Logical reads and Physical reads imply that the number of disk reads per second was less than the total number of DB block reads and consistent gets per second.

  Correct Answer: BD

• Question 36:

  Examine the command:

  SQL> ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;

  Which two statements are true about setting the parameter value to TRUE?

  A. It improves performance because DML and DDL operations performed on temporary tables do not use the database buffer cache and redo log buffer.

  B. It improves database performance by reducing the overhead on the PGA.

  C. It reduces the amount of redo generated for DML operations performed on temporary tables.

  D. It reduces the amount of undo stored in the undo tablespace.

  Correct Answer: BD

  Reference: https://onlineappsdba.com/index.php/2016/03/21/next-five-new-features-in-oracle-database12c-for-dbas-part-ii/

• Question 37:

  Which two statements are true about server-generated alerts? (Choose two.)

  A. They are always logged in the alert log.

  B. They are written to a trace file if the TRACE_ENABLED initialization parameter is set to TRUE.

  C. They are generated only when the STATISTICS_LEVEL initialization parameter is set to ALL.

  D. They can be generated for user-defined metric thresholds.

  E. They appear in the DBA_ALERT_HISTORY view whenever corrective action is taken for an alert.

  Correct Answer: CD

• Question 38:

  Which four objectives are achieved by using Resource Manager to manage multiple concurrent user sessions that are competing for resources? (Choose four.)

  A. distributing available CPU by allocating percentages of CPU time to different users and applications

  B. limiting the degree of parallelism of any operation performed by members of a group of users

  C. limiting queries based on resource consumption of runaway sessions or calls that consume more than a specified amount of CPU, physical I/O, logical I/O, or elapsed time

  D. limiting the number of concurrent sessions for a user

  E. limiting the number of user sessions allowed to be concurrently active within a group of users

  F. limiting the number of parallel executions that can be executed by a user

  Correct Answer: ACEF

• Question 39:

  Which two statements are true about the interpretation of Buffer Cache Hit Ratio in the Instance Efficiency Percentages section of an AWR report? (Choose two.)

  A. A high value indicates that the buffer cache is adequately sized for the current workload.

  B. Poor hit ratios indicate that a large number of indexed lookups or small table scans are being performed.

  C. A low hit ratio does not necessarily imply that increasing the size of the buffer cache will improve performance.

  D. A high hit ratio may indicate that repeated scanning of the same large table or index is being performed.

  E. A low hit ratio indicates that a KEEP buffer pool should be configured based on the size of the largest object accessed in the buffer cache.

  Correct Answer: CE

• Question 40:

  Which two statements are true about ADDM or Real-Time ADDM? (Choose two.)

  A. ADDM can be run manually by selecting any range of AWR snapshots available within the AWR retention period, provided they do not cover a time period when the instances were restarted.

  B. ADDM runs in Partial mode to analyze any hung database issues.

  C. Real-Time ADDM can proactively detect and diagnose transient performance issues that last for a few seconds.

  D. Real-Time ADDM is automatically invoked by ADDM at the end of every hour.

  Correct Answer: BC