Find execution plan from dba_hist_sql_plan for a specific SQL_ID and PLAN_HASH_VALUE fplan.sql

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;