REM fplan.sql
REM Find execution plan from dba_hist_sql_plan for a specific SQL_ID and PLAN_HASH_VALUE.
column plan_hash_value format 999999999999
accept v_sqlid prompt ‘Enter SQL_ID : ‘
accept v_plan_hash_value prompt ‘Enter PLAN Hash Value : ‘
SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position)
“Query Plan”
FROM dba_hist_sql_plan
START WITH id = 0 and sql_id=’&v_sqlid’ and plan_hash_value=&v_plan_hash_value
CONNECT BY PRIOR id = parent_id and sql_id=’&v_sqlid’ and plan_hash_value=&v_plan_hash_value;