Oracle DBMS_STATS.SET_TABLE_STATS 模擬大資料量進行 SQL 除錯和最佳化

531968912發表於2016-07-22
Oracle大資料SQL除錯
生產上有一張 200 萬條的表，需要進行 SQL 最佳化；測試環境中的這張表，裡面只有 10 萬行的資料。
不需要真正插入 190 萬條的資料，可以透過DBMS_STATS.SET_TABLE_STATS 這個包進行表統計資訊的設定，來模擬大資料量的場景。

首先，對測試環境的這張表進行統計資訊收集，以得到準確的統計資訊

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS ('acct', 'elmp_trans_custommessage');
END;

檢視這張表的相關資訊（行數、使用的所有資料塊、每行的位元組數）

select table_name,num_rows,blocks,avg_row_len from user_tables where lower(table_name) = 'elmp_trans_custommessage';



下面是使用這張表的SQL執行計劃




根據上面查詢到的資訊，重新設定這張表的統計資訊，將表的資料量增大到 200 萬條

計算200萬條時，這張表的資料塊大小

select round(3268*2000000/111684) from dual;

58522

手動設定這張表的統計資訊

BEGIN
  DBMS_STATS.SET_TABLE_STATS(OWNNAME => 'ACCT',
                             TABNAME  => 'ELMP_TRANS_CUSTOMMESSAGE',
                             NUMROWS  => 2000000,
                             NUMBLKS  => 58522,
                             AVGRLEN  => 202);
END;

檢視這張表的相關資訊（行數、使用的所有資料塊、每行的位元組數），可以看到，我們手動設定的表的相關資訊已經生效了

select table_name,num_rows,blocks,avg_row_len from user_tables where lower(table_name) = 'elmp_trans_custommessage';



重新檢視執行計劃，可以看到成本從 891 增大到 15900





建立索引

create index acct.idx_trans_custommessage on acct.elmp_trans_custommessage(CustomMessageType,CUSTOMMESSAGESTATUS) tablespace TS_ACCT_INDEX;

加上索引的 hint，檢視執行計劃，可以看到成本的大幅下降




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2122395/，如需轉載，請註明出處，否則將追究法律責任。

相關文章