In Oracle, the optimizer is responsible for determining the most efficient
execution plan for SQL queries. However, there are situations where the optimizer
may pick a bad execution plan due to various factors like incorrect statistics,
outdated histograms, or complex queries that lead to suboptimal execution. To
address this, you need to identify the bad plan and take corrective measures.
Step-by-Step Process to Identify and Correct a Bad Plan
1. Identify the Bad Execution Plan
You can identify a bad execution plan through various methods, including querying
views, analyzing execution plans, and using tools like AWR, ASH, or SQL Monitor.
a) Using V$SQL to Identify Bad Plans
You can query the V$SQL or V$SQL_PLAN view to find out which SQL queries have
suboptimal plans.
SELECT sql_id, plan_hash_value, sql_text
FROM v$sql
WHERE sql_text LIKE '%<part_of_query>%'
ORDER BY elapsed_time DESC;
This query gives you SQL IDs and execution times, which can help you identify slow-
running or inefficient queries. The plan_hash_value will tell you which execution
plan was used.
b) Using DBMS_XPLAN to View the Execution Plan
Once you’ve identified a SQL query, use DBMS_XPLAN to display its execution plan.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>', <child_number>));
This will show you the actual execution plan used by Oracle, including information
on access methods (e.g., table scans, index scans), joins, and sorts. Look for
inefficient operations like full table scans on large tables, Cartesian joins, or
nested loops on large datasets.
c) Using AWR/ASH Reports for More Details
For more detailed analysis of bad execution plans over time, you can use AWR
(Automatic Workload Repository) or ASH (Active Session History) reports:
• AWR Report: AWR reports give you performance statistics, including SQL
performance, top SQL by CPU, disk reads, and more.
To generate an AWR report, run:
@?/sqlplus/admin/awrrpt.sql
• ASH Report: ASH reports give real-time active session statistics,
including top SQL that is causing wait events.
@?/sqlplus/admin/ashrpt.sql
Both reports will help you identify SQL queries with bad execution plans.
2. Reasons for a Bad Plan
Several factors can cause the optimizer to pick a bad execution plan:
• Outdated or missing statistics: If statistics are outdated or not
collected, the optimizer may make incorrect assumptions about data distribution,
leading to suboptimal plans.
• Missing or incorrect indexes: The optimizer might choose a full table
scan when an index scan would be more efficient.
• Incorrect SQL hints: SQL hints can override the optimizer’s decisions,
and if incorrectly used, they may force a bad plan.
• Parameter settings: The optimizer behavior can be affected by session
or system-level parameters (e.g., OPTIMIZER_MODE, OPTIMIZER_INDEX_CACHING).
• Histograms: Missing or stale histograms can cause the optimizer to
misestimate the selectivity of predicates, leading to inefficient plans.
3. Steps to Correct the Bad Plan
Once you have identified a bad plan, here are some steps you can take to correct
it:
a) Collect or Refresh Statistics
Outdated or missing statistics are one of the most common causes of bad plans. Use
the DBMS_STATS package to collect fresh statistics on tables, indexes, and columns:
-- Collect statistics on the entire schema:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME');
-- Collect statistics for a specific table:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');
To ensure the optimizer makes decisions based on the most accurate data, especially
for large tables or complex queries, consider using histograms to capture skewed
data distributions:
-- Create a histogram for a specific column:
EXEC DBMS_STATS.GATHER_COLUMN_STATS('SCHEMA_NAME', 'TABLE_NAME', 'COLUMN_NAME',
method_opt => 'FOR ALL COLUMNS SIZE 254');
b) Force the Optimizer to Use a Specific Plan (SQL Plan Baseline)
You can capture and force the optimizer to use a specific plan using SQL Plan
Management (SPM). This can be helpful if you have a plan that you know is optimal
and want to avoid the optimizer changing it in the future.
1. Capture the plan using the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
procedure:
BEGIN
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '<SQL_ID>',
plan_hash_value => <PLAN_HASH_VALUE>
);
END;
2. Verify the plan baseline:
SELECT * FROM dba_sql_plan_baselines WHERE sql_id = '<SQL_ID>';
3. Accept the captured plan if it’s not automatically chosen:
BEGIN
DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => '<SQL_HANDLE>',
plan_name => '<PLAN_NAME>',
attribute_name => 'ACCEPTED',
attribute_value => 'YES'
);
END;
This ensures that the optimizer uses the captured plan in the future.
c) Use Hints (as a Temporary Measure)
If the optimizer keeps picking a suboptimal plan and you can’t easily fix it
through statistics or baselines, you can use optimizer hints to force the optimizer
to use a particular execution plan. For example:
SELECT /*+ INDEX(t idx_name) */ * FROM table_name t WHERE t.column = 10;
This will force the optimizer to use the idx_name index. However, be cautious when
using hints, as they override the optimizer’s decisions and can lead to issues if
the data distribution or query patterns change.
d) Review and Tune the Query
Sometimes the query itself is written in a way that leads to suboptimal execution
plans. You can improve the query by:
• Reducing the number of joins.
• Changing the order of joins (if possible).
• Using more selective predicates.
• Using EXISTS instead of IN for subqueries (in some cases).
• Using appropriate JOIN types (e.g., hash joins, nested loops, etc.).
For example, if a query is performing a full table scan on a large table, check if
an index is missing on the filter condition.
e) Optimize System Parameters
Check the following system parameters that influence the optimizer:
• OPTIMIZER_MODE: The mode in which the optimizer operates (ALL_ROWS,
FIRST_ROWS, CHOOSE).
• OPTIMIZER_INDEX_COST_ADJ: Influences the cost of using an index vs a
full table scan.
• OPTIMIZER_INDEX_CACHING: Controls how much the optimizer favors using
an index.
• SORT_AREA_SIZE, PGA_AGGREGATE_TARGET: Affect sorting and hashing
operations.
You can adjust these parameters based on the nature of the queries to help the
optimizer make better decisions.
f) Use SQL Profiles
If there are still issues with the plan, you can use SQL Profiles to suggest better
execution plans for problematic queries. Oracle can create a SQL Profile
automatically when you run the DBMS_SQLTUNE package, which might help the
optimizer.
1. Create a SQL Profile:
EXEC DBMS_SQLTUNE.CREATE_SQL_PROFILE(
name => 'my_sql_profile',
sql_id => '<SQL_ID>',
profile => '<PROFILE_TEXT>'
);
2. Check the profile:
SELECT * FROM dba_sql_profiles WHERE name = 'my_sql_profile';
4. Monitor After Fixing the Plan
After making changes, monitor the performance to ensure the new plan is being used
and there are improvements. You can use tools like AWR, ASH, and SQL Monitor to
verify that the performance has improved and that the optimizer is using the
desired execution plan.
Summary
To check if the optimizer has picked a bad plan and correct it:
1. Identify the bad plan using V$SQL, DBMS_XPLAN, or AWR/ASH reports.
2. Check for causes: outdated statistics, missing indexes, parameter
settings, or SQL design issues.
3. Correct the plan by:
• Updating statistics (DBMS_STATS).
• Capturing a SQL Plan Baseline to force a known good plan.
• Using optimizer hints (as a temporary solution).
• Optimizing the query or system parameters.
4. Monitor performance after making corrections to ensure the plan is
improved.
By following these steps, you can identify and fix cases where the Oracle optimizer
chooses inefficient execution plans.
