1 SQL Statements In AWR History Tables

1_sql_statements_in_awr_history_tables.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 220
SET TRIMSPOOL ON
SET TAB OFF
SET VERIFY OFF
SET FEEDBACK ON

COLUMN sql_id FORMAT A13 HEADING 'SQL_ID'
COLUMN statement FORMAT A100 HEADING 'STATEMENT'
COLUMN cpu_time FORMAT 999,999,999,999 HEADING 'CPU_TIME'

SELECT
    a.sql_id,
    dbms_lob.substr(t.sql_text, 100, 1) statement,
    SUM(a.cpu_time_delta)               cpu_time
FROM
    dba_hist_sqlstat  a,
    dba_hist_snapshot s,
    dba_hist_sqltext  t
WHERE
        s.snap_id = a.snap_id
    AND a.sql_id = t.sql_id
    AND s.begin_interval_time > sysdate - 1 -- yesterday
    AND EXTRACT(HOUR FROM s.end_interval_time) BETWEEN 0 AND 24
GROUP BY
    a.sql_id,
    dbms_lob.substr(t.sql_text, 100, 1)
ORDER BY
    SUM(a.cpu_time_delta) DESC;