Check PLAN_HASH_VALUE Against SQL_ID in Oracle ?
1. Objective
To verify which PLAN_HASH_VALUE is associated with a specific SQL_ID in Oracle.
This is useful when:
- Checking if SQL execution plan changed
- Troubleshooting SQL performance issues
- Comparing current and historical plans
- Validating SQL Plan Baseline usage
- Investigating plan regression
2. Required Input
You need the SQL ID.
Example:
SQL_ID = '5g8t1m9n2abc3'
If you do not have the SQL ID, you can search it from V$SQL using part of the SQL text.
SELECT sql_id,
child_number,
plan_hash_value,
sql_text
FROM v$sql
WHERE sql_text LIKE '%your_unique_sql_text%'
AND sql_text NOT LIKE '%v$sql%';
3. Check Current PLAN_HASH_VALUE from Cursor Cache
Use this query when the SQL is currently present in the shared pool.
SET LINESIZE 200
SET PAGESIZE 100
SELECT sql_id,
child_number,
plan_hash_value,
executions,
parsing_schema_name,
last_active_time
FROM v$sql
WHERE sql_id = '&sql_id'
ORDER BY child_number;
Example
SELECT sql_id,
child_number,
plan_hash_value,
executions,
parsing_schema_name,
last_active_time
FROM v$sql
WHERE sql_id = '5g8t1m9n2abc3'
ORDER BY child_number;
How to Read the Output
If output shows:
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS
------------- ------------ --------------- ----------
5g8t1m9n2abc3 0 1234567890 150
5g8t1m9n2abc3 1 9876543210 25
It means the same SQL ID has multiple child cursors and multiple execution plans.
4. Check Distinct PLAN_HASH_VALUE for a SQL_ID
Use this if you only want the list of unique plan hash values.
SELECT DISTINCT
sql_id,
plan_hash_value
FROM v$sql
WHERE sql_id = '&sql_id'
ORDER BY plan_hash_value;
Example
SELECT DISTINCT
sql_id,
plan_hash_value
FROM v$sql
WHERE sql_id = '5g8t1m9n2abc3'
ORDER BY plan_hash_value;
5. Check PLAN_HASH_VALUE with Execution Statistics
This query helps identify which plan is used most frequently.
SELECT sql_id,
plan_hash_value,
COUNT(*) child_count,
SUM(executions) total_executions,
MIN(first_load_time) first_load_time,
MAX(last_active_time) last_active_time
FROM v$sql
WHERE sql_id = '&sql_id'
GROUP BY sql_id, plan_hash_value
ORDER BY total_executions DESC;
Interpretation
CHILD_COUNT shows how many child cursors used the same plan.TOTAL_EXECUTIONS shows how often that plan was executed.- Multiple
PLAN_HASH_VALUE entries can indicate plan changes or different optimizer choices.
6. Display Current Execution Plan Using DBMS_XPLAN
Use DBMS_XPLAN.DISPLAY_CURSOR to see the actual cached execution plan. Oracle documentation states that DISPLAY_CURSOR displays the execution plan of a loaded cursor.
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => '&sql_id',
cursor_child_no => NULL,
format => 'ALLSTATS LAST'
)
);
Example
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => '5g8t1m9n2abc3',
cursor_child_no => NULL,
format => 'ALLSTATS LAST'
)
);
Notes
cursor_child_no => NULL displays all child cursors for that SQL ID.ALLSTATS LAST shows actual runtime statistics for the last execution if statistics are available.- If no rows are returned, the SQL may not be available in the cursor cache.
7. Display Plan for a Specific Child Cursor
If the SQL ID has multiple child cursors, check a specific one.
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => '&sql_id',
cursor_child_no => &child_number,
format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'
)
);
Example
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => '5g8t1m9n2abc3',
cursor_child_no => 0,
format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'
)
);
This is useful when bind values, optimizer outlines, or child cursor differences need to be reviewed.
8. Check Historical PLAN_HASH_VALUE from AWR
If the SQL is no longer in memory, check AWR history.
SELECT sql_id,
plan_hash_value,
SUM(executions_delta) executions,
MIN(snap_id) first_snap_id,
MAX(snap_id) last_snap_id
FROM dba_hist_sqlstat
WHERE sql_id = '&sql_id'
GROUP BY sql_id, plan_hash_value
ORDER BY first_snap_id;
Example
SELECT sql_id,
plan_hash_value,
SUM(executions_delta) executions,
MIN(snap_id) first_snap_id,
MAX(snap_id) last_snap_id
FROM dba_hist_sqlstat
WHERE sql_id = '5g8t1m9n2abc3'
GROUP BY sql_id, plan_hash_value
ORDER BY first_snap_id;
Oracle documentation confirms that DBMS_XPLAN can display execution plans stored in AWR using DISPLAY_AWR
9. Display Historical Execution Plan from AWR
Use this when you know the historical PLAN_HASH_VALUE.
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_AWR(
sql_id => '&sql_id',
plan_hash_value => &plan_hash_value,
format => 'TYPICAL'
)
);
Example
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_AWR(
sql_id => '5g8t1m9n2abc3',
plan_hash_value => 1234567890,
format => 'TYPICAL'
)
);
10. Check Plan Changes Over Time
This query shows when each plan was used across AWR snapshots.
SELECT s.begin_interval_time,
s.end_interval_time,
st.sql_id,
st.plan_hash_value,
st.executions_delta,
ROUND(st.elapsed_time_delta / 1000000, 2) elapsed_sec,
ROUND(st.cpu_time_delta / 1000000, 2) cpu_sec,
st.buffer_gets_delta,
st.disk_reads_delta
FROM dba_hist_sqlstat st
JOIN dba_hist_snapshot s
ON st.snap_id = s.snap_id
AND st.dbid = s.dbid
AND st.instance_number = s.instance_number
WHERE st.sql_id = '&sql_id'
ORDER BY s.begin_interval_time, st.plan_hash_value;
This is helpful for identifying whether a SQL performance issue started after a plan change.
11. Check SQL Plan Baseline for the SQL_ID
If SQL Plan Management is used, check whether the SQL has a baseline.
SELECT sql_handle,
plan_name,
enabled,
accepted,
fixed,
optimizer_cost,
created,
last_executed
FROM dba_sql_plan_baselines
WHERE signature IN (
SELECT exact_matching_signature
FROM v$sql
WHERE sql_id = '&sql_id'
);
Oracle documentation states that DBMS_XPLAN can also display execution plans from SQL plan baselines.
12. Display SQL Plan Baseline
If you get a SQL_HANDLE from the previous query, use:
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
sql_handle => '&sql_handle',
format => 'TYPICAL'
)
);
13. Complete Quick-Check Script
Use this as a ready-to-run script.
SET LINESIZE 220
SET PAGESIZE 100
COLUMN sql_id FORMAT A15
COLUMN parsing_schema_name FORMAT A20
COLUMN first_load_time FORMAT A25
COLUMN last_active_time FORMAT A30
ACCEPT sql_id_input CHAR PROMPT 'Enter SQL_ID: '
PROMPT
PROMPT === Current Cursor Cache Plan Hash Values ===
SELECT sql_id,
child_number,
plan_hash_value,
executions,
parsing_schema_name,
first_load_time,
last_active_time
FROM v$sql
WHERE sql_id = '&sql_id_input'
ORDER BY child_number;
PROMPT
PROMPT === Distinct Current Plan Hash Values ===
SELECT DISTINCT
sql_id,
plan_hash_value
FROM v$sql
WHERE sql_id = '&sql_id_input'
ORDER BY plan_hash_value;
PROMPT
PROMPT === Historical AWR Plan Hash Values ===
SELECT sql_id,
plan_hash_value,
SUM(executions_delta) executions,
MIN(snap_id) first_snap_id,
MAX(snap_id) last_snap_id
FROM dba_hist_sqlstat
WHERE sql_id = '&sql_id_input'
GROUP BY sql_id, plan_hash_value
ORDER BY first_snap_id;
PROMPT
PROMPT === Current Execution Plan from Cursor Cache ===
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => '&sql_id_input',
cursor_child_no => NULL,
format => 'ALLSTATS LAST'
)
);
14. Common Issues and Checks
Issue 1: No rows from V$SQL
Possible reasons:
- SQL aged out of shared pool
- SQL not executed recently
- Wrong SQL ID
- Query executed in another PDB or RAC instance
Use AWR:
SELECT sql_id,
plan_hash_value,
SUM(executions_delta) executions
FROM dba_hist_sqlstat
WHERE sql_id = '&sql_id'
GROUP BY sql_id, plan_hash_value;
Issue 2: Multiple PLAN_HASH_VALUE values
Possible reasons:
- Bind peeking
- Adaptive cursor sharing
- Statistics changed
- Different optimizer environment
- SQL profile or baseline added
- Object/index changes
Issue 3: DBMS_XPLAN.DISPLAY_CURSOR gives insufficient data
Try enhanced format:
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => '&sql_id',
cursor_child_no => NULL,
format => 'ADVANCED ALLSTATS LAST +PEEKED_BINDS +OUTLINE'
)
);
Final Recommended Flow
Step 1: Confirm SQL_ID
Step 2: Check V$SQL for current PLAN_HASH_VALUE
Step 3: Check distinct PLAN_HASH_VALUE values
Step 4: Display current execution plan using DBMS_XPLAN.DISPLAY_CURSOR
Step 5: Check DBA_HIST_SQLSTAT for historical plans
Step 6: Display old plan using DBMS_XPLAN.DISPLAY_AWR
Step 7: Check SQL Plan Baseline if plan stability is required
Best Quick Query
If you need only one query to check the plan hash value for a SQL ID:
SELECT sql_id,
child_number,
plan_hash_value,
executions,
last_active_time
FROM v$sql
WHERE sql_id = '&sql_id'
ORDER BY child_number;
Troubleshooting Privileges
You may need access to views like V$SQL, V$SQL_PLAN, V$SESSION, and V$SQL_PLAN_STATISTICS_ALL for DISPLAY_CURSOR; Oracle documents these privilege requirements for DBMS_XPLAN.DISPLAY_CURSOR.
No comments:
Post a Comment