重いSQLを抽出する

SQLDeveloperの（たしか）CPU上位SQLを少し改造したもの。
実行1回あたり5秒以上かかっているSQLを洗い出します。

SELECT s.INST_ID,
    (s.cpu_time/1000000) "CPU_Seconds",
    s.disk_reads "Disk_Reads",
    s.buffer_gets "Buffer_Gets",
    s.executions "Executions",
    CASE
        WHEN s.rows_processed = 0
        THEN NULL
        ELSE ROUND((s.buffer_gets/NVL(REPLACE(s.rows_processed,0,1),1)))
    END "Buffer_gets/rows_proc",
    ROUND((s.buffer_gets /NVL(REPLACE(s.executions,0,1),1))) "Buffer_gets/executions",
    (s.elapsed_time      /1000000) "Elapsed_Seconds",
    ROUND((s.elapsed_time/1000000)/NVL(REPLACE(s.executions,0,1),1)) "Elapsed/Execution",
    ga.sql_fulltext "SQL",
    s.module "Module",
    s.SQL_ID
FROM gv$sql s
LEFT JOIN gv$sqlarea ga ON s.inst_id = ga.inst_id and s.sql_id = ga.sql_id
WHERE s.sql_id IN
    ( SELECT DISTINCT sql_id
    FROM
        ( WITH sql_class AS
        (SELECT sql_id,
            state,
            COUNT(*) occur
        FROM
            (SELECT sql_id ,
                CASE
                    WHEN session_state = 'ON CPU'
                    THEN 'CPU'
                    WHEN session_state = 'WAITING'
                    AND wait_class    IN ('User I/O')
                    THEN 'IO'
                    ELSE 'WAIT'
                END state
            FROM gv$active_session_history
            WHERE session_type                         IN ( 'FOREGROUND')
            AND sample_time BETWEEN TRUNC(sysdate,'MI') - :minutes/24/60 AND TRUNC(sysdate,'MI')
            )
        GROUP BY sql_id,
            state
        ),
        ranked_sqls AS
        (SELECT sql_id,
            SUM(occur) sql_occur ,
            rank () over (order by SUM(occur)DESC) xrank
        FROM sql_class
        GROUP BY sql_id
        )
    SELECT sc.sql_id,
        state,
        occur
    FROM sql_class sc,
        ranked_sqls rs
    WHERE rs.sql_id = sc.sql_id
        --and rs.xrank <= :top_n
    ORDER BY xrank,
        sql_id,
        state
        )
    )
AND ROUND((s.elapsed_time/1000000)/NVL(REPLACE(s.executions,0,1),1)) > 5 -- SQL1回あたり5秒を超えるもの
ORDER BY "Elapsed/Execution" DESC NULLS LAST