1Z0-084 Oracle Database 19c: Performance Management and Tuning Practice Questions

When ASMM is enabled (SGA_TARGET > 0), manually setting a value for an auto-tuned component like DB_CACHE_SIZE does not disable auto-tuning for that component. Instead, the manually set value acts as a minimum floor. ASMM will ensure the buffer cache is at least the size specified by DB_CACHE_SIZE, but it retains the ability to dynamically allocate more memory to it (up to the SGA_TARGET limit) or shrink it back down to the specified minimum, based on workload demands. This allows DBAs to guarantee a minimum resource allocation while still benefiting from automatic management.

This statement is false. Statspack is a performance monitoring tool provided by Oracle that is free to use with all editions of the Oracle database. It does not require the Diagnostic Pack license. The more advanced tools like AWR, ADDM, and ASH, which are built upon the AWR repository, do require the Diagnostic Pack license.

An ADDM finding is a high-level summary and recommendation. When ADDM identifies that SQL is the primary consumer of DB Time, it points to inefficient SQL statements as the root cause. The ADDM report itself will list the top SQL IDs, but the most comprehensive data is in the AWR report that ADDM analyzed. The DBA's next logical step is to go to the source data—the AWR report—and examine the 'SQL ordered by DB Time' (or similar sections like 'SQL ordered by Gets', '...by Reads') to identify the exact SQL statements that are problematic. Once identified, those statements can be targeted for tuning using tools like the SQL Tuning Advisor.

The optimizer trace, enabled by setting event 10053, is the definitive way to see the optimizer's 'thought process'. The generated trace file contains a wealth of information, including the initial query block, query transformations (like view merging or predicate pushing), analysis of access paths for each table, join order considerations, cost calculations for different plans, and the final chosen plan. This is far more detailed than a standard execution plan or a 10046 SQL trace, which focuses on runtime statistics like waits and binds. It is the primary tool for deep dives into why the optimizer chose a particular plan.

The TABLE ACCESS BY INDEX ROWID BATCHED operation is an optimization primarily for Exadata, but available in 12c and later. It aims to improve the performance of table lookups via an index. Instead of retrieving a ROWID from the index and immediately accessing the table block (which can be inefficient for many rows), Oracle first retrieves a batch of ROWIDs from the index. It then sorts these ROWIDs by their data block address and accesses the table blocks in an optimal sequence to minimize I/O. This 'batching' process converts many single-block (random) reads into fewer, more efficient multi-block reads.

'latch: cache buffers chains' contention often occurs when multiple sessions are trying to access blocks protected by the same latch, a situation commonly caused by 'hot blocks'. This is frequently a result of inefficient SQL, such as repeatedly scanning a large number of blocks through a non-selective index. The most effective solution is to fix the root cause: the SQL statement. Tuning the SQL to use a more selective index or a full table scan (if appropriate) will reduce the logical I/O and the contention on the same set of blocks. Increasing the buffer cache might slightly alleviate the symptoms but doesn't solve the underlying SQL inefficiency. Reducing block size is a major architectural change and not a direct solution. Increasing _DB_BLOCK_LRU_LATCHES is a deprecated action and not recommended.

The ORA-00845 error (MEMORY_TARGET not supported on this system) indicates that the operating system environment cannot support the Automatic Memory Management configuration. On Linux systems, AMM requires the /dev/shm shared memory filesystem to be large enough to hold the entire MEMORY_TARGET allocation. If /dev/shm is smaller than MEMORY_TARGET, the database instance cannot allocate the required shared memory and will fail to start. The correct resolution is to increase the size of /dev/shm (typically by editing /etc/fstab) to be at least the size of MEMORY_TARGET and reboot the server or remount the filesystem.

Partition pruning can only occur if the optimizer can determine at parse time which partitions need to be accessed. If a function (e.g., TO_CHAR, SUBSTR) or an implicit data type conversion is applied to the partitioning key column in the WHERE clause, the optimizer cannot make this determination. For example, if a DATE column is the partitioning key and the WHERE clause compares it to a character string (WHERE partition_date_col = '01-JAN-2023'), the implicit TO_DATE conversion on the literal value is fine. However, if the query is written as WHERE TO_CHAR(partition_date_col, 'YYYY-MM-DD') = '2023-01-01', the function on the column prevents pruning. This is a common cause of performance issues with partitioned tables.

This statement is true. Oracle Database allows you to set a default In-Memory attribute for a tablespace. When you create a new table or partition within that tablespace without specifying an INMEMORY or NO INMEMORY clause, it inherits the default setting from the tablespace. This simplifies the management of In-Memory objects for large applications.