62 questions · Performance Tuning And Diagnostics · All types, answers revealed
When reviewing an AWR report, you notice a high value for 'db time'. What does this indicate?
DB Time is the sum of CPU and wait time for all user sessions.
Why this answer
DB Time represents the total time spent by foreground processes in the database, indicating overall load.
Which THREE factors influence the optimizer's choice of an execution plan?
Statistics are the foundation of cost estimation.
Why this answer
The optimizer considers statistics, system parameters, and schema objects.
Which TWO views can be used to monitor current session performance?
Gives session wait event data.
Why this answer
V$SESSION and V$SESSION_WAIT are standard tools for real-time monitoring.
When running the SQL Tuning Advisor, what does the 'Accept' action do?
Accepting applies the profile to influence the optimizer.
Why this answer
Accepting a recommendation (like a profile) implements it in the database for future executions.
Which background process is responsible for capturing AWR snapshots?
MMNL performs background tasks, including AWR snapshotting.
Why this answer
The MMNL (Manageability Monitor) process is responsible for AWR snapshots.
Which of these is the most effective way to address performance issues caused by suboptimal plans?
SPM enforces stable, optimal execution plans.
Why this answer
SQL Plan Management is specifically designed to manage and enforce optimal plans.
Which parameter controls the frequency of AWR snapshot collection?
Setting this to TYPICAL or ALL enables AWR snapshots.
Why this answer
STATISTICS_LEVEL controls the automatic collection of AWR snapshots.
When using the SQL Tuning Advisor to analyze a SQL statement, which THREE types of recommendations can it provide to improve performance?
SQL profiles provide extra information to the optimizer to produce better plans.
Why this answer
The SQL Tuning Advisor can suggest creating new indexes, refreshing optimizer statistics, and creating SQL profiles to alter the execution plan.
Which TWO components are essential for the operation of the Automatic Database Diagnostic Monitor (ADDM)?
ADDM requires this historical data.
Why this answer
ADDM needs AWR snapshots and the ADDM analysis task.
Which THREE items are required for the Automatic SQL Plan Management to effectively stabilize plans?
The core component for plan storage.
Why this answer
SPM requires SQL baselines, a plan history, and an evolution process.
Which THREE pieces of information can be found in a SQL Tuning Advisor report?
Advisor analyzes access paths.
Why this answer
The report includes findings, recommendations, and execution statistics.
You have a high volume of parse time in your database. What could be the cause?
This causes excessive hard parses for each new literal value.
Why this answer
Hard parsing occurs when SQL is not shared, often due to literals instead of bind variables.
You are using SQL Plan Management. What happens when a new execution plan is discovered for a SQL statement that already has a baseline?
New plans are kept as unaccepted until verified.
Why this answer
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'.
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?
This package manages SQL Plan Baselines.
Why this answer
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE is the standard way to load plans into the baseline.
What is the purpose of the 'ASH' report?
ASH is designed for recent, high-frequency snapshots.
Why this answer
ASH reports analyze transient problems by looking at recent active session history.
You notice that Automatic SQL Plan Management is not evolving your plans as expected. What is the most likely cause?
If the task is disabled, the system will not automatically evolve plans.
Why this answer
The SYS_AUTO_SPM_EVOLVE_TASK must be enabled and have a sufficient maintenance window to run.
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?
Preferences allow granular control over how stats are gathered for a specific object.
Why this answer
Table-specific statistics collection with specific parameters or using preference settings can help.
Which feature allows you to capture SQL statements from the cursor cache and load them into a SQL Tuning Set?
This procedure extracts SQL from the library cache.
Why this answer
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET is the procedure used to pull queries from memory.
What does the 'Elapsed Time' in a SQL statement represent?
Elapsed time includes both CPU and wait time.
Why this answer
It is the total wall-clock time spent executing the statement from start to finish.
Which advisor should you run to determine if your SGA and PGA memory settings are optimal?
Memory Advisor provides tuning suggestions for SGA/PGA.
Why this answer
Memory Advisor identifies if memory pools are undersized or oversized.
An ADDM report suggests increasing the library cache size. What is the most appropriate action?
Increasing SGA_TARGET allows the automatic shared memory manager to allocate more to the shared pool.
Why this answer
Oracle 23ai manages SGA memory automatically; adjusting the SGA_TARGET or SGA_MAX_SIZE is the primary way to influence this.
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?
V$ACTIVE_SESSION_HISTORY captures session activity in memory for recent performance analysis.
Why this answer
ASH data is stored in memory and is the primary source for recent, short-term performance diagnostics.
Which THREE of the following are components of the Oracle performance tuning lifecycle?
Third step is implementation.
Why this answer
Tuning is an iterative process involving identification, analysis, and implementation.
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?
SQL Tuning Sets provide the functionality to capture and export groups of SQL statements for tuning.
Why this answer
SQL Tuning Sets are specifically designed to capture, store, and transport SQL statements along with their execution context and statistics.
What does the 'SQL ID' in an Oracle database represent?
SQL IDs allow tracking of specific statements.
Why this answer
The SQL ID is a unique hash of the SQL text, used to identify statements in the library cache.
You notice that a specific query is performing very slowly due to excessive 'direct path read' waits. What is the most likely cause?
Full table scans bypass the buffer cache, causing direct path reads.
Why this answer
Direct path reads often occur during full table scans when the data is not in the buffer 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?
P1/P2/P3 reveal file and block numbers for I/O waits.
Why this answer
V$SESSION_WAIT includes P1, P2, and P3 columns which often identify the file and block being accessed during the wait.
Which procedure in the DBMS_SPM package is used to evolve a SQL plan baseline?
This verifies and promotes unaccepted plans to accepted.
Why this answer
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE is the standard procedure to evolve baselines.
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 SQL Profile can provide hints to the optimizer to steer it toward a hash join.
Why this answer
SQL Plan Baselines or SQL Profiles can influence the optimizer without changing application code.
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?
This creates the container to hold SQL statements.
Why this answer
DBMS_SQLTUNE.CREATE_SQLSET is the required procedure to initialize an STS container.
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?
AWR provides historical performance data aggregated over time intervals.
Why this answer
AWR reports provide aggregated data over a specific time range, making them ideal for identifying resource consumers.
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?
This is the correct procedural call to generate insights into statistics management.
Why this answer
The Optimizer Statistics Advisor can be invoked using the DBMS_STATS.REPORT_STATS_ADVISOR_TASK function to analyze and report on statistics health.
You are seeing 'enq: TX - row lock contention' in your performance reports. What does this mean?
TX locks represent transaction row-level locking.
Why this answer
TX lock contention indicates that multiple sessions are trying to update the same rows.
You are analyzing an AWR report and notice a high value for 'db file scattered read'. What is the most likely cause of this wait event?
These operations read multiple blocks into scattered memory buffers.
Why this answer
'db file scattered read' is typically associated with full table scans or index fast full scans where multiple blocks are read into non-contiguous memory locations.
Which TWO of these are common sources of performance degradation?
Locks and latches impact performance.
Why this answer
Suboptimal SQL and system-wide resource contention are the most common issues.
Which of the following is true regarding SQL Plan Baselines?
The goal of SPM is to ensure plan stability.
Why this answer
They allow the database to maintain a stable execution plan even if the environment (stats, indexes) changes.
In which view can you find information about the current SQL Plan Baselines?
This view lists all defined SQL plan baselines.
Why this answer
DBA_SQL_PLAN_BASELINES is the primary view for information about SQL plan baselines.
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?
SQL Plan Baselines explicitly control plan evolution to prevent undesirable changes.
Why this answer
SQL Plan Baselines are used to ensure that only verified execution plans are used for a specific SQL statement, protecting against performance regressions.
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?
This function creates a baseline from the cursor cache, locking the plan.
Why this answer
SQL Plan Baselines allow you to store and fix execution plans for specific SQL statements.
Which TWO types of reports are most useful for diagnosing long-term database performance trends?
AWR reports provide aggregated data over user-defined periods, perfect for trend analysis.
Why this answer
AWR reports and ADDM reports are standard tools for analyzing performance trends over extended periods (hours, days, or weeks).
Which component of the Oracle Database uses the Automatic Diagnostic Repository (ADR) to store critical errors and performance incidents?
ADR is the repository that stores diagnostic data.
Why this answer
ADR is the centralized location for all diagnostic data, including alert logs and incident dumps.
Which report provides a summary of SQL statements that have the highest 'Elapsed Time'?
AWR reports contain 'Top SQL' sections categorized by various metrics.
Why this answer
The AWR report has a section specifically for Top SQL by Elapsed Time.
An administrator has been tasked with reducing the overhead of AWR snapshot collection. What is the most effective way to modify the snapshot interval?
This is the correct administrative interface for AWR configuration.
Why this answer
The DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure is the official method to adjust snapshot frequency and retention policies.
How can you view the recommendations made by the Automatic Database Diagnostic Monitor (ADDM)?
This view contains the output findings for all advisors, including ADDM.
Why this answer
ADDM recommendations can be viewed in AWR or via the DBA_ADVISOR_FINDINGS view.
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?
ASH captures active session state every second, providing the necessary granularity for real-time analysis.
Why this answer
ASH provides real-time, session-level performance data, making it the ideal tool for analyzing short-term, granular performance spikes.
Which THREE actions can you take to improve execution plan stability?
Provides hints for stable plans.
Why this answer
Stability is achieved through baselines, profiles, and optimizer plan stability features.
An application is experiencing high library cache lock contention. Which view is best suited to investigate this specific wait event?
This view provides real-time information on what a session is waiting for.
Why this answer
V$SESSION_WAIT shows details about wait events for active sessions.
When using SQL Tuning Advisor, what is a 'SQL Tuning Set' (STS)?
STS groups SQL, plans, and bind variables for tuning analysis.
Why this answer
An STS is a collection of SQL statements with their associated execution context.
Which view displays the current settings for the AWR retention period?
This view stores the AWR configuration parameters.
Why this answer
DBA_HIST_WR_CONTROL contains the snapshot settings including retention and interval.
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?
This view provides the execution plan for cached SQL.
Why this answer
V$SQL_PLAN shows the execution plan for statements currently in the library cache.
To improve performance, you suspect an index is missing. Which advisor should you invoke to get recommendations for new indexes?
This advisor analyzes access paths and recommends indexes/materialized views.
Why this answer
The SQL Access Advisor provides recommendations on schema design, including indexes and materialized views.
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?
The database performs a trial execution to confirm performance improvement before evolving the plan baseline.
Why this answer
The database performs a verification task where it executes the new plan and compares it against the baseline performance to decide if it should be accepted.
Which TWO methods can be used to tune SQL statements?
Manual intervention using hints.
Why this answer
Manual tuning with hints and automated tuning via advisors are common practices.
Which THREE of the following are valid components or features of the Automatic SQL Tuning process in Oracle 23ai?
SPM is integrated with tuning to ensure performance stability.
Why this answer
The Automatic SQL Tuning process involves the SQL Tuning Advisor, SQL Tuning Sets, and SQL Plan Baselines as part of its automated diagnostic and correction cycle.
You are tuning a query that is performing poorly due to high 'buffer busy waits'. What is the likely cause?
This contention happens in the buffer cache.
Why this answer
Buffer busy waits occur when multiple sessions try to modify the same block at the same time.
What is the primary benefit of using a SQL Profile?
Profiles provide auxiliary information to the optimizer.
Why this answer
SQL Profiles improve the optimizer's choices by providing better selectivity and cardinality estimates.
Which THREE types of information are captured in an AWR snapshot?
Important for parsing/memory health.
Why this answer
AWR captures workload, system statistics, and session activity.
What is the primary function of ADDM in Oracle Database 23ai?
This is the core purpose of ADDM.
Why this answer
ADDM analyzes AWR snapshots to identify performance bottlenecks and provide recommendations.
What is the result of setting STATISTICS_LEVEL to 'BASIC'?
BASIC level turns off most automated diagnostic tools.
Why this answer
Setting it to BASIC disables many diagnostic features, including AWR and ADDM.
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?
This is the correct procedure for gathering table statistics.
Why this answer
DBMS_STATS is the standard package for managing optimizer statistics.
You are analyzing a query with the SQL Tuning Advisor. The advisor recommends an 'SQL Profile'. What does the profile actually do?
Profiles guide the optimizer to better choices.
Why this answer
A SQL Profile provides additional optimizer hints and statistics corrections.
You are investigating high CPU utilization in the database. Which view provides the top SQL statements currently in memory based on CPU usage?
This view has CPU_TIME and EXECUTIONS columns to identify heavy SQL.
Why this answer
V$SQLAREA contains aggregated statistics for SQL statements in the library cache, including CPU_TIME.
Ready to test yourself?
Try a timed practice session using only Performance Tuning And Diagnostics questions.