SQL executions based on AWR

SQL exection per day


SELECT TO_CHAR(s.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD') AS execution_date, ss.SQL_ID, SUM(ss.EXECUTIONS_DELTA) AS executions FROM DBA_HIST_SQLSTAT ss JOIN DBA_HIST_SNAPSHOT s ON ss.SNAP_ID = s.SNAP_ID AND ss.DBID = s.DBID AND ss.INSTANCE_NUMBER = s.INSTANCE_NUMBER WHERE ss.SQL_ID = '1gyhdhv4bcuaf' AND s.BEGIN_INTERVAL_TIME >= SYSDATE - 15 GROUP BY TO_CHAR(s.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD'), ss.SQL_ID ORDER BY execution_date;



SQL exection per hour


SELECT TO_CHAR(s.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24') AS execution_hour, ss.SQL_ID, SUM(ss.EXECUTIONS_DELTA) AS executions FROM DBA_HIST_SQLSTAT ss JOIN DBA_HIST_SNAPSHOT s ON ss.SNAP_ID = s.SNAP_ID AND ss.DBID = s.DBID AND ss.INSTANCE_NUMBER = s.INSTANCE_NUMBER WHERE ss.SQL_ID = 'gg4cuaprd4fsj' -- Replace with actual SQL_ID AND s.BEGIN_INTERVAL_TIME >= SYSDATE - 15 GROUP BY TO_CHAR(s.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24'), ss.SQL_ID ORDER BY execution_hour;


Recent executions (in-memory, last 1 hr typically)
SELECT sql_id, sample_time,session_id,session_serial#, user_id,program FROM v$active_session_history WHERE sql_id = '' ORDER BY sample_time DESC;


-- Historical executions (stored in AWR, retention based on settings)
SELECT sql_id,sample_time,session_id,session_serial#,user_id,program FROM dba_hist_active_sess_history WHERE sql_id = '' ORDER BY sample_time DESC;