嚴重 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$），才代表直方圖建立成功

將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",  -- 🎯 阮經理要的核心欄位

        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;

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'));