OCP Performance Tuning And Diagnostics Practice Test 6 — 15 Questions

Question 1

You are diagnosing a performance issue in an Oracle Database 23ai instance. You suspect that the issue is related to the optimizer's cardinality estimates. You want to gather additional information to confirm your suspicion. Which two of the following would provide you with the optimizer's estimated cardinality and the actual cardinality for each step of an execution plan? (Choose two.)

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

Accepted Answer

Using the /*+ GATHER_PLAN_STATISTICS */ hint and then querying V$SQL_PLAN_STATISTICS_ALL.. To compare estimated and actual cardinality, you need a method that captures runtime execution statistics. The GATHER_PLAN_STATISTICS hint causes the optimizer to collect actual row counts during execution, which are then visible in V$SQL_PLAN_STATISTICS_ALL. Similarly, DBMS_XPLAN.DISPLAY_CURSOR with 'ALLSTATS LAST' shows the plan with both estimated (E-Rows) and actual (A-Rows) cardinalities for the last execution. Both approaches are valid and commonly used for this purpose.

Answer

Querying V$SQLAREA and looking at the OPTIMIZER_COST column.

Answer

Querying V$SQL_PLAN for the SQL_ID and examining the CARDINALITY column.

Answer

Using the EXPLAIN PLAN FOR command and then querying PLAN_TABLE for the CARDINALITY column.

Question 2

You are analyzing a performance degradation in a data warehouse. The AWR report shows a high number of 'cell single block physical read' waits on Exadata. You suspect that the storage index is not being used effectively for a particular query. Which action should you take to improve the effectiveness of the storage index for this query?

Accepted Answer

Ensure that the query predicates are not using functions on the columns.. The storage index relies on predicates that reference columns directly, without functions or expressions, to filter out storage regions. If a predicate uses a function on a column, the storage index cannot be applied, leading to unnecessary physical reads. Ensuring predicates are on bare columns allows the storage index to function optimally and reduce I/O.

Answer

Enable the 'cell_offload_processing' parameter.

Answer

Recreate the table with a larger block size.

Answer

Increase the size of the Exadata Smart Flash Cache.

Question 3

You are the DBA for an Oracle Database 23ai instance. Users report that a particular application module is slow. You query V$ACTIVE_SESSION_HISTORY and see a high number of samples for the wait event 'log file sync'. You want to determine which sessions and SQL statements are contributing most to this wait. Which of the following would you use to find the top sessions and SQL_IDs associated with 'log file sync'?

Accepted Answer

Query V$ACTIVE_SESSION_HISTORY filtering on the event and group by session_id and sql_id.. V$ACTIVE_SESSION_HISTORY samples active sessions and records the wait event, session ID, and SQL ID for each sample. By filtering on the 'log file sync' event and aggregating by session_id and sql_id, you can identify the top contributors to this wait. This is the standard method for drilling down from a system-wide wait event to the specific sessions and SQL statements causing it.

Answer

Query V$SQL and order by ELAPSED_TIME descending.

Answer

Query V$SESSION_EVENT and order by TIME_WAITED descending.

Answer

Query V$SYSSTAT and look at the 'user commits' statistic.

Question 4

You are diagnosing a performance issue where a specific SQL statement is consuming excessive CPU. You have identified the SQL_ID and want to gather detailed execution statistics for this statement to understand its behavior. Which two methods can you use to obtain execution statistics for this SQL statement? (Choose two.)

Accepted Answer

Use DBMS_XPLAN.DISPLAY_CURSOR with the 'ALLSTATS LAST' format.. DBMS_SQL_MONITOR.REPORT_SQL_MONITOR and DBMS_XPLAN.DISPLAY_CURSOR with 'ALLSTATS LAST' are both valid methods to obtain detailed execution statistics for a specific SQL statement. SQL Monitor provides a comprehensive report including wait events and row source statistics, while DISPLAY_CURSOR with ALLSTATS LAST shows the execution plan with actual runtime statistics per operation.

Answer

Query V$SQL_PLAN_STATISTICS_ALL for the SQL_ID.

Answer

Query DBA_HIST_SQLSTAT for the SQL_ID.

Answer

Query V$SQLSTATS for the SQL_ID.

Question 5

You are an Oracle Database 23ai administrator. Users report that a critical order-entry form intermittently stalls for several seconds when submitting a row. You run an AWR report for the busy period and notice a high average 'row lock wait time' and a significant number of 'enq: TX - row lock contention' waits. Which action should you take first to diagnose the issue?

Accepted Answer

Query V$LOCK to identify the blocking session and the locked object.. Row lock contention occurs when one session holds a lock on a row that another session needs to modify. The first diagnostic step is to identify the blocking session and the object involved. V$LOCK provides this information by showing lock holders and requesters. Once identified, you can decide whether to wait, commit, or terminate the blocker.

Answer

Increase the value of the DB_BLOCK_CHECKING parameter to detect corruption.

Answer

Reduce the value of the PGA_AGGREGATE_TARGET to free memory for locks.

Answer

Run ADDM to automatically resolve the row lock contention.

Question 6

You are an Oracle Database 23ai administrator. A batch job that performs many small random reads on a large table is experiencing high 'db file sequential read' wait times. The table is stored in a tablespace with a block size of 8 KB. You suspect that the storage subsystem is the bottleneck. Which action would best confirm whether the wait time is due to slow disk I/O?

Accepted Answer

Query V$FILESTAT and compare the average read time per file.. The 'db file sequential read' wait event indicates single-block reads, often from indexes. To confirm if the storage subsystem is slow, you need to measure average I/O latency. V$FILESTAT provides cumulative read time and read count per file, allowing you to calculate average read time. High average read time points to slow disk I/O as the root cause.

Answer

Check the value of the DB_CACHE_SIZE parameter to see if the buffer cache is too small.

Answer

Run an EXPLAIN PLAN on the batch job's SQL statements to see if full table scans are used.

Answer

Use the SQL Tuning Advisor to analyze the batch SQL and recommend indexes.

Question 7

A DBA is analyzing an AWR report for an Oracle Database 23ai instance and notices a high number of 'enq: TX - row lock contention' waits. Which two actions should the DBA take to diagnose and resolve this issue? (Choose two.)

Accepted Answer

Query V$LOCK and V$SESSION to identify the blocking session and the locked object.. Row lock contention occurs when one transaction holds a lock on a row that another transaction needs. The first step is to identify the blocking session and the locked object using V$LOCK and V$SESSION. Then, examining the SQL of both sessions helps understand why the lock is held. This allows the DBA to tune the transaction or application to reduce lock duration. Increasing undo tablespaces or DML_LOCKS does not address the root cause.

Answer

Increase the number of undo tablespaces to reduce contention.

Answer

Enable automatic undo management and increase the undo retention period.

Answer

Set the parameter DML_LOCKS to a higher value.

Question 8

You are an Oracle Database 23ai administrator. A developer complains that a query joining two large tables is spilling to disk, and the session shows high 'direct path write temp' waits. The database uses Automatic Shared Memory Management (ASMM) and Automatic PGA Memory Management. Which action should you take to allow the query to complete without disk spills?

Accepted Answer

Increase the PGA_AGGREGATE_TARGET parameter.. Automatic PGA Memory Management dynamically allocates memory to work areas based on PGA_AGGREGATE_TARGET. When a query spills to disk, it means the allocated work area was insufficient. Increasing PGA_AGGREGATE_TARGET provides more memory for sorts and hash joins, reducing temp space usage and improving performance. This is the direct solution for the described symptom.

Answer

Set the WORKAREA_SIZE_POLICY parameter to MANUAL and configure SORT_AREA_SIZE.

Answer

Increase the SGA_TARGET parameter.

Answer

Increase the DB_CACHE_SIZE parameter to cache more blocks and reduce temp usage.

Question 9

A DBA is troubleshooting a performance issue in Oracle Database 23ai. A query that previously used an index full scan now performs a full table scan. The DBA suspects that the optimizer's statistics are stale. Which action should the DBA take to confirm this suspicion?

Accepted Answer

Examine the execution plan and compare the estimated cardinality with the actual rows processed using DBMS_XPLAN.DISPLAY_CURSOR with the ALLSTATS LAST option.. To confirm that stale statistics caused the optimizer to choose a full table scan, the DBA should compare the optimizer's estimated cardinality with the actual rows processed. If the estimates are far off, it indicates that the statistics do not reflect the current data. DBMS_XPLAN.DISPLAY_CURSOR with ALLSTATS LAST provides this comparison. Merely checking LAST_ANALYZED dates or gathering statistics does not confirm the cause.

Answer

Query DBA_TAB_STATISTICS and DBA_IND_STATISTICS to compare LAST_ANALYZED dates with the table modification timestamps.

Answer

Use DBMS_STATS.GATHER_TABLE_STATS with the default parameters and see if the plan changes.

Answer

Check the OPTIMIZER_MODE parameter to ensure it is set to ALL_ROWS.

Question 10

You are an Oracle Database 23ai administrator. A user reports that a specific SQL query is running slowly. You want to see the execution plan that the optimizer actually used for the most recent execution of that query. Which dynamic performance view should you query?

Accepted Answer

V$SQL_PLAN. To see the actual execution plan used for a recently executed SQL statement, you query V$SQL_PLAN. It stores the plan for each child cursor in the library cache, including the operations and their order. By joining with V$SQL, you can identify the specific SQL_ID and CHILD_NUMBER, and then retrieve the plan steps.

Answer

V$SESSION_LONGOPS

Answer

V$SQLAREA

Answer

DBA_HIST_SQL_PLAN

Question 11

You are an Oracle Database 23ai administrator. A developer complains that a query against a table with a recently added column is returning stale results. You suspect that the optimizer's statistics are outdated. Which task should you perform to refresh the statistics for this table?

Accepted Answer

Execute DBMS_STATS.GATHER_TABLE_STATS for the table.. The most direct and recommended way to refresh statistics for a specific table is to use the DBMS_STATS.GATHER_TABLE_STATS procedure. It collects current statistics that the optimizer needs to make accurate cardinality estimates. The other options either use deprecated methods, change unrelated parameters, or rely on automatic processes that do not provide immediate results.

Answer

Enable automatic statistics collection by setting STATISTICS_LEVEL to TYPICAL.

Answer

Run ANALYZE TABLE ... COMPUTE STATISTICS.

Answer

Set the OPTIMIZER_MODE initialization parameter to FIRST_ROWS.

Question 12

You are managing an Oracle Database 23ai instance. A critical application query is performing poorly. You have identified the SQL ID and want to capture a detailed execution plan with runtime statistics for a specific execution. The query is currently running and has been for several minutes. Which action should you take to get the most detailed diagnostic information?

Accepted Answer

Generate an active SQL Monitor report using DBMS_SQL_MONITOR.REPORT_SQL_MONITOR for the SQL ID.. SQL Monitor is specifically designed to monitor long-running or parallel SQL statements in real time. It captures detailed execution statistics, including actual rows, memory usage, time, and wait events for each operation. The report generated by DBMS_SQL_MONITOR.REPORT_SQL_MONITOR provides a comprehensive view of the query's performance while it is running, making it the ideal tool for this scenario.

Answer

Use DBMS_XPLAN.DISPLAY_AWR with the SQL ID to get the plan from AWR.

Answer

Query V$SQL_PLAN for the SQL ID to retrieve the execution plan.

Answer

Enable SQL tracing for the session using DBMS_SESSION.SET_SQL_TRACE and then examine the trace file.

Question 13

You are the lead DBA for an Oracle Database 23ai production system. Users report that a critical order-entry application becomes unresponsive for several seconds every few minutes. While monitoring, you notice a significant number of 'log file sync' wait events in V$SYSTEM_EVENT. Which action is most likely to reduce this contention?

Accepted Answer

Configure the redo log files on faster storage with lower latency.. The 'log file sync' wait event is directly related to the time a user session waits for the log writer to write redo to disk during a commit. The primary factor affecting this wait is the performance of the storage where the online redo logs reside. Moving the redo logs to faster storage with lower latency reduces the write time, thereby reducing the wait. Other options, such as increasing buffer size or adding log groups, do not address the commit path latency.

Answer

Increase the number of redo log groups.

Answer

Increase the size of the redo log buffer.

Answer

Enable asynchronous I/O for the redo log files.

Question 14

You are managing an Oracle Database 23ai instance where a specific SQL statement is consuming excessive CPU. You want to identify the top SQL statements by CPU usage in real time. Which dynamic performance view should you query?

Accepted Answer

V$SQLSTATS. To identify top SQL statements by CPU usage in real time, you should query a view that aggregates CPU time per SQL statement and is optimized for quick access. V$SQLSTATS is designed for this purpose, providing cumulative CPU_TIME and other statistics for SQL statements in the shared pool. It is more efficient than V$SQLAREA and more granular than V$SYSSTAT. V$SESSION does not aggregate CPU per SQL. Therefore, V$SQLSTATS is the correct choice.

Answer

V$SQLAREA

Answer

V$SYSSTAT

Answer

V$SESSION

Question 15

You are analyzing an AWR report for an Oracle Database 23ai instance. You notice that the 'DB Time' is significantly higher than the 'Elapsed Time' for the snapshot interval. What does this indicate?

Accepted Answer

There is significant concurrency; multiple sessions are active simultaneously.. In an AWR report, Elapsed Time is the duration of the snapshot interval in wall-clock time. DB Time is the total time spent by all foreground sessions in database calls, which includes CPU time and wait time. If DB Time is greater than Elapsed Time, it means that on average, more than one session was active at the same time. This is a measure of average concurrency. For instance, if DB Time is 120 minutes and Elapsed Time is 60 minutes, the average concurrency is 2. This is normal for a multi-user system and does not necessarily indicate a problem, but it helps in understanding the workload.

Answer

The database is experiencing a high number of user calls.

Answer

The database instance is running on a single CPU.

Answer

The AWR snapshot interval was too short.