5 Detailed History For Particular Query

5_detailed_history_for_particular_query.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 snap_id FORMAT 999999999 HEADING 'SNAP_ID'
COLUMN sql_id FORMAT A13 HEADING 'SQL_ID'
COLUMN plan_hash_value FORMAT 999999999999999 HEADING 'PLAN_HASH'
COLUMN end_interval_time FORMAT A20 HEADING 'END_INTERVAL'
COLUMN executions_delta FORMAT 999,999,999 HEADING 'EXECS_DELTA'
COLUMN "Elapsed Avg ms" FORMAT 999,999,999.9
COLUMN "CPU Avg ms" FORMAT 999,999,999.9
COLUMN "IO Avg ms" FORMAT 999,999,999.9
COLUMN "Avg Buffer Gets" FORMAT 999,999,999.9
COLUMN "Avg Disk Reads" FORMAT 999,999,999.9
COLUMN "Avg Physical Writes in MB" FORMAT 999,999,999.9
COLUMN "Avg Physical Reads in MB" FORMAT 999,999,999.9
COLUMN "Avg Rows Processed" FORMAT 999,999,999.9

SELECT
    snap_id,
    sql_id,
    plan_hash_value,
    TO_CHAR(end_interval_time, 'YYYY-MM-DD HH24:MI:SS') end_interval_time,
    executions_delta,
    round(elapsed_time_delta /(nonzeroexecutions * 1000), 1) "Elapsed Avg ms",
    round(cpu_time_delta /(nonzeroexecutions * 1000), 1)     "CPU Avg ms",
    round(iowait_delta /(nonzeroexecutions * 1000), 1)       "IO Avg ms",
    round(buffer_gets_delta /(nonzeroexecutions * 1000), 1)  "Avg Buffer Gets",
    round(disk_reads_delta /(nonzeroexecutions * 1000), 1)   "Avg Disk Reads",
    round(trunc(physical_write_bytes_delta /(nonzeroexecutions * 1024 * 1024)),
          1)                                                 "Avg Physical Writes in MB",
    round(trunc(physical_read_bytes_delta /(nonzeroexecutions * 1024 * 1024)),
          1)                                                 "Avg Physical Reads in MB",
    round(rows_processed_delta /(nonzeroexecutions), 1)      "Avg Rows Processed"
FROM
    (
        SELECT
            ss.snap_id,
            ss.sql_id,
            ss.plan_hash_value,
            sn.end_interval_time,
            ss.executions_delta,
            CASE ss.executions_delta WHEN 0 THEN 1 ELSE ss.executions_delta END nonzeroexecutions,
            elapsed_time_delta,
            cpu_time_delta,
            iowait_delta,
            buffer_gets_delta,
            disk_reads_delta,
            physical_write_bytes_delta,
            physical_read_bytes_delta,
            rows_processed_delta
        FROM
            dba_hist_sqlstat  ss,
            dba_hist_snapshot sn
        WHERE
            ss.sql_id = 'd2b04u19u9khc'
            AND ss.snap_id = sn.snap_id
            AND ss.instance_number = sn.instance_number
    )
WHERE
    elapsed_time_delta > 0
ORDER BY
    snap_id DESC;