Oracle DBA_SQL_PLAN_BASELINE SQL_ID and PLAN_HASH_VALUE

There are probably better ways, so please let me know (@FranckPachot). This is what I use when I want to get the SQL_ID and the PLAN_HASH_VALUE when looking at the SQL Plan Baselines.

The DBA_SQL_PLAN_BASELINES view does not provide them, probably because SQL Plan Management (SPM) is going from a statement and it’s execution plan to the SQL Plan Baselines, but doesn’t need to navigate in the other way. However, we need it when troubleshooting query performance.

SQL_ID

I think I got this from It Tony Hasler “Expert Oracle SQL: Optimization, Deployment, and Statistics” book. There’s an internal function available since 11gR2 which calculates the SQL_ID from an SQL_TEXT (as null-terminated C string):

dbms_sqltune_util0.sqltext_to_sqlid(sql_text||chr(0)) sql_id

PLAN_HASH_VALUE

I lazily use DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE for this one, as it displays a line with it:

( select to_number(regexp_replace(plan_table_output,'^[^0-9]*')) 
  from table(
   dbms_xplan.display_sql_plan_baseline(sql_handle,plan_name)
) where plan_table_output like 'Plan hash value: %') plan_hash_value

Example

Here is an example where I query DBA_SQL_PLAN_BASELINE with those additional columns:

select dbms_sqltune_util0.sqltext_to_sqlid(sql_text||chr(0)) sql_id,
( select to_number(regexp_replace(plan_table_output,'^[^0-9]*')) 
  from table(dbms_xplan.display_sql_plan_baseline(sql_handle,plan_name)) 
  where plan_table_output like 'Plan hash value: %') plan_hash_value
,plan_name,enabled,accepted,fixed,reproduced  
,dbms_xplan.format_time_s(elapsed_time/1e6) hours,creator,origin,created,last_modified,last_executed
,sql_text
from dba_sql_plan_baselines b 
where sql_text like '%&sql_text_pattern.%'
order by sql_id,hours desc
;

For the ELAPSED_TIME, I use the FORMAT_TIME_S for pretty formatting.
More info:

Oracle numbers in K/M/G/T/P/E

So here is the output of the previous query. I have seen some SQL_ID executed with different PLAN_HASH_VALUE and I know which one was good or not. This helps me to know exactly which ones I want to accept or disable: