Find hash value & time when this plan was taken for particular sql id.
Filename : fphv.sql
REM *** Find Plan Hash value
column plan_hash_value format 999999999999
accept v_sqlid prompt ‘Enter SQL_ID : ‘
select distinct sql_id, plan_hash_value, to_char(timestamp, ‘DD-MON-RRRR:HH24:MI:SS’) from dba_hist_sql_plan
where sql_id=’&v_sqlid’;
Then, Apply sql id and plan_hash_value for a unique plan.
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;
SQl_ID and SQL statement you can get from currwaitobj.sql
set lines 120 pages 200
column event format a30
column object_type format a14
column object_name format a30
column owner format a10
column sql_text format a120
spool currwaitobj
accept event_name prompt ‘Enter fraction of event to trace : ‘
accept start_time1 prompt ‘Start Time in (DD-MON-RRRR:HH24:MI format) : ‘
accept stop_time1 prompt ‘Stop Time in (DD-MON-RRRR:HH24:MI format) : ‘
select distinct a.event
from v$active_session_history a
where sample_time between to_date(‘&start_time1’, ‘DD-MON-RRRR:HH24:MI’) and to_date(‘&stop_time1’, ‘DD-MON-RRRR:HH24:MI’);
select distinct a.event, s.sql_id, s.sql_text
from v$active_session_history a, v$sqlarea s
where s.sql_id = a.sql_id
and lower(a.event) like lower(‘%&event_name%’)
and sample_time between to_date(‘&start_time1’, ‘DD-MON-RRRR:HH24:MI’) and to_date(‘&stop_time1’, ‘DD-MON-RRRR:HH24:MI’)
order by s.sql_id;
spool off