Performance Tuning And Diagnostics practice questions

SQL Plan Baselines are full

Why it fails: This would cause errors, not stop evolution.

Statistics are gathered manually

Why it fails: Manual statistics collection does not disable SPM.

The database is in NOARCHIVELOG mode

Why it fails: SPM does not depend on redo logging mode.

DBMS_SQLTUNE

Why it fails: Used for tuning, not for gathering statistics.

DBMS_UTILITY

Why it fails: This provides utility functions, not stats collection.

DBMS_ADVISOR

Why it fails: Used for running advisors like SQL Access Advisor.

Automatic Database Diagnostic Monitor (ADDM)

Why it fails: ADDM analyzes data, it doesn't store the incident files.

AWR

Why it fails: AWR stores performance metrics in the database, not incident files.

SQL Plan Management

Why it fails: This manages execution plans.

DBMS_SCHEDULER

Why it fails: Used for job scheduling.

DBMS_SQLTUNE

Why it fails: Used for SQL tuning sets.

DBMS_STATS

Why it fails: Used for optimizer statistics.

V$PARAMETER

Why it fails: This shows instance parameter settings.

V$SQL

Why it fails: This provides SQL statistics, not wait event details.

V$LOCK

Why it fails: This shows lock information, but V$SESSION_WAIT is more direct for wait events.

V$BH

Why it fails: Provides block buffer cache status.

V$SQL

Why it fails: Provides SQL text and stats.

V$FILESTAT

Why it fails: Provides aggregate I/O stats per file.

V$SQL_PLAN

Why it fails: Shows the plan, not session wait details.

It is ignored by the optimizer

Why it fails: The system keeps it for potential future evolution.

It is automatically accepted

Why it fails: Security and stability dictate it is not automatically accepted.

It replaces the existing baseline

Why it fails: Existing baselines are protected.

Manually set SHARED_POOL_SIZE

Why it fails: SHARED_POOL_SIZE governs the shared pool, not the library cache specifically; the library cache sits within it and is sized through the separate parameter ADDM actually recommends adjusting. Manually setting SHARED_POOL_SIZE suits overall shared pool tuning, not this targeted change.

Drop and recreate the library cache

Why it fails: Dropping and recreating the library cache discards parsed SQL and forces full hard parsing, causing a temporary performance drop rather than raising the cache capacity. Recreating caches suits corruption or invalidation issues, not an ADDM sizing recommendation.

Rebuild the database

Why it fails: Rebuilding the database is a drastic recovery action for corruption or failed upgrades, and it neither changes the library cache allocation nor preserves the existing instance. ADDM's sizing advice requires adjusting the relevant memory parameter instead.

Change the STATISTICS_LEVEL

Why it fails: This affects data collection, not plan selection.

Use SQL Plan Management

Why it fails: This manages existing plans but does not force new ones easily.

Create an index

Why it fails: Indexes don't force join methods.

SQL Access Advisor

Why it fails: This is for indexes/materialized views.

SQL Tuning Advisor

Why it fails: This is for SQL statements.

ADDM

Why it fails: ADDM is for general analysis.

Instance parameter history

Why it fails: Parameter history is in AWR.

Database backup schedules

Why it fails: Backup info is in RMAN, not Tuning Advisor.

Run the AWR report and look for 'Stats' in the top wait events.

Why it fails: AWR reports analyze wait events, but do not directly invoke the Statistics Advisor.

Enable the SQL Tuning Advisor for the specific SQL_ID.

Why it fails: SQL Tuning Advisor focuses on query plans, not the underlying health of statistics.

Execute DBMS_STATS.GATHER_SCHEMA_STATS with the 'AUTO' option.

Why it fails: This command gathers statistics but does not analyze their quality or provide advisor recommendations.

Automatic Maintenance Tasks

Why it fails: While related, this is a broader management category, not a specific component of the SQL tuning engine itself.

Automatic Database Diagnostic Monitor (ADDM)

Why it fails: ADDM is a separate diagnostic tool that analyzes system-wide issues rather than specific SQL tuning.

Query V$SQLAREA and examine the PLAN_HASH_VALUE column.

Why it fails: V$SQLAREA provides aggregated statistics for SQL statements but does not contain the detailed execution plan or per-operation runtime statistics. The PLAN_HASH_VALUE is just a numeric hash of the plan and does not allow you to see the plan itself or its runtime behavior, so it cannot help diagnose the performance issue.

Use the EXPLAIN PLAN FOR command followed by a query on PLAN_TABLE.

Why it fails: EXPLAIN PLAN generates the optimizer's estimated plan without actually executing the statement. It does not provide actual runtime statistics, and the plan shown may differ from the one used at runtime. This is insufficient for diagnosing a performance issue that has already occurred.

Use the DBMS_XPLAN.DISPLAY_AWR function with the SQL_ID.

Why it fails: DBMS_XPLAN.DISPLAY_AWR displays execution plans stored in the Automatic Workload Repository (AWR). While useful for historical analysis, it shows the plan captured at snapshot time and does not include real-time runtime statistics for the most recent execution. It is not the best tool for immediate diagnosis of a current performance problem.

Adaptive Cursor Sharing (ACS)

Why it fails: ACS addresses skewed data distributions for bind variables by allowing multiple execution plans, but it does not directly enable partition pruning at parse time for an unknown bind value. ACS can help with cardinality misestimates, yet partition pruning with a bind variable requires a different mechanism that evaluates the predicate during optimization rather than at runtime through multiple child cursors.

Partition-wise join

Why it fails: Partition-wise join is a technique that improves join performance between two partitioned tables by joining matching partitions, but it does not resolve pruning for a single-table query with a bind variable on the partition key. This feature is irrelevant to the scenario because no join is involved, and it would not allow the optimizer to eliminate partitions based on an unknown bind value.

Bloom filtering

Why it fails: Bloom filters are used to reduce the amount of data transferred between parallel execution processes or to filter rows during joins, not to enable partition pruning for a bind variable. While bloom filters can improve performance in some cases, they do not provide the optimizer with the information needed to prune partitions when the partition key value is supplied via a bind variable.

V$SYSTEM_EVENT

Why it fails: V$SYSTEM_EVENT summarizes total waits for the entire instance. It shows the total time waited for 'buffer busy waits' but does not provide any information about which segment or block is involved. It is useful for identifying the top wait events but not for drilling down to the object level.

V$SESSION_WAIT

Why it fails: V$SESSION_WAIT shows current or last wait for each session. While it includes P1 and P2 values, it only captures the wait at the moment of query. For intermittent contention, this view is unlikely to catch the specific wait. It is not a historical repository, so it is unreliable for diagnosing past spikes.

V$SEGMENT_STATISTICS

Why it fails: V$SEGMENT_STATISTICS provides statistics like logical reads and buffer busy waits per segment, but it does not give block-level details or the specific block type. It can tell you which segment has high buffer busy waits, but not the exact block or why. It lacks the granularity needed to identify the block type causing contention.

Create a SQL plan baseline for the query.

Why it fails: A SQL plan baseline can force the optimizer to use a specific plan, but it does not provide statistics for the function-based index. Baselines are used to lock in known-good plans, not to inform the optimizer about data distribution. If the index is not being considered due to missing statistics, a baseline would not help unless it was created from a plan that already used the index, which is not the case here.

Enable the optimizer feature 'function-based index cost'.

Why it fails: There is no such optimizer feature named 'function-based index cost'. The optimizer already considers function-based indexes if statistics are available. This option is a distractor; enabling a non-existent feature would not resolve the issue. The correct approach is to gather statistics for the index so the optimizer can accurately estimate the cost of using it.

Run DBMS_STATS.GATHER_TABLE_STATS with the CASCADE option.

Why it fails: Gathering table statistics with CASCADE also gathers index statistics, but it is not the most direct method for a function-based index. While it would work, the question asks for the action to provide statistics specifically for the function-based index. Using GATHER_INDEX_STATS is more targeted and avoids unnecessary work on the table. CASCADE is a valid approach but less precise in this context.

Use SQL Plan Management (SPM) to create a SQL plan baseline and force the optimizer to use the fastest plan.

Why it fails: SPM can stabilize plans, but it does not diagnose why the optimizer chooses different plans. The scenario requires understanding the root cause of the variability, likely bind peeking or adaptive cursor sharing. Forcing a plan without diagnosis could mask the issue or lead to a plan that is suboptimal for certain bind values. SPM is a solution, not a diagnostic step.

Query DBA_HIST_SQLSTAT and DBA_HIST_SQL_PLAN for the SQL ID to compare historical execution statistics and plans across different time periods.

Why it fails: This is useful for seeing how the plan changed over time, but it does not capture the per-execution bind values or the optimizer's internal decision-making process. The variability is tied to bind values, so historical AWR data alone will not reveal which bind values produced which plan or why. It is a retrospective tool, not one that explains the optimizer's plan choice for a specific execution.

Generate an active SQL Monitor report for a problematic execution and examine the 'Plan' and 'Statistics' tabs for cardinality misestimates and bind variable details.

Why it fails: SQL Monitor is excellent for real-time monitoring of long-running statements, but it requires the execution to be currently running or recently completed and captured. It may not capture multiple executions with different bind values. While it can show cardinality misestimates, it is not the most efficient way to compare across many executions. It is also not designed for per-bind-value analysis.

Query V$PGASTAT to check PGA memory usage.

Why it fails: V$PGASTAT provides statistics on PGA memory, such as aggregate PGA target and cache hit percentage. It does not report on shared pool memory or library cache performance. Shared pool contention is an SGA issue, so this view is not applicable. Using it would not diagnose the problem.

Query V$BH to check buffer cache hit ratios.

Why it fails: V$BH displays information about buffers in the buffer cache, not the shared pool. Buffer cache hit ratios relate to data block reads, not SQL parsing or library cache efficiency. This view is irrelevant for diagnosing shared pool contention. Monitoring it would not help confirm the suspected issue.

Query V$SYSSTAT for 'parse count (total)' and 'parse count (hard)'.

Why it fails: While V$SYSSTAT provides parse counts, it does not directly indicate shared pool contention. High hard parse counts could be due to literal SQL or other factors, not necessarily pool size. This view is useful for overall parsing trends but does not specifically diagnose shared pool pressure. It is not the best choice for confirming contention.

Check the OPTIMIZER_DYNAMIC_SAMPLING parameter to see if dynamic sampling is enabled, and if not, enable it at the session level.

Why it fails: Dynamic sampling can improve cardinality estimates by sampling data during parsing, but it does not confirm whether existing estimates are wrong. It is a preventive or corrective measure, not a diagnostic one. Enabling it might change the plan, but it does not provide a comparison of estimated versus actual rows. This action could be part of a solution, but it does not confirm the suspicion.

Query V$SQL_PLAN_STATISTICS_ALL for the SQL ID to view the estimated and actual rows for each step of the execution plan.

Why it fails: V$SQL_PLAN_STATISTICS_ALL does contain estimated and actual rows, but it only has actual rows if the query was executed with the GATHER_PLAN_STATISTICS hint or if STATISTICS_LEVEL is set to ALL. Simply querying the view without first enabling statistics gathering will not provide actual rows. The scenario does not state that statistics were gathered, so this view alone may not show actuals. The hint method is more reliable.

Use the SQL Tuning Advisor to analyze the query and generate a report that includes cardinality feedback.

Why it fails: SQL Tuning Advisor can provide recommendations and may mention cardinality misestimates, but it is a more general tool that runs multiple analyses. It does not directly show the estimated versus actual rows for each operation in the plan. It is also an asynchronous process that may take time. The scenario requires a quick confirmation of cardinality estimates, which is more directly achieved by examining the plan with actual statistics.