1Z0-084 Sample Questions & Answers

The 'log file sync' wait event indicates that user sessions are waiting for LGWR to write redo from the log buffer to the online redo logs. The 'log file parallel write' event is the time LGWR itself spends writing to the logs. To determine if the I/O subsystem is the bottleneck, you must measure the actual write performance. The AWR report's I/O stats section, specifically the 'redo write time' and average write time (avg wrt(ms) for the redo log files), provides a direct measurement of the I/O performance for redo writes. High values here (e.g., >10ms) confirm an I/O bottleneck. Increasing the log buffer is unlikely to help if the I/O subsystem cannot keep up. Increasing log file size might reduce log switches but won't improve write speed. Adding more redo log groups is a good practice for availability but also doesn't directly speed up individual writes.

A SQL Profile does not freeze an execution plan like a SQL Plan Baseline does. Instead, it contains supplemental statistics and correction factors (e.g., adjustments to cardinality or cost estimates) derived from the SQL Tuning Advisor's analysis. When the SQL statement is parsed, the optimizer uses this additional information from the profile, along with the regular object statistics, to make better decisions and generate a more optimal plan. This allows the plan to adapt to future changes in data or statistics while still being guided by the profile's corrections.

The V$PGA_TARGET_ADVICE view is specifically designed to help DBAs size the PGA_AGGREGATE_TARGET. It predicts how changes to this parameter will affect the cache hit percentage and the number of multipass executions. By examining the ESTD_OVERALLOC_COUNT or ESTD_MULTIPASS_EXECUTIONS (in older versions) columns, the DBA can see at which PGA_AGGREGATE_TARGET value the number of multipass executions would drop to zero or an acceptable level. This provides a direct, data-driven recommendation for tuning the PGA.

The high 'gc current block 2-way' waits on the INVENTORY table indicate severe block contention between the RAC nodes. This happens when both nodes are frequently requesting the most current version of the same data blocks for modification. The root cause is that updates for different products are likely physically co-located in the same data blocks, causing inter-node conflicts. Implementing hash partitioning on PRODUCT_ID will physically separate the data for different products into different partitions, and therefore different sets of data blocks. This dramatically reduces the probability that Node 1 and Node 2 will need to modify the same block at the same time, thus mitigating the global cache contention. Directing traffic to one node would serialize the workload, defeating the purpose of RAC for scalability. Increasing DBWRs doesn't solve the inter-node block transfer issue. Rebuilding indexes might temporarily help but won't solve the fundamental data placement problem.

Adaptive plans make a final decision on the join method at execution time. The optimizer initially chooses a default plan (often NESTED LOOPS, which is optimal for a small number of driving rows) but also defines an alternative (HASH JOIN, optimal for a large number of rows). It inserts a 'statistics collector' operation in the plan. As the first row source is executed, the collector counts the actual number of rows produced. If this count exceeds a specific threshold calculated by the optimizer, it signals that the initial cardinality estimate was wrong. The plan then 'flips' to the alternative HASH JOIN sub-plan, which is more efficient for the larger-than-expected row set.

SQL Plan Management (SPM) using SQL Plan Baselines is the designated feature for ensuring plan stability. A baseline is created for a SQL statement, capturing one or more accepted execution plans. By default, the optimizer will only use plans that are present in the baseline. This effectively 'locks in' the known good plans and prevents the optimizer from choosing a new, potentially worse plan (a regression) after changes like an upgrade or statistics gathering. Stored Outlines are a legacy feature superseded by SPM. SQL Profiles guide the optimizer but do not lock a specific plan. SQL Patches are for injecting hints to fix specific issues.

With the default setting, the optimizer uses dynamic sampling (now called dynamic statistics) in several key situations where it believes the existing statistics are insufficient or absent. This includes when a table has no statistics at all, when predicates involve complex expressions (e.g., WHERE SUBSTR(col,1,1) = 'A'), or when there's a combination of predicates on multiple tables for which correlation is unknown. The goal is to get a more accurate cardinality estimate at parse time to generate a better plan. Stale statistics alone do not trigger dynamic sampling; the optimizer will use the stale stats unless other conditions are met.

The Automatic Big Table Cache feature is controlled by the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter. Setting this parameter to a non-zero value allocates a percentage of the buffer cache to be used as a special-purpose cache for large tables, utilizing a different caching and eviction algorithm optimized for scans rather than random reads.

The SQL Tuning Advisor is the primary tool for analyzing and providing recommendations for a single, specific SQL statement. It performs a deep analysis of the statement, its objects, statistics, and the database environment. Its recommendations can include creating new indexes, accepting a SQL Profile, gathering or modifying statistics, or restructuring the SQL. The SQL Access Advisor works on an entire workload (SQL Tuning Set) to recommend a holistic set of indexes and materialized views, while the Optimizer Statistics Advisor focuses on the health of the statistics gathering process.