抓 Oracle full table scan

 SELECT * FROM (

    SELECT 

        s.parsing_schema_name as "執行Schema",  -- 🎯 阮經理要的核心欄位

        s.sql_id,

        s.executions as "總執行次數",

        

        -- 執行週期核心指標

        TO_CHAR(TO_DATE(s.first_load_time, 'YYYY-MM-DD/HH24:MI:SS'), 'YYYY-MM-DD HH24:MI') as "SQL首次載入時間",

        ROUND(s.executions / DECODE(ROUND((SYSDATE - TO_DATE(s.first_load_time, 'YYYY-MM-DD/HH24:MI:SS')) * 24, 2), 0, 1, ROUND((SYSDATE - TO_DATE(s.first_load_time, 'YYYY-MM-DD/HH24:MI:SS')) * 24, 2)), 2) as "平均每小時執行次數",

        ROUND((((SYSDATE - TO_DATE(s.first_load_time, 'YYYY-MM-DD/HH24:MI:SS')) * 86400) / DECODE(s.executions, 0, 1, s.executions)), 1) as "平均每隔幾秒執行一次",

        

        -- I/O 與時間指標

        s.disk_reads as "累積硬碟讀取(Blocks)",

        ROUND(s.disk_reads / DECODE(s.executions, 0, 1, s.executions), 2) as "單次平均硬碟讀取",

        ROUND(s.elapsed_time / 1000000, 2) as "累積消耗時間(秒)",

        ROUND(s.elapsed_time / 1000000 / DECODE(s.executions, 0, 1, s.executions), 2) "平均消耗時間(秒)",

        ROUND(s.cpu_time / 1000000 / DECODE(s.executions, 0, 1, s.executions), 2) "平均CPU時間(秒)",

        

        s.sql_text as "SQL縮略文本"

    FROM v$sql_plan p

    JOIN v$sql s ON p.sql_id = s.sql_id AND p.child_number = s.child_number

    WHERE p.operation = 'TABLE ACCESS' 

      AND p.options = 'FULL'

      AND s.parsing_schema_name NOT IN ('SYS','SYSTEM','SYSMAN','DBA','EXFSYS')

      AND s.executions > 0  

    ORDER BY s.disk_reads DESC

) WHERE ROWNUM <= 20;