OCP Performance Tuning And Diagnostics — 40 Questions

Question 1

You notice that Automatic SQL Plan Management is not evolving your plans as expected. What is the most likely cause?

Accepted Answer

The SPM Evolve Task is disabled. Automatic SQL Plan Management relies on the SPM Evolve Advisor task, which runs during maintenance windows to verify and evolve new plans. If this task is disabled (via DBMS_SPM.SET_EVOLVE_TASK_PARAMETER or DBMS_AUTO_TASK_ADMIN.DISABLE), no plans will ever be evolved regardless of other settings. This is the most direct and common cause of SPM not evolving plans.

Answer

SQL Plan Baselines are full

Answer

Statistics are gathered manually

Answer

The database is in NOARCHIVELOG mode

Question 2

An application query is failing to use a newly created index. You suspect the optimizer statistics are inaccurate. Which package should you use to gather statistics for this specific table?

Accepted Answer

DBMS_STATS.GATHER_TABLE_STATS. DBMS_STATS is the standard package for managing optimizer statistics.

Answer

DBMS_SQLTUNE

Answer

DBMS_UTILITY

Answer

DBMS_ADVISOR

Question 3

Which component of the Oracle Database uses the Automatic Diagnostic Repository (ADR) to store critical errors and performance incidents?

Accepted Answer

Automatic Diagnostic Repository (ADR). ADR is the centralized location for all diagnostic data, including alert logs and incident dumps.

Answer

Automatic Database Diagnostic Monitor (ADDM)

Answer

AWR

Answer

SQL Plan Management

Question 4

You want to capture a specific set of SQL statements and their execution plans to baseline them. Which package allows you to capture plans from the cursor cache?

Accepted Answer

DBMS_SPM. DBMS_SPM (SQL Plan Management) is the package that allows you to capture SQL statements and their execution plans from the cursor cache to create SQL plan baselines. It provides procedures like CAPTURE_CURSOR_CACHE_SQLSET or LOAD_PLANS_FROM_CURSOR_CACHE to capture plans and evolve them into baselines.

Answer

DBMS_SCHEDULER

Answer

DBMS_SQLTUNE

Answer

DBMS_STATS

Question 5

An application is experiencing high library cache lock contention. Which view is best suited to investigate this specific wait event?

Accepted Answer

V$SESSION_WAIT. V$SESSION_WAIT shows details about wait events for active sessions.

Answer

V$PARAMETER

Answer

V$SQL

Answer

V$LOCK

Question 6

You need to determine why a particular session is experiencing high 'db file sequential read' waits. Which view provides the most detailed information about the specific blocks being read?

Accepted Answer

V$SESSION_WAIT. V$SESSION_WAIT is correct because it exposes the current or last wait event for each session, including the P1, P2, and P3 parameters. For 'db file sequential read', P1 is the file number, P2 is the block number, and P3 is the number of blocks being read. This directly identifies the exact block being read by the session, which is the most detailed information available for diagnosing this wait event.

Answer

V$BH

Answer

V$SQL

Answer

V$FILESTAT

Answer

V$SQL_PLAN

Question 7

You are using SQL Plan Management. What happens when a new execution plan is discovered for a SQL statement that already has a baseline?

Accepted Answer

It is stored as an unaccepted plan. The new plan is marked as 'accepted' only if it is proven to be better than the existing plan; otherwise, it is stored as 'unaccepted'.

Answer

It is ignored by the optimizer

Answer

It is automatically accepted

Answer

It replaces the existing baseline

Question 8

An ADDM report suggests increasing the library cache size. What is the most appropriate action?

Accepted Answer

Increase SGA_TARGET. Oracle 23ai manages SGA memory automatically; adjusting the SGA_TARGET or SGA_MAX_SIZE is the primary way to influence this.

Answer

Manually set SHARED_POOL_SIZE

Answer

Drop and recreate the library cache

Answer

Rebuild the database

Question 9

You have a query that is using a nested loops join, but it should be using a hash join. The optimizer statistics are correct. How can you force a hash join without changing the SQL text?

Accepted Answer

Use a SQL Profile. SQL Plan Baselines or SQL Profiles can influence the optimizer without changing application code.

Answer

Change the STATISTICS_LEVEL

Answer

Use SQL Plan Management

Answer

Create an index

Question 10

Which advisor should you run to determine if your SGA and PGA memory settings are optimal?

Accepted Answer

Memory Advisor. The Memory Advisor in Oracle Enterprise Manager (or via DBMS_ADVISOR) analyzes current SGA and PGA memory allocations and provides recommendations to optimize them. It evaluates whether the instance's memory settings are appropriately sized for the workload, suggesting changes to parameters like SGA_TARGET, PGA_AGGREGATE_TARGET, and individual SGA components. This directly addresses the question of determining if SGA and PGA settings are optimal.

Answer

SQL Access Advisor

Answer

SQL Tuning Advisor

Answer

ADDM

Question 11

Which THREE pieces of information can be found in a SQL Tuning Advisor report?

Accepted Answer

Index recommendations. The report includes findings, recommendations, and execution statistics.

Answer

Instance parameter history

Answer

Database backup schedules

Question 12

A critical application query is experiencing performance issues due to stale optimizer statistics. You want to use the Optimizer Statistics Advisor to identify the root cause. Which action must you perform to trigger the advisor?

Accepted Answer

Call DBMS_STATS.REPORT_STATS_ADVISOR_TASK to generate a report on statistics-related issues.. The Optimizer Statistics Advisor is a diagnostic tool introduced in Oracle 12.2 that analyzes how statistics are being gathered and used, and it stores its findings in the SYSAUX tablespace as part of the Automated Maintenance Task framework. To view its output, you must invoke DBMS_STATS.REPORT_STATS_ADVISOR_TASK, which returns a CLOB report containing findings, recommendations, and actions for the default task named 'AUTO_STATS_ADVISOR_TASK'. This is the only option that directly triggers the advisor's reporting mechanism for statistics-related issues.

Answer

Run the AWR report and look for 'Stats' in the top wait events.

Answer

Enable the SQL Tuning Advisor for the specific SQL_ID.

Answer

Execute DBMS_STATS.GATHER_SCHEMA_STATS with the 'AUTO' option.

Question 13

Which THREE of the following are valid components or features of the Automatic SQL Tuning process in Oracle 23ai?

Accepted Answer

SQL Plan Management (SPM). SQL Plan Management (SPM), option A, is a valid component of Automatic SQL Tuning because it maintains SQL plan baselines that prevent performance regressions by ensuring the optimizer uses only verified, efficient plans. The Automatic SQL Tuning Advisor, option B, is correct because it is the core engine that automatically analyzes high-load SQL statements, generates tuning recommendations, and can implement SQL profiles without DBA intervention. SQL Tuning Sets, option C, are valid because they provide the input mechanism—a named set of SQL statements with their execution context—that the Automatic SQL Tuning Advisor analyzes during automatic tuning tasks. Option D, Automatic Maintenance Tasks, is not specific to Automatic SQL Tuning; it is the broader scheduler framework that runs many maintenance jobs (optimizer statistics, segment advisor, etc.), not a component of the SQL tuning process itself. Option E, ADDM, is a performance diagnostic tool that identifies bottlenecks and recommends actions, but it is not a component or feature of the Automatic SQL Tuning process.

Answer

Automatic Maintenance Tasks

Answer

Automatic Database Diagnostic Monitor (ADDM)

Question 14

You are the DBA for an Oracle Database 23ai production system. A critical reporting query has started performing poorly. You suspect the issue is related to the optimizer's choice of execution plan. You want to see the actual execution plan that was used for the most recent execution of this specific SQL statement, including the runtime statistics for each operation. Which of the following would you use to achieve this?

Accepted Answer

Query V$SQL_PLAN_STATISTICS_ALL for the SQL_ID of the statement.. The view V$SQL_PLAN_STATISTICS_ALL is specifically designed to combine the execution plan with runtime statistics for each operation. By querying this view using the SQL_ID, you can see the actual plan used and metrics like rows processed and elapsed time, which are crucial for identifying which step in the plan is causing the slowdown. This is the standard method for obtaining an actual execution plan with performance data.

Answer

Query V$SQLAREA and examine the PLAN_HASH_VALUE column.

Answer

Use the EXPLAIN PLAN FOR command followed by a query on PLAN_TABLE.

Answer

Use the DBMS_XPLAN.DISPLAY_AWR function with the SQL_ID.

Question 15

A critical reporting query against a partitioned SALES table suddenly begins performing a full table scan instead of partition pruning. You run DBMS_STATS.GATHER_TABLE_STATS with the default settings, but the optimizer still fails to prune. Upon investigation, you discover that the query's WHERE clause uses a bind variable on the partition key. Which optimizer feature should you enable to allow partition pruning with this bind variable?

Accepted Answer

Bind-aware partition pruning. Bind-aware partition pruning is the correct feature because it allows the optimizer to perform partition pruning at runtime when the partition key is compared to a bind variable. This avoids the need to hard-parse with literals and ensures that only relevant partitions are accessed, significantly improving performance for queries that filter on partition keys with bind variables.

Answer

Adaptive Cursor Sharing (ACS)

Answer

Partition-wise join

Answer

Bloom filtering

Question 16

You are managing an Oracle Database 23ai instance that experiences intermittent performance spikes. You have enabled the Automatic Workload Repository (AWR) and notice that a particular SQL statement has a high number of 'buffer busy waits'. You want to determine the specific segment and block type that is causing the contention. Which of the following would you query to find this information?

Accepted Answer

V$ACTIVE_SESSION_HISTORY. V$ACTIVE_SESSION_HISTORY captures sampled session activity, including wait events and their associated parameters. For 'buffer busy waits', the P1 and P2 columns represent the file and block numbers, which can be mapped to a segment and block type. This allows you to identify the exact hot block and its type, such as a segment header or data block, enabling targeted remediation.

Answer

V$SYSTEM_EVENT

Answer

V$SESSION_WAIT

Answer

V$SEGMENT_STATISTICS

Question 17

A developer reports that a query using a function-based index is not being used by the optimizer, despite the index being valid and the function matching the WHERE clause. The function is deterministic but has a high cost. You suspect the optimizer is not considering the index because of missing statistics. Which action should you take to provide the optimizer with statistics for the function-based index?

Accepted Answer

Run DBMS_STATS.GATHER_INDEX_STATS on the function-based index.. Gathering index statistics with DBMS_STATS.GATHER_INDEX_STATS provides the optimizer with the necessary data distribution information for the function-based index, allowing it to evaluate the cost of using the index versus a full table scan. Without these statistics, the optimizer may ignore the index, leading to suboptimal performance.

Answer

Create a SQL plan baseline for the query.

Answer

Enable the optimizer feature 'function-based index cost'.

Answer

Run DBMS_STATS.GATHER_TABLE_STATS with the CASCADE option.

Question 18

You are the lead DBA for an Oracle Database 23ai production system. A critical query that joins a large partitioned sales table with a small lookup table is sometimes fast, but occasionally takes minutes to complete. AWR reports show the query's plan uses a NESTED LOOPS join with an INDEX RANGE SCAN on the lookup table, but the number of rows returned from the sales table varies significantly depending on the bind variable values. Which diagnostic approach will best help you understand why the optimizer sometimes produces a suboptimal plan for this query?

Accepted Answer

Query V$SQL_CS_HISTOGRAM and V$SQL_CS_SELECTIVITY to examine the adaptive cursor sharing information for the SQL ID.. The query's performance varies with bind values, indicating that the optimizer is choosing different plans based on the selectivity of the bind variables. Adaptive cursor sharing is the feature that allows the optimizer to adapt the plan to different bind values. The dynamic performance views V$SQL_CS_HISTOGRAM and V$SQL_CS_SELECTIVITY expose the internal statistics that adaptive cursor sharing uses to decide when to switch plans, providing the most direct insight into the problem.

Answer

Use SQL Plan Management (SPM) to create a SQL plan baseline and force the optimizer to use the fastest plan.

Answer

Query DBA_HIST_SQLSTAT and DBA_HIST_SQL_PLAN for the SQL ID to compare historical execution statistics and plans across different time periods.

Answer

Generate an active SQL Monitor report for a problematic execution and examine the 'Plan' and 'Statistics' tabs for cardinality misestimates and bind variable details.

Question 19

You are an Oracle Database 23ai administrator. A critical OLTP application has been experiencing intermittent slowdowns. You suspect that the shared pool is undersized, leading to excessive parsing and contention. Which two actions should you take to diagnose and confirm shared pool contention? (Choose two.)

Accepted Answer

Query V$LIBRARYCACHE to check the GETHITRATIO and RELOADS columns.. Shared pool contention often manifests as excessive parsing and cursor aging. V$SGASTAT reveals free memory in the shared pool; low free memory indicates pressure. V$LIBRARYCACHE shows hit ratios and reloads; poor ratios and high reloads confirm that cursors are being aged out and reparsed. Together, these views provide direct evidence of shared pool contention.

Answer

Query V$PGASTAT to check PGA memory usage.

Answer

Query V$BH to check buffer cache hit ratios.

Answer

Query V$SYSSTAT for 'parse count (total)' and 'parse count (hard)'.

Question 20

You are an Oracle Database 23ai administrator. Users report that a batch job that runs a complex query with multiple joins is taking longer than usual. You examine the execution plan and notice that the optimizer has chosen a HASH JOIN for a join between two large tables, but the join condition is highly selective. You suspect that the optimizer's cardinality estimates are incorrect. Which action should you take to confirm your suspicion?

Accepted Answer

Run the query with the GATHER_PLAN_STATISTICS hint and then use DBMS_XPLAN.DISPLAY_CURSOR with the 'ALLSTATS LAST' format to compare estimated and actual rows.. To confirm that the optimizer's cardinality estimates are incorrect, you need to compare the estimated number of rows with the actual number of rows returned at each step of the execution plan. The GATHER_PLAN_STATISTICS hint causes the database to collect execution statistics, and DBMS_XPLAN.DISPLAY_CURSOR with the 'ALLSTATS LAST' format shows both the estimated and actual rows. This direct comparison is the definitive way to identify cardinality misestimates.

Answer

Check the OPTIMIZER_DYNAMIC_SAMPLING parameter to see if dynamic sampling is enabled, and if not, enable it at the session level.

Answer

Query V$SQL_PLAN_STATISTICS_ALL for the SQL ID to view the estimated and actual rows for each step of the execution plan.

Answer

Use the SQL Tuning Advisor to analyze the query and generate a report that includes cardinality feedback.