Sql History

--
-- Show the Plan Hash Values for a Given SQLID Over a Given Period
--

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
    (
    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
    FROM dba_hist_snapshot ss
    WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
    ) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
  AND q.sql_id IN ( '&SQLID')
/


--
-- List SQL Run Dates for a Given Plan Hash Value
--

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

SELECT DISTINCT
     sql_plan_hash_value,
     to_char(sample_time,'DD-Mon-YYYY') as st
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE sql_plan_hash_value=&Enter_Plan_Hash_Value
ORDER BY st
/