6b AWR History Often Executed Queries

6b_awr_history_often_executed_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 sql_text FORMAT A120 HEADING 'SQL_TEXT'
COLUMN seconds_since_date FORMAT 999,999,999,999 HEADING 'SECONDS'
COLUMN execs_since_date FORMAT 999,999,999,999 HEADING 'EXECUTIONS'
COLUMN gets_since_date FORMAT 999,999,999,999 HEADING 'BUFFER_GETS'
COLUMN rows_since_date FORMAT 999,999,999,999 HEADING 'ROWS'
COLUMN average_query_time FORMAT 999,999,999.999 HEADING 'AVG_SEC'

SELECT
    sub.sql_id,
    txt.sql_text,
    sub.seconds_since_date,
    sub.execs_since_date,
    sub.gets_since_date,
    sub.rows_since_date,
    round(sub.seconds_since_date /(sub.execs_since_date + 0.01), 3) average_query_time
FROM
    (
        SELECT
            sql_id,
            round(SUM(elapsed_time_delta) / 1000000) AS seconds_since_date,
            SUM(executions_delta)                    AS execs_since_date,
            SUM(buffer_gets_delta)                   AS gets_since_date,
            SUM(rows_processed_delta)                AS rows_since_date,
            ROW_NUMBER() OVER (ORDER BY round(SUM(elapsed_time_delta) / 1000 / 1000) DESC) r
        FROM
            dba_hist_snapshot NATURAL JOIN dba_hist_sqlstat g
        WHERE
            begin_interval_time > sysdate - 1
            --AND parsing_schema_name = 'BILL'
        GROUP BY
            sql_id
    ) sub
    JOIN dba_hist_sqltext txt ON sub.sql_id = txt.sql_id
WHERE
    r < 50
ORDER BY
    average_query_time DESC;