Oracle execution plans can be checked from the cursor cache using views like V$SQL, and detailed plans can be displayed using DBMS_XPLAN.DISPLAY_CURSOR .
Oracle documents that DBMS_XPLAN can display plans from cached cursors, AWR, SQL tuning sets, and SQL plan baseline
1. Check SQL_IDs Having Multiple PLAN_HASH_VALUEs in Current Memory
Use this query on V$SQL.
SELECT sql_id,
COUNT(DISTINCT plan_hash_value) AS plan_count,
LISTAGG(DISTINCT plan_hash_value, ', ')
WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,
SUM(executions) AS total_executions
FROM v$sql
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
ORDER BY plan_count DESC, total_executions DESC;
Meaning
SQL_ID: SQL statement identifierPLAN_COUNT: number of different plans used by the SQLPLAN_HASH_VALUES: list of plan hash valuesTOTAL_EXECUTIONS: total executions from cursor cache
If one SQL_ID has more than one PLAN_HASH_VALUE, it means Oracle has generated multiple execution plans for the same SQL.
2. Detailed Child Cursor Level Query
This gives more detail for each SQL_ID, child cursor, and plan hash value.
SELECT sql_id,
child_number,
plan_hash_value,
executions,
parsing_schema_name,
optimizer_mode,
loaded_versions,
invalidations,
first_load_time,
last_active_time
FROM v$sql
WHERE sql_id IN (
SELECT sql_id
FROM v$sql
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
)
ORDER BY sql_id, plan_hash_value, child_number;
This is useful when you want to see which child cursor used which plan.
3. Group PLAN_HASH_VALUE Against Each SQL_ID
If you want a grouped summary of all SQL_IDs and their plan hash values:
SELECT sql_id,
plan_hash_value,
COUNT(*) AS child_cursor_count,
SUM(executions) AS executions,
MIN(first_load_time) AS first_load_time,
MAX(last_active_time) AS last_active_time
FROM v$sql
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id, plan_hash_value
ORDER BY sql_id, executions DESC;
This output shows one row per:
SQL_ID + PLAN_HASH_VALUE
Example output:
SQL_ID PLAN_HASH_VALUE CHILD_CURSOR_COUNT EXECUTIONS
------------- --------------- ------------------ ----------
abc123xyz789 1234567890 2 500
abc123xyz789 9876543210 1 20
def456pqr111 5555555555 1 100
4. Check Only One Specific SQL_ID
If you want to check plan hash values for one SQL ID:
SELECT sql_id,
plan_hash_value,
COUNT(*) AS child_cursor_count,
SUM(executions) AS total_executions,
MIN(child_number) AS min_child_number,
MAX(child_number) AS max_child_number
FROM v$sql
WHERE sql_id = '&sql_id'
GROUP BY sql_id, plan_hash_value
ORDER BY total_executions DESC;
Example:
SELECT sql_id,
plan_hash_value,
COUNT() AS child_cursor_count,
SU(executions) AS total_executions,
* MIN(child_number) AS min_chi*d_number,
MAX(child_number)AS max_child_number
FROM v$sql
WERE sql_id = '5g8t1m9n2abc3'
GROUP BY sql_id, plan_hash_value
ORDER BY total_executions DESC;
5. Historical Check from AWR
If you want to check SQL_IDs with multiple plans historically, use DBA_HIST_SQLSTAT.
SELECT sql_id,
COUNT(DISTINCT plan_hash_value) AS plan_count,
LISTAGG(DISTINCT plan_hash_value, ', ')
WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,
SUM(executions_delta) AS total_executions
FROM dba_hist_sqlstat
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
ORDER BY plan_count DESC, total_executions DESC;
Use this when the SQL is not present in V$SQL, or you want to analyze old plan changes. Oracle documents that DBMS_XPLAN.DISPLAY_AWR can display execution plans stored in AWR. [docs.oracle.com]
6. Historical Grouping by SQL_ID and PLAN_HASH_VALUE
SELECT sql_id,
plan_hash_value,
SUM(executions_delta) AS executions,
ROUND(SUM(elapsed_time_delta) / 1000000, 2) AS elapsed_seconds,
ROUND(SUM(cpu_time_delta) / 1000000, 2) AS cpu_seconds,
SUM(buffer_gets_delta) AS buffer_gets,
SUM(disk_reads_delta) AS disk_reads,
MIN(snap_id) AS first_snap_id,
MAX(snap_id) AS last_snap_id
FROM dba_hist_sqlstat
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id, plan_hash_value
ORDER BY sql_id, executions DESC;
This is good for performance comparison between plans.
7. Best Query for Plan Instability Report
For DBA troubleshooting, this is usually the most useful query:
SELECT sql_id,
COUNT(DISTINCT plan_hash_value) AS plan_count,
SUM(executions_delta) AS total_executions,
ROUND(SUM(elapsed_time_delta) / 1000000, 2) AS total_elapsed_sec,
ROUND(SUM(cpu_time_delta) / 1000000, 2) AS total_cpu_sec,
SUM(buffer_gets_delta) AS total_buffer_gets,
SUM(disk_reads_delta) AS total_disk_reads
FROM dba_hist_sqlstat
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
ORDER BY total_elapsed_sec DESC;
This shows SQLs that had multiple plans and consumed the most elapsed time.
8. RAC-Aware Query
If your database is RAC, include INST_ID from GV$SQL.
SELECT inst_id,
sql_id,
plan_hash_value,
COUNT(*) AS child_cursor_count,
SUM(executions) AS executions,
MAX(last_active_time) AS last_active_time
FROM gv$sql
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY inst_id, sql_id, plan_hash_value
ORDER BY sql_id, inst_id, executions DESC;
To find SQL_IDs with multiple plans across RAC:
SELECT sql_id,
COUNT(DISTINCT plan_hash_value) AS plan_count,
LISTAGG(DISTINCT plan_hash_value, ', ')
WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,
SUM(executions) AS total_executions
FROM gv$sql
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
ORDER BY plan_count DESC, total_executions DESC;
9. Display Execution Plan for Each PLAN_HASH_VALUE
After finding multiple plan hash values, display the plan using:
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => '&sql_id',
cursor_child_no => NULL,
format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'
)
);
For historical AWR plan:
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_AWR(
sql_id => '&sql_id',
plan_hash_value => &plan_hash_value,
format => 'TYPICAL'
)
);
Recommended Quick Script
SET LINESIZE 220
SET PAGESIZE 100
COLUMN sql_id FORMAT A15
COLUMN plan_hash_values FORMAT A80
PROMPT === SQL_IDs with Multiple Plan Hash Values from V$SQL ===
SELECT sql_id,
COUNT(DISTINCT plan_hash_value) AS plan_count,
LISTAGG(DISTINCT plan_hash_value, ', ')
WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,
SUM(executions) AS total_executions
FROM v$sql
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
ORDER BY plan_count DESC, total_executions DESC;
Plan Hash Grouping Summary
Use this if your goal is simply:
“Group plan hash value against SQL ID.”
SELECT sql_id,
plan_hash_value,
COUNT(*) AS cursor_count,
SUM(executions) AS executions
FROM v$sql
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id, plan_hash_value
ORDER BY sql_id, executions DESC;
SQL_IDs having multiple plan hash values and executions more than 5
SELECT sql_id,
plan_hash_value,
COUNT(*) AS child_cursor_count,
SUM(executions) AS executions,
MIN(first_load_time) AS first_load_time,
MAX(last_active_time) AS last_active_time
FROM v$sql
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id, plan_hash_value
HAVING SUM(executions) > 5
ORDER BY sql_id, executions DESC;
SQL_IDs Having More Than One PLAN_HASH_VALUE
SELECT sql_id,
COUNT(DISTINCT plan_hash_value) AS plan_hash_count,
LISTAGG(DISTINCT plan_hash_value, ', ')
WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,
SUM(executions) AS total_executions
FROM v$sql
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
ORDER BY plan_hash_count DESC, total_executions DESC;
Yes, to find SQL_IDs where PLAN_HASH_VALUE is more than 1, meaning the same SQL_ID has used multiple execution plans, use COUNT(DISTINCT plan_hash_value) > 1.
SQL_IDs Having More Than One PLAN_HASH_VALUE
SELECT sql_id,
COUNT(DISTINCT plan_hash_value) AS plan_hash_count,
LISTAGG(DISTINCT plan_hash_value, ', ')
WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,
SUM(executions) AS total_executions
FROM v$sql
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
ORDER BY plan_hash_count DESC, total_executions DESC;
If You Also Want Executions Greater Than 5
SELECT sql_id,
COUNT(DISTINCT plan_hash_value) AS plan_hash_count,
LISTAGG(DISTINCT plan_hash_value, ', ')
WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,
SUM(executions) AS total_executions
FROM v$sql
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
AND SUM(executions) > 5
ORDER BY plan_hash_count DESC, total_executions DESC;
Detailed Output Per SQL_ID and PLAN_HASH_VALUE
Use this after identifying SQL_IDs with multiple plans:
SELECT sql_id,
plan_hash_value,
COUNT(*) AS child_cursor_count,
SUM(executions) AS executions,
MIN(first_load_time) AS first_load_time,
MAX(last_active_time) AS last_active_time
FROM v$sql
WHERE sql_id IN (
SELECT sql_id
FROM v$sql
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
)
AND plan_hash_value <> 0
GROUP BY sql_id, plan_hash_value
ORDER BY sql_id, executions DESC;
Best RAC Version Using GV$SQL
If it is a RAC database, use this:
SELECT sql_id,
COUNT(DISTINCT plan_hash_value) AS plan_hash_count,
LISTAGG(DISTINCT plan_hash_value, ', ')
WITHIN GROUP (ORDER BY plan_hash_value) AS plan_hash_values,
SUM(executions) AS total_executions
FROM gv$sql
WHERE sql_id IS NOT NULL
AND plan_hash_value <> 0
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
ORDER BY plan_hash_count DESC, total_executions DESC;
Key Condition
HAVING COUNT(DISTINCT plan_hash_value) > 1
This is the main condition to find SQL IDs having multiple plan hash values.
DBA Tip
If a SQL_ID has multiple PLAN_HASH_VALUEs, check:
- Statistics changes
- Bind peeking
- Adaptive cursor sharing
- Different optimizer environment
- Index creation or drop
- SQL profile
- SQL baseline
- Object invalidation
- RAC instance-specific plan difference
