1z0-084 Practice Exam Questions with Answers Oracle Database 19c: Performance Management and Tuning Certification

During a soft parse, Oracle checks the shared SQL area to see if an incoming SQL statement matches one already in the shared pool. This operation includes syntax and semantic checks. The syntax check ensures the statement is properly formed, and the semantic check confirms that all the objects referenced in the SQL statement exist and that the user has the necessary privileges to access them.References:

• Oracle Database Concepts, 19c
• Oracle Database SQL Tuning Guide, 19c

DB time includes the time spent on user and background processes. It can be greater than the elapsed time because it accumulates the active time of all the processes. For example, if two sessions are each active for 2 seconds at the same time, DB time would accumulate 4 seconds, while the elapsed time would be only 2 seconds.References:

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

To investigate the cause of the ORA-04036 error, which indicates that PGA memory usage exceeds thePGA_AGGREGATE_LIMIT, the appropriate views to consult areDBA_HIST_ACTIVE_SESS_HISTORYandDBA_HIST_PROCESS_MEM_SUMMARY.

• DBA_HIST_ACTIVE_SESS_HISTORY:This view provides historical information about active sessions in the database. It includes details about the SQL executed, the execution context, and the resources consumed by each session. By examining this view, you can identify the specific sessions and SQL queries that were active and potentially consuming excessive PGA memory around the time the ORA-04036 error occurred.
• DBA_HIST_PROCESS_MEM_SUMMARY:This view contains historical summaries of memory usage by processes. It can help in identifying the processes that were consuming a significant amount of PGA memory, leading to the ORA-04036 error. This view provides aggregated memory usage information over time,making it easier to pinpoint the processes responsible for high PGA memory consumption.

Together, these views offer a comprehensive overview of the memory usage patterns and specific queries or processes that might have contributed to exceeding thePGA_AGGREGATE_LIMIT, resulting in the ORA-04036 error.

References:

• Oracle Database Reference:DBA_HIST_ACTIVE_SESS_HISTORY
• Oracle Database Reference:DBA_HIST_PROCESS_MEM_SUMMARY
• Oracle Database Performance Tuning Guide:Managing Memory

When you enable statistics gathering for a specific service and module usingDBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE, the output is aggregated and can be viewed using theV$SERV_MOD_ACT_STATSdynamic performance view. This view contains the cumulative statistics of database activity broken down by service and module, which is exactly what you collect when executing the provided command.

• B (Incorrect):While many types of trace files are located in the Diagnostic Destination directory ($ORACLE_BASE/diag), the aggregated statistics for services and modules are not written to trace files but are instead viewable through dynamic performance views.
• C (Incorrect):TheV$SERVICE_STATSview provides service-level statistics but does not provide the combined service/module-level breakdown.
• D (Incorrect):The output of the PL/SQL block is not written to a file in the current working directory; it is stored in the data dictionary and accessible via dynamic performance views.

References:

• Oracle Database PL/SQL Packages and Types Reference:DBMS_MONITOR
• Oracle Database Reference:V$SERV_MOD_ACT_STATS

When an ORA-00060 deadlock error occurs, detailed information about the error and the deadlock graph are dumped into the alert log. This log contains a trace file name that you can use to find additional detailed information about the sessions involved in the deadlock and the SQL statements they were executing.References:

• Oracle Database Administrator’s Guide, 19c
• Oracle Database Error Messages, 19c

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

A high buffer cache hit ratio typically indicates that the database is effectively using the buffer cache and does not often need to read data from disk. However, this metric alone is not a reliable indicator of the I/O performance of the database for several reasons:

• Full table scans and fast full index scans (A) can bypass the buffer cache by design if the blocks are not deemed reusable shortly, which can impact the cache hit ratio.
• A high cache hit ratio (B) can be misleading if the database performance is poor due to other factors, such as inefficient queries or contention issues.
• The buffer cache advisory (C) is a more valuable tool for understanding the potential impact of different cache sizes on the database's I/O performance. It simulates scenarios with different cache sizes and provides a more targeted recommendation.
• The RECYCLE and KEEP buffer caches (D) are specialized caches designed for certain scenarios. While high hit ratios can be beneficial, they are not universally required; some workloads might not be significantly impacted by lower hit ratios in these caches.
• A lower cache hit ratio (E) does not necessarily mean poor I/O performance. In some cases, a system with a well-designed storage subsystem and efficient queries might perform well even with a lower cache hit ratio.

References

• Oracle Database 19c Performance Tuning Guide - Buffer Cache Hit Ratio
• Oracle Database 19c Performance Tuning Guide - v$db_cache_advice

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

The Oracle Optimizer statistics advisor, which is part of the automated tasks framework, can be disabled using the DBMS_AUTO_TASK_ADMIN package. This will prevent it fromrunning and thus reduce space usage in the SYSAUX tablespace.References:

• Oracle Database PL/SQL Packages and Types Reference, 19c

Big Table caching is a feature that allows frequently accessed large tables to be cached in memory to improve I/O performance. From the parameter settings provided, Big Table caching is not occurring becauseDB_BIG_TABLE_CACHE_PERCENT_TARGETis set to 10, which is the minimum threshold for enabling the feature, but the size of the cache is too small for the big tables to be effectively cached.

To enable Big Table caching, one of the following actions could be taken:

• C (Correct):IncreasingDB_BIG_TABLE_CACHE_PERCENT_TARGETto at least 25. This action would allocate a larger percentage of the buffer cache for storing big tables, which could allow for caching large tables and thus improve I/O performance.
• D (Correct):IncreasingDB_CACHE_SIZEto 1G. Since the size of the buffer cache is a determining factor for how much data can be cached, increasing this parameter would provide more memory space for big tables to be cached.

Options A, B, E, and F will not enable Big Table caching because:

• A:IncreasingDB_BIG_TABLE_CACHE_PERCENT_TARGETto 50 without adjusting the overall size of the cache might still not be sufficient if theDB_CACHE_SIZEis not large enough to hold the big tables.
• B:SettingDB_KEEP_CACHE_SIZEto at least 50M only specifies a separate buffer pool for objects with the KEEP cache attribute and does not affect Big Table caching.
• E:andF:Changing thePARALLEL_DEGREE_POLICYtoADAPTIVEorAUTOinfluences the behavior of parallel execution but does not directly enable or influence Big Table caching.

References:

• Oracle Database Performance Tuning Guide:Big Table Caching
• Oracle Database Reference:DB_BIG_TABLE_CACHE_PERCENT_TARGET
• Oracle Database Reference:DB_CACHE_SIZE

In this scenario, creating an index and generating frequency histograms are two methods that can potentially improve performance:

• A (Correct):Generating frequency histograms on theCITY_IDcolumn can help the optimizer make better decisions regarding the execution plan, especially if the data distribution is skewed. Histograms provide the optimizer with more detailed information about the data distribution in a column, which is particularly useful for columns with non-uniform distributions.
• B (Correct):Creating an index on theCITY_IDcolumn would speed up queries that filter on this column, especially if it's used frequently in the WHERE clause as a filter. An index would allow for an index range scan instead of a full table scan, reducing the I/O and time needed to execute such queries.
• C (Incorrect):While SQL profiles can be used to improve the performance of specific SQL statements, they are usually not the first choice for such a problem, and creating a profile does not replace the need for proper indexing or statistics.
• D (Incorrect):Forcing the subquery to use dynamic sampling might not provide a consistent performance benefit, especially if the table statistics are not representative or are outdated. However, dynamic sampling is not as effective as having accurate statistics and a well-chosen index.
• E (Incorrect):Adaptive plans can adjust the execution strategy based on the conditions at runtime. While they can be useful in certain scenarios, in this case, creating an index and ensuring accurate statistics would likely provide a more significant performance improvement.

References:

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