Oracle DBA_SQL_PLAN_BASELINE SQL_ID and PLAN_HASH_VALUE

Franck Pachot
Jul 20 · 2 min read

SQL_ID

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

PLAN_HASH_VALUE

( 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

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
;

Franck Pachot

Written by

https://twitter.com/FranckPachot DBA at CERN, Oracle OCM 12c, Oracle ACE Director, Oak Table member. My 499 posts at dbi-services: http://blog.dbi.pachot.net

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade