OCP Performance Tuning And Diagnostics — Timed 15 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