CCNA Performance Tuning And Diagnostics Questions

DB time aggregates the elapsed time all foreground sessions spend actively working inside the database, including CPU and non-idle wait time. A high value therefore signals that sessions are collectively spending excessive time in the database, indicating heavy workload or contention rather than merely long connection durations.

Optimiser statistics describe table, column and index data distribution, feeding cardinality estimates that drive cost calculations. They satisfy the stem because inaccurate or stale statistics cause the optimiser to select a plan based on wrong row-count assumptions.

Enabling SQL trace for the session captures detailed execution statistics for all SQL statements executed in that session, including the problematic one. The trace file contains the execution plan, wait events, and row source statistics. This is a classic method for in-depth diagnosis of a single statement. It can be enabled at the session or instance level, but session-level is preferred for targeted analysis.

V$SESSION_WAIT exposes the current wait event, wait time and wait parameters for each active session, letting you pinpoint which sessions are stalled and on what resource right now. This directly satisfies the requirement to monitor current session performance rather than historical trends.

Accepting a SQL Tuning Advisor recommendation applies the suggested SQL Profile, storing it in the SQL profile repository so the optimizer uses the adjusted estimates for that statement. It does not rewrite the SQL text or create indexes; it implements the profile itself.

MMNL performs background tasks, including AWR snapshotting.

DBMS_STATS.GATHER_TABLE_STATS is the standard procedure to collect fresh statistics for a table. It gathers table, column, and index statistics, which the optimizer uses to generate execution plans. In this scenario, the stale results are likely due to outdated statistics after adding a column, so refreshing them with this procedure is the correct action. It ensures the optimizer has current data distribution information.

V$ACTIVE_SESSION_HISTORY contains sampled session activity, including the wait event, session ID, and SQL ID. By filtering for 'log file sync' and grouping by session_id and sql_id, you can identify which sessions and SQL statements are experiencing the most waits. This is the correct approach for top-down analysis.

V$FILESTAT accumulates physical read counts and read time (in centiseconds) per data file. Dividing the read time by the number of reads gives the average read latency per file. If this value is high (e.g., >20 ms), it indicates slow I/O. This directly confirms whether the storage is the bottleneck for the observed single-block reads.

SQL Plan Management captures and stores known-good execution plans, then ensures the optimiser uses them instead of regressing to suboptimal plans. This directly addresses plan instability, the stated cause, by preventing performance fluctuations rather than merely reacting to symptoms after they appear.

Setting this to TYPICAL or ALL enables AWR snapshots.

A SQL profile supplies auxiliary statistics and hints that steer the optimizer toward a better plan without changing the SQL text or schema objects. SQL Tuning Advisor recommends this when plan regressions or misestimates persist, directly improving performance.

ADDM relies on AWR snapshots as its data source: each analysis run compares two snapshots to quantify instance-wide activity and identify bottlenecks. Without a snapshot pair, ADDM has no baseline or interval to diagnose, so AWR snapshot generation is essential to its operation.

SQL Plan Baselines store accepted, known-good execution plans that the optimiser is forced to reproduce. They satisfy the stabilisation requirement by preventing plan regression, since only verified plans are permitted for the captured SQL statement.

The 'log file sync' wait event occurs when a user session must wait for the log writer (LGWR) to write redo information to disk upon a COMMIT. The primary cause of high 'log file sync' waits is slow I/O on the redo log files. Moving the redo logs to faster storage, such as solid-state drives (SSDs), or ensuring they are on dedicated, low-latency disks directly reduces the time LGWR takes to complete the write, thereby reducing the wait time for user sessions.

V$SQL_PLAN displays the execution plans for cursors currently in the library cache. It shows the plan that was actually used for each child cursor, including operations, costs, and cardinalities. By joining to V$SQL on SQL_ID and CHILD_NUMBER, you can retrieve the exact plan for the most recent execution, making it the correct choice.

Literals force Oracle to hard-parse every distinct statement, so each execution incurs full parse and optimisation cost, inflating parse time. Bind variables let one parsed cursor be reused across executions, eliminating repeated hard parsing. This directly addresses the high parse time described in the stem.

ASH samples active session activity every second into a circular buffer, so it exposes transient spikes and brief contention that AWR's ten-minute snapshots smooth over. This short-term sampling interval is precisely what makes it suited to analysing short-term performance.

Preferences allow granular control over how stats are gathered for a specific object.

DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET polls the shared pool's cursor cache over a specified interval, extracting SQL statements and their execution statistics directly into a SQL Tuning Set. This satisfies the stem's requirement to capture statements from the cursor cache, unlike alternatives that load sets from AWR snapshots or manual SQL text.

DB Time is the sum of the time spent by all foreground sessions in database calls (CPU time plus wait time). Elapsed Time is the actual wall-clock time of the AWR snapshot interval. If DB Time exceeds Elapsed Time, it means that on average, more than one session was active at the same time. For example, if DB Time is 10 minutes and Elapsed Time is 5 minutes, the average concurrency was 2. This indicates that the database was handling multiple concurrent requests, which is normal in a busy system but can highlight contention if waits are high.

Elapsed Time measures the total wall-clock duration of the SQL execution, from parse through fetch completion, including all waits and CPU time. It reflects the full end-to-end latency the user experiences, not just CPU consumption or database time.

V$ACTIVE_SESSION_HISTORY samples active sessions every second, retaining recent history in memory, so it exposes which sessions blocked others during the last 10 minutes. This directly satisfies the stem's requirement to pinpoint blocked sessions within that short window.

Implementing a solution closes the tuning lifecycle by applying the chosen change to the production system. This satisfies the stem's requirement for a lifecycle component, since tuning progresses from observation through diagnosis to corrective action, and implementation is the stage where the remedy is actually deployed and its effect realised.

SQL Tuning Sets capture a defined group of SQL statements with their execution context, and can be exported and imported into another database. This satisfies the stem's requirement to group high-CPU statements and transport them across environments.

V$LOCK shows locks held and requested, including TX locks. By joining V$LOCK with V$SESSION, you can identify the blocking session and the object causing the contention. This is the primary diagnostic step to find the root cause of row lock contention. It helps determine which session is holding the lock and what SQL it is executing.

V$LOCK shows current lock holders and requesters, including the blocking session ID and the object ID for TX enqueue waits. Joining V$LOCK with DBA_OBJECTS and V$SESSION identifies the exact row-level conflict. This is the correct first step to pinpoint which session holds the lock and what object is involved, enabling targeted resolution.

Automatic PGA Memory Management uses PGA_AGGREGATE_TARGET to dynamically allocate work areas. When a query spills to disk, it indicates insufficient PGA memory for the operation. Increasing this parameter gives the database more memory to allocate to work areas, reducing or eliminating temp space usage. This is the correct action to address the spills.

The SQL ID is the hash-derived identifier Oracle assigns to a SQL statement's text in the shared pool, letting you trace that statement across V$SQL, AWR and ASH. It identifies the statement itself, not a session or execution plan.

Direct path reads occur when Oracle bypasses the buffer cache and reads blocks straight into PGA memory, typical of parallel or serial full table scans on large segments. Excessive waits therefore point to a full table scan rather than index or rowid access.

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE verifies unaccepted plans against the SQL performance history and promotes those that perform better to accepted status, optionally running in verify or commit mode. It is the sole DBMS_SPM procedure providing this evolution capability.

DBMS_SQLTUNE.CREATE_SQLSET creates the empty SQL tuning set object, which is then populated from sources such as the cursor cache, AWR or SQL trace before being passed to the SQL Tuning Advisor as its input workload.

V$PGASTAT provides instance-level statistics on PGA memory usage, including total PGA allocated, cache hit percentage, and the number of multi-pass and one-pass executions for work areas. This view directly shows if operations are spilling to disk and the overall efficiency of PGA memory, making it the correct choice.

DBMS_XPLAN.DISPLAY_CURSOR with the 'ALLSTATS LAST' format displays the execution plan along with runtime statistics for the last execution of the cursor. It shows actual rows, consistent gets, and other metrics per operation. This is a standard method to obtain detailed execution statistics for a SQL statement, provided the cursor is still in the library cache and statistics were collected (e.g., via GATHER_PLAN_STATISTICS hint or STATISTICS_LEVEL=ALL).

The 'log file sync' wait event occurs when a user session commits a transaction and must wait for the log writer (LGWR) to write the redo entries to the online redo log files. If the storage subsystem hosting the redo logs has high latency, commits will be slow. Placing redo logs on faster, lower-latency storage directly reduces the time spent waiting for the write to complete, thus reducing 'log file sync' waits.

Storage indexes on Exadata work by storing min/max values for columns in storage cells. If a predicate applies a function to a column, the storage index cannot be used because the function's result is not stored. By ensuring predicates are on bare columns, the storage index can filter out irrelevant storage regions, reducing physical I/O. This is a key best practice for maximizing storage index effectiveness.

The DBMS_XPLAN.DISPLAY_CURSOR function can display the actual execution plan of a SQL statement that is currently in the library cache, along with runtime statistics. By specifying the 'ALLSTATS LAST' format, you can see the actual rows processed, consistent gets, physical reads, and other execution statistics for the last execution of the cursor. This method has minimal overhead because it retrieves information from the cursor's runtime statistics that are already collected by the database, without needing to re-execute the statement or enable additional tracing.

The AWR report captures snapshots of database activity, including SQL statistics ranked by CPU consumption, over a specified interval such as the last hour. This directly identifies the top CPU-consuming SQL statements, unlike real-time tools that lack historical aggregation.

A reverse key index reverses the bytes of each indexed column value, which spreads inserts across many leaf blocks instead of concentrating them on the rightmost leaf block. This reduces contention on the index during high-concurrency inserts. For a batch job inserting many rows, a reverse key index can significantly decrease 'enq: TX - index contention' waits while still enforcing uniqueness. However, it may affect range scans, so it is best for unique or primary key indexes on monotonically increasing values.

The TX enqueue wait signifies transactional locking, where one session holds a row lock and others queue for that same row until the holder commits or rolls back. The wait therefore directly indicates concurrent sessions competing for identical rows, matching the reported contention.

The 'db file scattered read' wait arises from multiblock reads, which Oracle issues during full table scans and index fast full scans. Single-block index range scans produce 'db file sequential read' instead, making this the precise cause matching the stem's high value.

High resource contention directly degrades performance because CPU, memory, disk, and network capacity are finite; when multiple processes compete for the same constrained resource, queuing delays increase and throughput falls. This satisfies the stem's requirement for a common degradation source, as contention predictably slows response times across workloads.

SQL Plan Baselines lock in a known-good execution plan for a SQL statement, preventing the optimiser from regressing to a worse plan after statistics or parameter changes. This directly delivers plan stability, the property the option asserts.

DBA_SQL_PLAN_BASELINES lists every SQL plan baseline in the database, showing the SQL handle, plan name, enabled and accepted status, and origin. Querying it reveals which plans are currently captured and active, which is exactly the baseline information required.

SQL Plan Baselines capture accepted, verified plans and make the optimizer use only those, preventing it from adopting a newly generated suboptimal plan after the upgrade. This directly enforces the requirement that only known, verified execution plans are used.

SQL Monitor provides real-time monitoring of long-running SQL statements, including detailed execution statistics such as actual rows, memory, time, and wait events per operation. It is designed for exactly this scenario: a currently running query that needs in-depth diagnosis. You can generate a report using DBMS_SQL_MONITOR.REPORT_SQL_MONITOR, which gives the most comprehensive information for an active execution.

The GATHER_PLAN_STATISTICS hint instructs the optimizer to collect execution statistics for all operations in the plan. These statistics, including estimated and actual rows, are then available in V$SQL_PLAN_STATISTICS_ALL. This is a standard method to compare estimates with actuals for a specific execution.

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE captures the existing optimal plan from the shared pool and loads it into a SQL plan baseline, which pins that plan so the optimizer cannot substitute a different one on later executions. This directly satisfies the requirement to lock the current plan against change.

AWR reports capture persistent, historical snapshots of database workload statistics, enabling comparison across days or weeks. This directly satisfies the long-term trend requirement, since the stem asks for diagnosing performance over time rather than instantaneous activity. Unlike real-time views, AWR retains data in the workload repository for retrospective analysis.

The AWR report satisfies the requirement for identifying SQL statements ranked by elapsed time, since its SQL ordered by Elapsed Time section aggregates statements across the snapshot interval. Unlike STATSPACK, which offers similar data but with less detail, AWR captures this automatically from the Automatic Workload Repository, directly answering the stem's summary request.

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS directly alters the interval parameter controlling Automatic Workload Repository snapshot frequency, satisfying the requirement to reduce collection overhead. Unlike manual snapshots or statistics-gathering changes, it adjusts the scheduled interval itself, so fewer snapshots are captured without disabling AWR entirely.

Querying DBA_ADVISOR_FINDINGS returns ADDM's stored findings directly, since ADDM writes its results into the advisor framework tables rather than exposing a dedicated view. This satisfies the requirement to view recommendations without generating a new report, though the view holds raw finding rows rather than formatted advice.

Active Session History samples active sessions every second into a circular buffer, recording session identifier, wait event, and blocking details. This one-second granularity over the past hour gives the session-level wait breakdown that aggregated views such as AWR cannot provide.

SQL Profiles supply auxiliary statistics and hints that let the optimiser choose a more consistent plan for a specific statement without altering the SQL text, improving execution plan stability by countering poor cardinality estimates that cause plan changes.

A SQL Tuning Set is a database object holding SQL statements plus their execution context, such as bind values, execution plans and statistics. SQL Tuning Advisor consumes this container to analyse and tune the captured workload.

DBA_HIST_WR_CONTROL stores the active Automatic Workload Repository configuration, exposing columns such as RETENTION and SNAP_INTERVAL. Querying it returns the current retention period directly, satisfying the stem's requirement for present settings rather than historical snapshots or parameter defaults.

V$SQL_PLAN exposes the execution plan rows the optimizer actually chose for a cached SQL statement, keyed by SQL_ID and child number. It satisfies the requirement to see the plan currently in use, unlike EXPLAIN PLAN, which shows a predicted plan.

SQL Access Advisor analyses workload from the SQL cache and recommends indexes, materialised views and partitioning for the supplied workload, which directly satisfies the stem's need for new index recommendations. Unlike the SQL Tuning Advisor, which tunes individual statements, it evaluates an entire workload to propose missing indexes.

ASPM validates a candidate plan by executing the SQL statement with the new plan and comparing its performance metrics against the existing baseline. Only if the measured performance improves does the database accept and evolve the baseline, ensuring the change is verified rather than assumed.

Optimizer hints embedded in the SQL text directly override the optimizer's cost-based decisions, forcing a specific join method, index or access path. This manual method gives the DBA explicit control when statistics or transformations produce a poor plan.

Buffer busy waits arise when sessions contend for the same data blocks concurrently in the buffer cache. Multiple sessions accessing identical blocks serialise on buffer pins and latches, so the wait event points directly to block-level contention rather than I/O latency or parsing overhead.

A SQL Profile supplies auxiliary statistics and corrections to the optimizer for a specific statement, letting it derive a better plan without changing the SQL text. That improved cardinality and cost estimation is what raises optimizer accuracy.

Important for parsing/memory health.

ADDM (Automatic Database Diagnostic Monitor) analyses Automatic Workload Repository data to detect performance bottlenecks and generate actionable recommendations. This directly satisfies the stem's requirement, distinguishing it from AWR, which merely collects statistics, and SQL Tuning Advisor, which targets individual statements.

Setting STATISTICS_LEVEL to BASIC disables Automatic Workload Repository snapshots and Automatic Database Diagnostic Monitor analysis, since both depend on TYPICAL-level statistics collection. This satisfies the scenario's requirement to stop AWR and ADDM entirely, unlike TYPICAL or ALL, which retain them.

V$SQLSTATS provides statistics on SQL statements currently in the shared pool, aggregated by SQL_ID and plan hash value. It includes CPU_TIME, which is the cumulative CPU time consumed by the statement. This view is designed for efficient access to SQL statistics and is ideal for identifying high-CPU SQL in real time. It is updated frequently and is less resource-intensive than querying V$SQL directly for aggregation.

An SQL Profile stores auxiliary information, including optimizer hints and adjusted cardinality estimates, that the optimiser applies when parsing the statement, steering it toward the advisor's recommended plan without editing the SQL text or changing statistics.

By comparing estimated cardinality (from the optimizer) with actual rows (from runtime statistics) using DBMS_XPLAN.DISPLAY_CURSOR with ALLSTATS LAST, you can see if the optimizer's estimates are significantly off. If the estimates are inaccurate, it suggests stale or missing statistics. This directly confirms whether statistics are the cause of the suboptimal plan.

V$SQLAREA aggregates statistics per SQL statement across all cursors sharing the same text, including CPU usage, so it ranks the top statements resident in memory by CPU consumption. That matches the requirement to identify the highest-CPU SQL currently in memory.