6a AWR History Long Running Queries

6a_awr_history_long_running_queries.sql

Disclaimer: This script is provided for educational and learning purposes. Review it carefully, test in a non-production environment, confirm privileges, and validate impact before using it in production.

SET PAGESIZE 200
SET LINESIZE 260
SET TRIMSPOOL ON
SET TAB OFF
SET VERIFY OFF
SET FEEDBACK ON

COLUMN sql_id FORMAT A13 HEADING 'SQL_ID'
COLUMN snap_begin_time FORMAT A20 HEADING 'SNAP_BEGIN'
COLUMN snap_end_time FORMAT A20 HEADING 'SNAP_END'
COLUMN sql_text FORMAT A120 HEADING 'SQL_TEXT'
COLUMN executions FORMAT 999,999,999,999 HEADING 'EXECUTIONS'
COLUMN elapsed_time_seconds FORMAT 999,999,999,999.99 HEADING 'ELAPSED_SEC'

SELECT
    t.sql_id,
    TO_CHAR(MIN(sn.begin_interval_time), 'YYYY-MM-DD HH24:MI:SS') snap_begin_time,
    TO_CHAR(MAX(sn.end_interval_time), 'YYYY-MM-DD HH24:MI:SS')   snap_end_time,
    MAX(CAST(dbms_lob.substr(t.sql_text, 200) AS NVARCHAR2(200))) sql_text,
    SUM(s.executions_delta)                                        executions,
    SUM(s.elapsed_time_delta) / 1000 / 1000                        elapsed_time_seconds
FROM
    dba_hist_sqlstat  s,
    dba_hist_sqltext  t,
    dba_hist_snapshot sn
WHERE
    t.sql_id = s.sql_id
    AND s.snap_id = sn.snap_id
    AND sn.begin_interval_time > sysdate - 7
    AND sn.end_interval_time < sysdate
    AND parsing_schema_name NOT IN ('SYS')
GROUP BY
    t.sql_id
ORDER BY
    elapsed_time_seconds DESC;