嚴重 screwed column，強制指定建立 254 階直方圖，讓oracle 選擇正確excution plan

REPORT_PROCESS_MAIL 嚴重 screwed 

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => 'REVIEWREPORT',
        tabname          => 'REPORT_PROCESS_MAIL',
        method_opt       => 'FOR COLUMNS SIZE 254 SEND_MAIL_FLAG',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        -- estimate_percent => 100,
        cascade          => TRUE
    );
END;
/

收集後驗證 Histogram 是否成功建立

SELECT column_name, num_distinct, num_buckets, histogram
  FROM all_tab_col_statistics
WHERE owner = 'REVIEWREPORT' 
     AND table_name = 'REPORT_PROCESS_MAIL'
     AND column_name = 'SEND_MAIL_FLAG';

如果 HISTOGRAM 顯示 FREQUENCY 或 HYBRID（且 NUM_BUCKETS > 1），才代表直方圖建立成功

以下SQL 可以觀察 直方圖分布
SELECT 
    column_name,
    endpoint_number,     -- 累積筆數/端點編號 (Cumulative row count or bucket number)
    endpoint_value,      -- 數值型態的值 (若是文字型態會被轉成 Double)
    endpoint_actual_value-- Oracle 12c+ 用於顯示文字型態的真實字串 (若是文字欄位)
FROM all_tab_histograms
WHERE owner = 'REVIEWREPORT'
  AND table_name = 'REPORT_BATCH_MAIL'
  AND column_name = 'ERROR_FLAG' -- 指定要查看直方圖的欄位
ORDER BY endpoint_number;

將shared pool 裡面的SQL execution plan 拿掉

1.
SELECT address, hash_value, sql_text
   FROM v$sql
WHERE sql_id = 'gy4mvy9rkpmfu';

2.
BEGIN DBMS_SHARED_POOL.PURGE('00007FFBC18DC578, 1865076186', 'C'); END;

抓 Oracle full table scan

SELECT * FROM (

    SELECT 

        --s.parsing_schema_name as "執行Schema",

        p.OBJECT_OWNER,p.OBJECT_NAME,

        s.sql_id,

        

        -- 執行週期核心指標

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

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

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

        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 "平均每隔幾秒執行一次",

        --ROUND((((SYSDATE - TO_DATE(s.first_load_time, 'YYYY-MM-DD/HH24:MI:SS')) * 86400) / DECODE(s.executions, 0, 1, s.executions)) / 60, 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 1 = 1

      AND p.operation = 'TABLE ACCESS' 

      AND p.options = 'FULL'

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

      AND s.executions > 0  

      --AND s.SQL_TEXT LIKE '%select att.value  || %'

    ORDER BY "單次平均硬碟讀取" DESC

) WHERE ROWNUM <= 100;

Oracle SQL 的 exution plan and statistics

 
1. 需要知道exution plan and statistics 的SQL
select /*+ GATHER_PLAN_STATISTICS */ max(verson) from ESP_NDE_NEW.SYS_LOG t where t.verson !='singleton ver.';

2.
SELECT sql_id, child_number, sql_text, last_active_time
   FROM v$sql
 WHERE UPPER(sql_text) LIKE '%GATHER_PLAN_STATISTICS%' -- 抓有GATHER_PLAN_STATISTICS字眼的
      AND sql_text NOT LIKE '%v$sql%'
 ORDER BY last_active_time DESC;

 3. 查詢 結果
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('71d3ck9v8vtb4', NULL, 'ALLSTATS LAST'));