2 Active Session History Longest Wait Times

2_active_session_history_longest_wait_times.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 sample_time FORMAT A20 HEADING 'SAMPLE_TIME'
COLUMN sql_id FORMAT A13 HEADING 'SQL_ID'
COLUMN username FORMAT A20 HEADING 'USERNAME'
COLUMN sql_text FORMAT A100 HEADING 'SQL_TEXT'
COLUMN total_wait_time FORMAT 999,999,999,999 HEADING 'TOTAL_WAIT'
COLUMN total_io_read_data FORMAT 999,999,999,999 HEADING 'IO_READ'
COLUMN total_memory_reads FORMAT 999,999,999,999 HEADING 'MEM_READ'

SELECT
    TO_CHAR(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') sample_time,
    ash.sql_id,
    u.username,
    sqa.sql_text,
    SUM(ash.wait_time + ash.time_waited)                             total_wait_time,
    round(SUM(ash.delta_read_io_bytes /(ash.delta_time / 1000000)))  total_io_read_data,
    round(SUM(ash.delta_read_mem_bytes /(ash.delta_time / 1000000))) total_memory_reads
FROM
    v$active_session_history ash,
    v$sqlarea                sqa,
    dba_users                u
WHERE
        ash.sql_id = sqa.sql_id
    AND ash.user_id = u.user_id
    AND u.username != 'SYS'
    AND ash.sample_time > sysdate - 3 / 24 -- last 3 hours
GROUP BY
    TO_CHAR(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS'),
    ash.sql_id,
    u.username,
    sqa.sql_text
ORDER BY
    5 DESC;