OCP Performance Tuning And Diagnostics Practice Test 5 — 15 Questions

Question 1

Which TWO of these are common sources of performance degradation?

Accepted Answer

High resource contention. Suboptimal SQL and system-wide resource contention are the most common issues.

Answer

Renaming a table

Answer

Changing the database name

Answer

Adding a new user

Question 2

Which TWO components are essential for the operation of the Automatic Database Diagnostic Monitor (ADDM)?

Accepted Answer

AWR snapshots. ADDM relies on AWR snapshots (option C) because it analyzes the performance data captured in the Automatic Workload Repository between two snapshots to identify root causes and quantify their impact. It also requires an ADDM Analysis Task (option D), which is the actual diagnostic run that processes those snapshots and produces findings and recommendations. Without AWR snapshots there is no historical performance data to diagnose, and without an ADDM Analysis Task the diagnostic engine has no execution context. SQL Plan Baselines (A) are a SQL plan management feature for plan stability, not a prerequisite for ADDM. Data Pump files (B) are used for export/import operations and are unrelated to ADDM diagnostics. Listener control (E) manages network listener services and has no role in ADDM's internal analysis.

Answer

SQL Plan Baselines

Answer

Data Pump files

Answer

Listener control

Question 3

Which THREE actions can you take to improve execution plan stability?

Accepted Answer

Use SQL Profiles. SQL Profiles (C) improve plan stability by supplying optimizer hints and statistics adjustments that steer the optimizer toward a proven plan without changing application SQL. Freezing execution plans (D) locks a specific plan for a SQL statement so the optimizer cannot switch to a different, potentially worse plan. SQL Plan Baselines (E) capture and enforce known-good plans, allowing the optimizer to use only plans in the baseline and preventing regressions. Dropping and recreating indexes (A) is disruptive and can cause plan changes and instability, not stability. Increasing the SGA size daily (B) is arbitrary and does not directly stabilize execution plans.

Answer

Frequently drop and recreate indexes

Answer

Increase the SGA size every day

Question 4

You observe a significant performance regression after a database upgrade. You suspect that the optimizer has chosen a suboptimal execution plan. Which component of SQL Plan Management should you use to ensure the database only uses known, verified execution plans?

Accepted Answer

SQL Plan Baselines. SQL Plan Baselines are used to capture and freeze known good execution plans for SQL statements. When the optimizer considers a new plan, it compares it to the baseline; if the new plan is not verified as better, the baseline plan is used. This ensures that only verified plans are used, preventing performance regressions after an upgrade.

Answer

Optimizer Statistics Advisor

Answer

SQL Tuning Sets

Answer

Automatic SQL Tuning Advisor

Question 5

An administrator needs to identify the top wait events causing performance degradation in the database over the last hour. Which tool provides the most granular, session-level view of these wait events?

Accepted Answer

Active Session History (ASH). ASH provides real-time, session-level performance data, making it the ideal tool for analyzing short-term, granular performance spikes.

Answer

SQL Tuning Advisor

Answer

Automatic Database Diagnostic Monitor (ADDM)

Answer

Automatic Workload Repository (AWR)

Question 6

The Automatic SQL Plan Management (ASPM) feature is enabled. A new execution plan is identified for a high-load query that performs better during testing. How does the database verify this plan before evolving the baseline?

Accepted Answer

It executes the SQL statement using the new plan and compares its performance metrics against the current baseline.. The database performs a verification task where it executes the new plan and compares it against the baseline performance to decide if it should be accepted.

Answer

It flags the plan for manual DBA approval via the DBMS_SPM package.

Answer

It automatically promotes the plan based on the optimizer cost estimation.

Answer

It relies on the Advisor to run a full analysis of the explain plan tree.

Question 7

You are analyzing an AWR report and notice a high value for 'db file scattered read'. What is the most likely cause of this wait event?

Accepted Answer

Full table scans or index fast full scans.. 'db file scattered read' is typically associated with full table scans or index fast full scans where multiple blocks are read into non-contiguous memory locations.

Answer

Insufficient PGA memory for sorting operations.

Answer

Excessive log file sync waits.

Answer

Heavy use of nested loop joins.

Question 8

An administrator has been tasked with reducing the overhead of AWR snapshot collection. What is the most effective way to modify the snapshot interval?

Accepted Answer

Use the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure.. The DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure is the official method to adjust snapshot frequency and retention policies.

Answer

Edit the SPFILE to change the statistics_level to 'BASIC'.

Answer

Update the SGA_TARGET parameter.

Answer

Manually delete old records from the DBA_HIST_SNAPSHOT view.

Question 9

You need to capture a specific set of SQL statements that are causing high CPU usage for later tuning. Which tool allows you to group these statements and transport them across different database environments?

Accepted Answer

SQL Tuning Sets. SQL Tuning Sets (STS) are designed to capture a specific set of SQL statements along with their execution context, and they can be exported and transported to other databases for tuning or plan comparison. This makes them the correct tool for grouping high-CPU SQL and moving it across environments.

Answer

SQL Plan Baselines

Answer

ADDM Findings

Answer

AWR Snapshots

Question 10

When using the SQL Tuning Advisor to analyze a SQL statement, which THREE types of recommendations can it provide to improve performance?

Accepted Answer

Creating a SQL profile. The SQL Tuning Advisor can suggest creating new indexes, refreshing optimizer statistics, and creating SQL profiles to alter the execution plan.

Answer

Restructuring the SQL query syntax

Answer

Renaming database tables

Question 11

Which TWO types of reports are most useful for diagnosing long-term database performance trends?

Accepted Answer

AWR Report. The AWR Report (B) is correct because Automatic Workload Repository snapshots persist performance data over long retention periods, enabling comparison of load, wait events, and top SQL across days or weeks to reveal long-term trends. The ADDM Report (E) is correct because Automatic Database Diagnostic Monitor analyzes AWR snapshots and produces findings and recommendations that track recurring performance issues over time, making it well suited for trend diagnosis. V$SESSION_WAIT (A) is a dynamic view showing only current or very recent wait activity, so it lacks historical depth. ASH Report (C) samples active sessions at fine granularity but typically covers short windows, not long-term trends. SQL Trace with tkprof (D) captures a single session's statement-level execution details for point-in-time tuning, not multi-period trend analysis.

Answer

V$SESSION_WAIT view

Answer

ASH Report

Answer

SQL Trace (tkprof)

Question 12

You are the lead DBA for a busy online transaction processing (OLTP) database. Users report that a critical order-entry application has become slow. You run a query against V$SYSTEM_EVENT and notice that the 'log file sync' wait event has the highest total time waited. Which action should you take to address this issue?

Accepted Answer

Tune the redo log file I/O by moving them to faster storage.. The 'log file sync' wait event indicates that user sessions are waiting for the log writer (LGWR) to write redo entries to disk during commit operations. This wait is predominantly caused by slow I/O on the redo log files. To resolve this, the DBA should focus on improving the performance of the storage where the redo logs reside, such as by moving them to faster disks or SSDs. Increasing the redo log buffer size or adjusting DBWn processes does not directly address the I/O bottleneck that LGWR faces.

Answer

Increase the size of the redo log buffer.

Answer

Reduce the number of commits performed by the application.

Answer

Increase the number of database writer (DBWn) processes.

Question 13

You are analyzing a performance issue in your Oracle 23ai database. You suspect that a specific SQL statement is performing poorly due to a suboptimal execution plan. You want to capture the actual execution plan and runtime statistics for this statement while it is executing. Which tool should you use to achieve this with minimal overhead?

Accepted Answer

Use the DBMS_XPLAN.DISPLAY_CURSOR function with the 'ALLSTATS LAST' format.. To capture the actual execution plan and runtime statistics for a specific SQL statement with minimal overhead, the DBMS_XPLAN.DISPLAY_CURSOR function with the 'ALLSTATS LAST' format is the most efficient method. It retrieves the plan and statistics from the library cache for the last execution of the cursor, leveraging data that the database already collects. This avoids the overhead of enabling SQL trace or re-executing the statement with EXPLAIN PLAN, and it provides the actual rows and other runtime metrics needed for tuning.

Answer

Enable SQL trace for the session and use TKPROF.

Answer

EXPLAIN PLAN FOR statement;

Answer

Query the V$SQL_PLAN_STATISTICS_ALL view for the SQL ID.

Question 14

You are investigating a performance issue where a batch job that inserts millions of rows into a table with a unique index is running slowly. You notice a high number of 'enq: TX - index contention' waits. Which action is most likely to reduce this contention without compromising data integrity?

Accepted Answer

Use a reverse key index.. A reverse key index is the correct solution because it distributes index entries across many blocks, eliminating the hot spot on the rightmost leaf block that occurs with sequentially increasing keys. This reduces contention during high-volume inserts while preserving uniqueness. Other options either do not address the root cause or could worsen the situation.

Answer

Increase the PCTFREE value for the index.

Answer

Rebuild the index with a larger block size.

Answer

Decrease the INITRANS value for the index.

Question 15

You are tuning a data warehouse query that performs a large hash join. The query is spilling to disk, and you want to determine if increasing the PGA_AGGREGATE_TARGET would help. Which of the following dynamic performance views would you query to see the current PGA memory usage and the number of multi-pass executions?

Accepted Answer

V$PGASTAT. V$PGASTAT is the primary view for monitoring PGA memory allocation and work area execution efficiency. It includes statistics such as 'aggregate PGA target parameter', 'total PGA allocated', 'cache hit percentage', and counts of optimal, one-pass, and multi-pass executions. By checking the number of multi-pass executions, you can determine if increasing PGA_AGGREGATE_TARGET would reduce disk spills and improve performance.

Answer

V$PROCESS

Answer

V$SQL_WORKAREA

Answer

V$SESSTAT