1z0-084 Questions and Answers

In an adaptive execution plan, the Optimizer makes runtime decisions between nested loop and hash joins using a statistics collector. The collector is a row source that collects statistics about the rows it processes and can adapt the plan based on the number of rows processed.

References:

• Oracle Database SQL Tuning 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

When importing objects that used the In-Memory (IM) column store in their source database using Oracle Data Pump, the following statements are true:

• D (Correct):TheTRANSFORMclause can be used to alter object creation DDL during import operations. This can include adding theINMEMORYclause to tables that were not originally using the IM column store.
• F (Correct):The import operation can preserve theINMEMORYattributes of tables as they were at the time of export, effectively replicating the IM column store settings from the source database.

The other statements are not accurate in the context of Data Pump import:

• A (Incorrect):Data Pump does not give preference to the IM column store clauses at the tablespace level over table-level definitions unless explicitly specified by theTRANSFORMclause.
• B (Incorrect):While Data Pump can transport existingINMEMORYattributes, it is not mandatory. It is controlled by theINCLUDEorEXCLUDEData Pump parameters or theTRANSFORMclause.
• C (Incorrect):TheINMEMORY_CLAUSEparameter is not part of the Data Pump Export utility. To modify the IM column store clauses, you would use theTRANSFORMparameter during import, not export.
• E (Incorrect):Data Pump does not ignore the IM column store clause unless specifically instructed to do so via theEXCLUDEparameter.

References:

• Oracle Database Utilities:Data Pump Export
• Oracle Database Utilities:Data Pump Import

The production phase of the application lifecycle is often managed reactively. While proactive measures and performance tuning are essential, unforeseen issues can arise in production that require immediate attention and resolution. Reactive management involves monitoring performance and responding to issues as they occur, ensuring the application maintains acceptable performance levels for end-users.

References

• Oracle Database 19c Performance Tuning Guide - Reactive Tuning

The Active Session History (ASH) report is a tool that provides detailed information about active sessions for the time period specified. Since the AWR snapshot interval is one hour and does not capture the granularity needed for this issue, ASH reports aremore suitable as they contain more granular data for sessions that were active during the period of interest.

References:

• Oracle Database Performance Tuning Guide, 19c

TheDBMS_STATS.GATHER_DATABASE_STATSprocedure is used to gather statistics for all schema objects in the database that do not have up-to-date statistics. These statistics are essential for the optimizer to make informed decisions about the most efficient way to execute a query. The procedure collects statistics such as table and column statistics, index statistics, and system statistics, which are all used in the execution plan generation.

References:

• Oracle Database PL/SQL Packages and Types Reference, 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

A. Statspack is a performance diagnostic tool that can help identify high CPU usage issues. High CPU time may indicate that SQL statements need to be tuned for better performance.

D. High wait times can often be reduced by instance tuning, such as adjusting database parameters or improving I/O performance.

F. DB Time is a cumulative time metric that includes the time spent by both user sessions and background processes executing database calls.References:

• Oracle Database Performance Tuning Guide, 19c
• Oracle Database Concepts, 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

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.

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