OCP Performance Tuning And Diagnostics Practice Test 2 — 15 Questions

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

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 2

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 3

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 4

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 5

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.

Question 6

You are an Oracle Database 23ai administrator investigating a performance issue where a specific SQL statement is consuming excessive CPU. You want to analyze the SQL statement's execution plan and identify potential causes. Which two actions can you take to gather the necessary information? (Choose two.)

Accepted Answer

Query V$SQLSTATS for the SQL_ID to view execution statistics such as CPU_TIME, ELAPSED_TIME, and BUFFER_GETS.. To diagnose a SQL statement consuming excessive CPU, you need to gather both its execution statistics and its execution plan. Querying V$SQLSTATS provides aggregated metrics like CPU time and buffer gets, confirming the statement's resource usage. Using DBMS_XPLAN.DISPLAY_CURSOR retrieves the execution plan, allowing you to identify CPU-intensive operations such as sorts or hash joins. Together, these actions give a comprehensive view of the statement's behavior and potential causes.

Answer

Generate an AWR report for the time period when the issue occurred and look at the 'SQL ordered by CPU Time' section.

Answer

Use DBMS_SQLTUNE.REPORT_SQL_MONITOR to generate a SQL Monitor report for the SQL_ID.

Answer

Query V$SESSION_EVENT for the session to see wait events related to CPU.

Question 7

You are diagnosing a performance issue in an Oracle Database 23ai instance. A query that normally runs in milliseconds is now taking several seconds. You suspect that the execution plan has changed due to a stale statistic. Which action should you take first to confirm your suspicion?

Accepted Answer

Query DBA_HIST_SQL_PLAN to compare the current plan with historical plans.. To confirm that a query's execution plan has changed, the most reliable method is to compare the current plan with historical plans. DBA_HIST_SQL_PLAN, populated by AWR snapshots, retains a history of execution plans for SQL statements. By querying this view for the SQL_ID of the problematic query, you can see previous plans and compare them to the current plan in V$SQL_PLAN. This directly verifies a plan regression, which is the suspected cause. Other options may indicate potential causes but do not provide the definitive comparison.

Answer

Check the LAST_ANALYZED column in DBA_TAB_STATISTICS for the tables involved.

Answer

Run an EXPLAIN PLAN for the query and compare it with the plan in V$SQL_PLAN.

Answer

Examine the NOTE column in V$SQL for the query.

Question 8

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. The AWR report is the most direct tool for identifying top SQL by CPU consumption over a specific time period such as the last hour. AWR automatically captures snapshots of performance statistics, including SQL ordered by CPU time, and the report presents this data in a ready-to-read format. ASH provides session-level sampling for shorter, more granular analysis, but AWR is the standard tool for hourly or longer-range top-SQL identification.

Answer

SQL Tuning Advisor

Answer

Active Session History (ASH) report

Answer

SQL Plan Management

Question 9

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 10

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. DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE loads existing plans from the cursor cache into SQL Plan Baselines, allowing you to lock the current optimal plan. This prevents the optimizer from changing it because the baseline will be used.

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 11

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 12

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 13

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 14

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 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