Oracle Oracle Database 1Z0-064 Questions & Answers

• Question 1:

  Queries on the SALES table frequently use the PROD_ID and COST_ID columns together in the WHERE clause. Examine the command:

  

  Which three statements are true about executing this command?

  A. It creates extended statistics for the column group (PROD_ID and CUST_ID).

  B. It improves the cardinality estimates when both the PROD_ID and CUST_ID columns are used in the WHERE clause.

  C. It creates a virtual column for the column group (PROD_ID and CUST_ID).

  D. It improves the selectivity estimates of a single predicate in the WHERE clause.

  E. It gathers individual column statistics for the PROD_ID and CUST_ID columns.

  Correct Answer: ACD

• Question 2:

  Which two statements are true about wait events?

  A. A single resource wait event may be recorded as multiple waits, depending on the number of session timeouts during that wait.

  B. A wait event can be defined in multiple wait classes.

  C. Wait event statistics are cumulatively collected only at the instance level.

  D. Wait events for an instance include statistics for both background and foreground processes.

  E. Wait event countries are incremented by the server process that waits.

  Correct Answer: BE

  Reference: https://docs.oracle.com/database/121/TGDBA/pfgrf_instance_tune.htm#TGDBA13014

• Question 3:

  Examine the Load Profile and partial Top 10 Foreground Events by Total Wait Time sections from an AWR report.

  

  

  Which two areas should you examine next to identify possible bottlenecks?

  A. the application code because of CPU-intensive activities

  B. the application code because user calls are performing several queries that require sorting

  C. the "SQL ordered by Gets" section of the AWR report to check for excessive logical I/O

  D. the "SQL ordered by Reads" section of the AWR report to check for excessive physical reads

  Correct Answer: AC

• Question 4:

  Your database supports an OLTP system.

  Examine the parameter values configured in your database:

  

  The CUSTOMERS table contains 8,000 rows. The CUST_ID column is the primary key and the COUNTRY_ID column contains only three possible values: 1111, 2222, and 3333.

  You execute the commands:

  SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(`SH',`CUSTOMERS');

  PL/SQL procedure successfully completed.

  SQL> CREATE INDEX COUNTRY_IDX ON CUSTOMERS (COUNTRY_ID);

  Index created.

  You then perform a series of INSERT, UPDATE, and DELETE operations on the table.

  View the Exhibit to examine the query and its execution plan.

  

  Which three options would improve the performance of the query? (Choose three.)

  A. creating a bitmap index on the COUNTRY_ID column

  B. regathering statistics on the CUSTOMERS table

  C. creating a histogram on the COUNTRY_ID column

  D. increasing the size of the PGA

  E. creating a SQL profile

  F. creating a KEEP cache

  Correct Answer: BCD

• Question 5:

  In which three scenarios would you recommend the use of the SQL Performance Analyzer?

  A. to identify SQL statements that require SQL profiles

  B. to analyze the impact of network and interconnect changes on database workload

  C. to identify SQL statements whose performance may have regressed due to a hardware upgrade

  D. to analyze the impact of new indexes and materialized views on the SQL statements executed by an application

  E. to identify SQL statements in an application whose performance may have regressed due to migration from Oracle Database 11g to 12c

  F. to analyze the impact of using bind variables for syntactically similar SQL statements

  Correct Answer: ABC

• Question 6:

  Which four statements are true about Consolidated Database Replay?

  A. Multiple replay schedules can be defined and any existing replay schedules can also be used.

  B. Multiple database workloads can be replayed only if both the capture and replay databases are on the same operating system.

  C. The replay starts simultaneously for all participating workload captures if no order is defined in the replay schedule.

  D. Consolidated workloads from one or multiple databases can be replayed concurrently on a single system.

  E. A subset of the captured workload can be replayed on a test system.

  F. Multiple database workloads can be replayed only if both the capture and replay databases have the same database version.

  G. The number of replay clients must be equal to the number of captured workloads.

  Correct Answer: ACDE

  Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/ratug/using-consolidateddatabase-replay.html#GUID-BBC96414-F08A-4D57-BA72-B28C1BBEE9F2

• Question 7:

  Your database supports an OLTP workload where a large number of syntactically similar queries are executed. Examine the Instance Efficiency Percentages in the Automatic Workload Repository (AWR) report of the last hour: Which two inferences can be drawn from the report?

  

  A. The optimizer is waiting for resources during parsing of the queries.

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

  C. The database buffer cache is undersized and is causing contention.

  D. Cursors are not getting shared in the library cache.

  Correct Answer: AD

• Question 8:

  As part of an application upgrade, new objects are being added to an application schema. You want to check the effect of the new schema objects on the performance of the SQL workload generated by the application.

  How would you accomplish this?

  A. Capture the workload in an STS and submit to SQL Access Advisor as an input to generate recommendations for indexes and materialized views.

  B. Capture the workload, set the OPTIMIZER_USE_PENDING_STATISTICS to FALSE, and then replay the workload.

  C. Capture the workload in an STS and submit to SQL Tuning Advisor as an input to generate recommendations for indexes and SQL profiles.

  D. Set the PUBLISH statistic preference to FALSE, gather statistics, capture the workload in a SQL Tuning Set (STS), and submit the STS to SQL Tuning Advisor.

  Correct Answer: A

• Question 9:

  Your database supports multiple applications. The applications run on the middle tier and use connection for connecting to the database.

  You notice that the sessions created by the applications are competing for resources. You want to statistically measure the workload and set priorities.

  What action must you perform to achieve this?

  A. Create services for the applications and set a relative priority by assigning them to application users and using the DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE procedure to trace the services.

  B. Create services for the applications and set a relative priority by assigning them to application users and using the DBMS_MONITOR.SESSION_TRACE_ENABLE procedure to trace the services.

  C. Create services for the applications and set the relative priority of services within an instance by mapping the services directly to consumer groups.

  D. Create services for the applications and set a relative priority by assigning them to application users.

  Correct Answer: C

• Question 10:

  Your database supports an OLTP workload where applications primarily perform small random I/Os. You notice an increase in the I/O requests queued up against the storage, and an increase in the wait time in queue. The database uses file system storage.

  What would you recommend to decrease the wait time of I/O requests?

  A. Decrease the value of the DBWR_IO_SLAVES parameter.

  B. Increase the size of the buffer cache.

  C. Increase the value of the DB_WRITER_PROCESSES parameter.

  D. Suggest storage admin to add more disks (spindles) to storage and stripe data across disks.

  Correct Answer: B