Querying the SQL Running During an ASH Sample

-- 1) Survey the sampling activity near the interesting event 
SELECT DISTINCT SAMPLE_ID, SAMPLE_TIME 
  FROM DBA_HIST_ACTIVE_SESS_HISTORY 
WHERE INSTANCE_NUMBER=:INSTNUM
AND SAMPLE_TIME > TO_DATE('2014-02-07 08:00','YYYY-MM-DD HH24:MI')
AND SAMPLE_TIME < TO_DATE('2014-02-07 09:00','YYYY-MM-DD HH24:MI')
ORDER BY 2;

-- 2) Get SQL running during the sample time
SELECT DHS.SQL_TEXT, 
       ASH.* 
  FROM DBA_HIST_ACTIVE_SESS_HISTORY ASH LEFT OUTER JOIN DBA_HIST_SQLTEXT DHS 
       ON ASH.SQL_ID=DHS.SQL_ID 
WHERE ASH.SAMPLE_ID=:SAMPLEID AND ASH.INSTANCE_NUMBER=:INSTNUM;

-- 1) Find Sample_IDs for desired range
WITH SAMPLEIDS AS (
SELECT DISTINCT SAMPLE_ID, SAMPLE_TIME, INSTANCE_NUMBER
  FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE SAMPLE_TIME > TO_DATE('2023-09-27 23:50','YYYY-MM-DD HH24:MI')
AND SAMPLE_TIME < TO_DATE('2023-09-28 00:10','YYYY-MM-DD HH24:MI')
ORDER BY 2)
-- 2) Get SQL running during the sample times
SELECT SAMPLEIDS.*, 
       DHS.SQL_TEXT,
       ASH.*
  FROM DBA_HIST_ACTIVE_SESS_HISTORY ASH 
        INNER JOIN SAMPLEIDS ON ASH.SAMPLE_ID=SAMPLEIDS.SAMPLE_ID AND ASH.INSTANCE_NUMBER=SAMPLEIDS.INSTANCE_NUMBER
         LEFT OUTER JOIN DBA_HIST_SQLTEXT DHS ON ASH.SQL_ID=DHS.SQL_ID
         ORDER BY ASH.SAMPLE_ID, ASH.INSTANCE_NUMBER;