PDF 1z0-084 Study Guide

The Automatic Database Diagnostic Monitor (ADDM) analyzes and reports on various types of performance problems. For Pluggable Databases (PDBs), it can identify issues such as I/O capacity limits which may hinder the overall performance by causing bottlenecks. Additionally, ADDM can report on user I/O waits, which can indicate performance issues related to the time it takes for user queries to read data from the disk.

References:

• Oracle Multitenant Administrator's Guide, 19c
• Oracle Database Performance Tuning Guide, 19c

The execution plan shows a full table access for theSALEStable. To reduce the execution time, creating an index onSALES.TIME_IDwould be beneficial as it would allow the database to quickly sort and retrieve the most recent sales without the need to perform a full table scan, which is I/O intensive and slower. By indexingTIME_ID, which is used in theORDER BYclause, the optimizer can take advantage of the index to efficiently sort and limit the result set to the ten most recent sales.

• B (Incorrect):ReplacingFETCH FIRSTwithROWNUMwould not necessarily improve the performance unless there is an appropriate index that the optimizer can use to avoid sorting the entire result set.
• C (Incorrect):There is no indication that the current statistics are inaccurate; hence, collecting new statistics may not lead to performance improvement.
• D (Incorrect):While adaptive plans can provide performance benefits by allowing the optimizer to adapt the execution strategy, the main issue here is the lack of an index on theORDER BYcolumn.
• E (Incorrect):Creating an index onSALES.CUST_IDcould improve join performance but would not address the performance issue caused by the lack of an index on theORDER BYcolumn.

References:

• Oracle Database SQL Tuning Guide:Managing Indexes
• Oracle Database SQL Tuning Guide:Using Indexes and Clusters

For a Standard Edition production database, the Statspack tool is available to diagnose performance problems. The Automatic Workload Repository (AWR) and its related tools like AWR Compare Periods report and SQL Performance Analyzer are features of the Oracle Database Enterprise Edition and are not available in Standard Edition. Database Replay is also a feature of the Enterprise Edition. Statspack is a performance diagnostic tool provided for earlier versions and Standard Editions of the Oracle Database to collect, store, and analyze performance data.

References

• Oracle Database 19c Administrator’s Guide - Using Statspack to Diagnose Database Performance Issues

Given a server with 64 CPUs, if the buffer cache size increase did not alleviate free buffer waits and buffer busy waits, one can look into optimizing I/O and the efficiency of the DB writer processes.

C. Setting theDBWR_IO_SLAVESparameter to a non-zero value, such as the number of CPUs, would initiate I/O slave processes to assist the DB writer process. This can help reduce I/O contention when writing from the buffer cache to disk, particularly for systems without asynchronous I/O capabilities.

D. Increasing the value ofDBWRITERPROCESSESenables multiple DB writer processes to be active simultaneously. In a system with many CPUs, such as 64, increasing this value can improve the write throughput to disk and potentially reduce buffer busy waits.

References:

• Oracle Database Reference, 19c
• Oracle Database Performance Tuning Guide, 19c