How to check execution plan hash value (PLAN_HASH_VALUE) for a specific SQL_ID ?

Check  execution plan hash value (PLAN_HASH_VALUE) for a specific SQL_ID 

If you want to check the execution plan hash value (PLAN_HASH_VALUE) for a specific SQL_ID in Oracle, use one of these queries:

Current Cursor Cache (V$SQL)

SELECT sql_id,

plan_hash_value,

executions,

parsing_schema_name

FROM v$sql

WHERE sql_id = '&SQL_ID';

Historical Plans from AWR (DBA_HIST_SQLSTAT)

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

ORDER BY executions DESC;

``

Get Detailed Plan Information (DBMS_XPLAN)

For the current plan in memory:

SELECT *

FROM TABLE(

DBMS_XPLAN.DISPLAY_CURSOR(

sql_id => '&SQL_ID',

format => 'ALLSTATS LAST'

)

);

Check All Plans Associated with a SQL_ID

SELECT DISTINCT

sql_id,

plan_hash_value

FROM v$sql

WHERE sql_id = '&SQL_ID'

ORDER BY plan_hash_value;

Check SQL Plan Baselines (if used)

SELECT sql_handle,

plan_name,

enabled,

accepted

FROM dba_sql_plan_baselines

WHERE signature IN (

SELECT exact_matching_signature

FROM v$sql

WHERE sql_id = '&SQL_ID'

);

Example

For SQL_ID 5g8t1m9n2abc3:

SELECT sql_id, plan_hash_value

FROM v$sql

WHERE sql_id = '5g8t1m9n2abc3';

This will return the plan hash value(s) Oracle is currently using for that SQL statement. If multiple rows are returned, the SQL has been executed with different plans (e.g., due to Adaptive Plans, bind peeking, or plan evolution).