OCP Performance Tuning And Diagnostics — 20 Questions

Question 1

You need to identify the top SQL statements consuming the most CPU resources in an Oracle 23ai database over the last hour. Which tool provides the most direct analysis of this performance issue?

Accepted Answer

Automatic Workload Repository (AWR) report. AWR reports provide aggregated data over a specific time range, making them ideal for identifying resource consumers.

Answer

SQL Tuning Advisor

Answer

Active Session History (ASH) report

Answer

SQL Plan Management

Question 2

To improve performance, you suspect an index is missing. Which advisor should you invoke to get recommendations for new indexes?

Accepted Answer

SQL Access Advisor. The SQL Access Advisor provides recommendations on schema design, including indexes and materialized views.

Answer

SQL Tuning Advisor

Answer

Memory Advisor

Answer

ADDM

Question 3

You have a query that is performing poorly due to a stale execution plan. You want to lock the current optimal plan to prevent the optimizer from changing it during subsequent runs. What should you do?

Accepted Answer

Use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE. SQL Plan Baselines allow you to store and fix execution plans for specific SQL statements.

Answer

Run DBMS_STATS.LOCK_TABLE_STATS

Answer

Set OPTIMIZER_FEATURES_ENABLE to a previous version

Answer

Use SQL Tuning Advisor to accept a new profile

Question 4

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. The SYS_AUTO_SPM_EVOLVE_TASK must be enabled and have a sufficient maintenance window to run.

Answer

SQL Plan Baselines are full

Answer

Statistics are gathered manually

Answer

The database is in NOARCHIVELOG mode

Question 5

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 6

You are tuning a complex SQL statement. You want to see the execution plan that the optimizer is currently choosing. Which dynamic performance view is most useful?

Accepted Answer

V$SQL_PLAN. V$SQL_PLAN shows the execution plan for statements currently in the library cache.

Answer

V$SESSION

Answer

V$SQLAREA

Answer

V$SYSSTAT

Question 7

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 8

A production database experiences a sudden performance degradation. You want to analyze the last 10 minutes of activity to pinpoint blocked sessions. Which approach is best?

Accepted Answer

Query V$ACTIVE_SESSION_HISTORY. ASH data is stored in memory and is the primary source for recent, short-term performance diagnostics.

Answer

Generate an AWR report for the last hour

Answer

Check the Alert Log for ORA errors

Answer

Run the SQL Access Advisor

Question 9

What is the primary function of ADDM in Oracle Database 23ai?

Accepted Answer

To identify performance bottlenecks and provide recommendations. ADDM analyzes AWR snapshots to identify performance bottlenecks and provide recommendations.

Answer

To capture SQL execution plans

Answer

To migrate data between instances

Answer

To automatically tune individual SQL statements

Question 10

Which parameter controls the frequency of AWR snapshot collection?

Accepted Answer

STATISTICS_LEVEL. STATISTICS_LEVEL controls the automatic collection of AWR snapshots.

Answer

PROCESSES

Answer

DB_CACHE_SIZE

Answer

OPTIMIZER_MODE

Question 11

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.LOAD_PLANS_FROM_CURSOR_CACHE is the standard way to load plans into the baseline.

Answer

DBMS_SCHEDULER

Answer

DBMS_SQLTUNE

Answer

DBMS_STATS

Question 12

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 13

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 includes P1, P2, and P3 columns which often identify the file and block being accessed during the wait.

Answer

V$BH

Answer

V$SQL

Answer

V$FILESTAT

Answer

V$SQL_PLAN

Question 14

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 15

You are performing a SQL tuning exercise. How do you create a SQL Tuning Set (STS) to be used as input for the SQL Tuning Advisor?

Accepted Answer

DBMS_SQLTUNE.CREATE_SQLSET. DBMS_SQLTUNE.CREATE_SQLSET is the required procedure to initialize an STS container.

Answer

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT

Answer

DBMS_SPM.CREATE_BASELINE

Answer

DBMS_STATS.CREATE_STAT_TABLE

Question 16

When reviewing an AWR report, you notice a high value for 'db time'. What does this indicate?

Accepted Answer

The total time spent by sessions in the database. DB Time represents the total time spent by foreground processes in the database, indicating overall load.

Answer

The time spent on disk I/O only

Answer

The time spent on instance background processes

Answer

The time the database was open

Question 17

Which view displays the current settings for the AWR retention period?

Accepted Answer

DBA_HIST_WR_CONTROL. DBA_HIST_WR_CONTROL contains the snapshot settings including retention and interval.

Answer

DBA_SNAPSHOTS

Answer

V$DATABASE

Answer

V$INSTANCE

Question 18

Which report provides a summary of SQL statements that have the highest 'Elapsed Time'?

Accepted Answer

AWR Report. The AWR report has a section specifically for Top SQL by Elapsed Time.

Answer

Alert Log

Answer

Listener Log

Answer

ADDM Report

Question 19

You have a query that is being incorrectly optimized because the statistics are not representative of the actual data distribution. What is the best method to handle this without changing the global statistics?

Accepted Answer

Use DBMS_STATS.SET_TABLE_PREFS. Table-specific statistics collection with specific parameters or using preference settings can help.

Answer

Run the SQL Tuning Advisor

Answer

Modify the init.ora parameter

Answer

Create a SQL Plan Baseline

Question 20

Which procedure in the DBMS_SPM package is used to evolve a SQL plan baseline?

Accepted Answer

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE is the standard procedure to evolve baselines.

Answer

DBMS_SPM.DROP_SQL_PLAN_BASELINE

Answer

DBMS_SPM.ALTER_SQL_PLAN_BASELINE

Answer

DBMS_SPM.LOAD_PLANS_FROM_SQLSET