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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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.)
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?
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.)
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?
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?
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?
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?
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?
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?
You observe a significant performance regression after a database upgrade. You suspect that the optimizer has chosen a suboptimal execution plan. Which component of SQL Plan Management should you use to ensure the database only uses known, verified execution plans?
An administrator needs to identify the top wait events causing performance degradation in the database over the last hour. Which tool provides the most granular, session-level view of these wait events?
The Automatic SQL Plan Management (ASPM) feature is enabled. A new execution plan is identified for a high-load query that performs better during testing. How does the database verify this plan before evolving the baseline?
An administrator has been tasked with reducing the overhead of AWR snapshot collection. What is the most effective way to modify the snapshot interval?
You need to capture a specific set of SQL statements that are causing high CPU usage for later tuning. Which tool allows you to group these statements and transport them across different database environments?
You are the lead DBA for a busy online transaction processing (OLTP) database. Users report that a critical order-entry application has become slow. You run a query against V$SYSTEM_EVENT and notice that the 'log file sync' wait event has the highest total time waited. Which action should you take to address this issue?
You are analyzing a performance issue in your Oracle 23ai database. You suspect that a specific SQL statement is performing poorly due to a suboptimal execution plan. You want to capture the actual execution plan and runtime statistics for this statement while it is executing. Which tool should you use to achieve this with minimal overhead?
You are investigating a performance issue where a batch job that inserts millions of rows into a table with a unique index is running slowly. You notice a high number of 'enq: TX - index contention' waits. Which action is most likely to reduce this contention without compromising data integrity?
You are tuning a data warehouse query that performs a large hash join. The query is spilling to disk, and you want to determine if increasing the PGA_AGGREGATE_TARGET would help. Which of the following dynamic performance views would you query to see the current PGA memory usage and the number of multi-pass executions?
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.)
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?
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'?
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.)
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?
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?
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.)
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?
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?
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?
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?
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?
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?
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?
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?
You are a DBA for an Oracle Database 23ai instance. A developer reports that a specific SQL statement is performing poorly. You want to gather detailed execution statistics for this single statement to diagnose the issue. Which two methods can provide the actual execution plan and runtime statistics for this statement? (Choose two.)