1z0-084 Sample Questions Answers

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

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 AWR excerpt points to excessive physical reads on the SALES table and index, suggesting the need for optimizing table storage and access.

Partitioning the SALES table (A) can reduce 'db file sequential read' waits by breaking down the large SALES table into smaller, more manageable pieces. This can localize the data and reduce the I/O necessary for query operations.

Compressing the SALES table (D) can also help reduce I/O by minimizing the amount of data that needs to be read from disk. This can also improve cache utilization and reduce the 'db file sequential read' waits.

References:

• Oracle Database VLDB and Partitioning Guide, 19c
• Oracle Database Administrator’s Guide, 19c

These changes are recommended based on Oracle's best practices for managing large tables and reducing I/O waits, ensuring better performance and efficiency.

To configure and enable Database Smart Flash Cache, you must set the DB_FLASH_CACHE_SIZE parameter to reflect the combined size of the flash devices youintend to use for the cache. In this scenario, two flash devices are configured: /dev/sdj with 128G and /dev/sdk with 64G.

• Determine the combined size of the flash devices intended for the Database Smart Flash Cache. In this case, it's 128G + 64G = 192G.
• However, Oracle documentation suggests setting DB_FLASH_CACHE_SIZE to the exact sizes of the individual devices, separated by a comma when multiple devices are used.
• Modify the parameter in the database initialization file (init.ora or spfile.ora) or using an ALTER SYSTEM command. Here's the command for altering the system setting:

ALTER SYSTEM SET DB_FLASH_CACHE_SIZE='128G,64G' SCOPE=SPFILE;

• Since this is a static parameter, a database restart is required for the changes to take effect.
• Upon database startup, it will allocate the Database Smart Flash Cache using the provided sizes for the specified devices.

It is important to note that MEMORY_TARGET and MEMORY_MAX_TARGET parameters should be configured independently of DB_FLASH_CACHE_SIZE. They control the Oracle memory management for the SGA and PGA, and do not directly correlate with the flash cache configuration.

References

• Oracle Database 19c Documentation on Database Smart Flash Cache
• Oracle Support Articles and Community Discussions on DB_FLASH_CACHE_SIZE Configuration

The right time to stop tuning an Oracle database is often determined by the point of diminishing returns - when the cost of further tuning (in terms of time, resources, or money) exceeds the performance benefits gained. This is often related to the budget allocated for performance tuning.

• A (Correct):When the allocated budget for performance tuning has been exhausted, it may be time to stop tuning unless the benefits of further tuning justify requesting additional budget.
• B (Incorrect):Eliminating all concurrency waits from the Top 10 is an unrealistic goal since some waits are inevitable and can occur due to application design, which might not be possible to eliminate completely.
• C (Incorrect):The buffer cache and library cache hit ratio being above 95% does not necessarily indicate that the database is fully optimized. Hit ratios are not reliable indicators of database performance and should not be used as sole criteria to end tuning efforts.
• D (Incorrect):Having I/O less than 10% of DB time is not a definitive indicator to stop tuning. It is essential to consider the overall performance goals and whether they have been met rather than focusing solely on I/O metrics.

References:

• Oracle Database Performance Tuning Guide:Introduction to Performance Tuning
• Oracle Database 2 Day + Performance Tuning Guide:Understanding the Tuning Process

In the provided code block, theDBMS_SERVER_ALERT.SET_THRESHOLDprocedure is used to set alert thresholds for the CPU time per call in Oracle Database. This procedure is a part of Oracle's Database Server Alert system, which monitors various metrics and generates alerts when certain thresholds are exceeded.

The parameters passed to theSET_THRESHOLDprocedure are as follows:

• The first parameterDBMS_SERVER_ALERT.CPU_TIME_PER_CALLspecifies the metric for which the threshold is being set, in this case, the CPU time consumed per database call.
• The second and third parametersDBMS_SERVER_ALERT.OPERATOR_GEand'8000'specify the warning threshold level and its value, respectively. However, these are not relevant to the answer as they are overridden by the critical threshold settings.
• The fourth and fifth parametersDBMS_SERVER_ALERT.OPERATOR_GEand'10000'set the critical threshold level and its value. This means that a critical alert will be generated when the CPU time per call exceeds 10000 microseconds.
• The remaining parameters specify the warning and critical alert intervals, the instance name, the object type, and the service name. These are not directly relevant to the behavior described in the options.

Thus, the correct answer is B, as the critical threshold for CPU time per call is set to 10000 microseconds, and the system is configured to issue a critical alert when this threshold is exceeded.

References:

• Oracle Database 19c documentation on theDBMS_SERVER_ALERT.SET_THRESHOLDprocedure, which details the parameters and usage of this procedure for setting alert thresholds within Oracle Database monitoring system.
• Oracle Database Performance Tuning Guide, which provides best practices and methodologies for monitoring and tuning Oracle Database performance, including the use of server alerts and thresholds.

The optimizer_adaptive_statistics parameter, when set to TRUE, enables the optimizer to use adaptive statistics, such as SQL Plan Directives, to help improve plans by automatically adjusting them based on the actual execution statistics.

References:

• Oracle Database SQL Tuning Guide, 19c

When ASMM is disabled, the sizes of the automatically managed SGA components remain at their current values. ASMM is controlled by theSGA_TARGETparameter. IfSGA_TARGETis set to a non-zero value, ASMM is enabled and Oracle will automatically manage the sizes of the various SGA components. When ASMM is disabled, by settingSGA_TARGETto zero, the SGA components that were automatically sized will retain their current sizes rather than being reset to their original user-defined values. The overall size of the SGA remains the same unless manually changed by modifying individual component sizes orSGA_MAX_SIZE.

References:

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

The SQL Query Result Cache stores the results of queries and PL/SQL function calls for reuse. However, entries in the result cache can be invalidated or lost under certain conditions:

A. Results can be aged out of the cache when the cache becomes full and new results need to be stored. This process ensures that the cache does not exceed its allocated memory and that it contains the most recently used entries.

B. Setting theRESULT_CACHE_MAX_SIZEparameter to 0 will effectively disable the result cache and all cached results will be lost, as Oracle will no longer allocate any memory to the result cache.

References:

• Oracle Database Performance Tuning Guide, 19c

When sessions concurrently insert data into a table with an LOB column and one session needs to allocate a new extent because it cannot find available space, the wait event associated with this contention is "enq: HW - contention". The HW stands for High Water Mark which is related to space allocation in the database segment. When asession needs to allocate a new extent, it may raise this wait event in other sessions that are also attempting to allocate space in the same LOB segment.

References

• Oracle Database 19c Reference Guide - enq: HW - contention

The ORA-15505 error indicates that the instance encountered errors while trying to access the specified directory. This could be due to:

A. Insufficient privileges: The user attempting to start the workload capture might not have the required permissions to execute the DBMS_WORKLOAD_CAPTURE package or to read/write to the directory specified.

E. Accessibility: The database instance may not be able to access the directory due to issues such as incorrect directory path, directory does not exist, permission issues at the OS level, or the directory being on a file system that's not accessible to the database instance.

References:

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

Database services in Oracle are used to manage how resources are allocated and how workloads are managed within the database. The features that use database services are:

• B (Correct):Database Resource Manager (DBRM) uses services to control resource allocation to different workloads. It ensures that resources are assigned to the most critical tasks first, based on the service associated with the workload.
• E (Correct):Oracle Scheduler can also utilize database services. Jobs in Oracle Scheduler can be assigned to different services to control resource allocation and prioritization.

The other features mentioned are related to SQL performance but do not directly utilize database services in the way Resource Manager and Scheduler do:

• A:Oracle Automatic Reoptimization is a feature that allows the database to automatically improve the execution plan of a SQL statement after it is executed, based on the actual performance metrics, but it does not directly use database services.
• C:Oracle SQL Performance Management involves various components of SQL tuning and monitoring, but it does not use database services to operate.
• D:Oracle SQL Tuning Advisor provides advice on how to tune SQL queries for better performance. While it can be used in conjunction with services for managing and analyzing workloads, it doesn't use services in its core functionality.

References:

• Oracle Database Administrator’s Guide:Administering Services
• Oracle Database Administrator's Guide:Managing Resources with Oracle Database Resource Manager
• Oracle Database Scheduler Developer’s Guide:Using the Scheduler

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