（一）問題背景
在使用impdp進行數據導入的時候，往往在導入表和索引的統計信息的時候，速度非常慢，因此我在使用impdp進行導入時，會使用exclude=table_statistics排除表的統計信息，從而加快導入速度，之後再手動收集統計信息。
圖.impdp導入數據的時導入統計信息速度非常慢
導入語句如下：
impdp user/password directory=DUMPDIR dumpfile=TEST01.dmp logfile=TEST01.log remap_schema=TEST_USER:TEST_USER123 exclude=table_statistics
手動收集統計信息語句如下：
EXEC dbms_stats.gather_table_stats(ownname => 'LIJIAMAN',tabname => 'TEST01');
最近使用以上方法將數據還原到測試環境后，發現與生產環境執行計劃存在偏差，本來應該走全表掃描的，卻走了索引範圍掃描。經過確認，是由於列的直方圖統計信息未收集引發的執行計劃偏差。
（二）列的直方圖統計信息
什麼是列的直方圖統計信息呢？在Oracle數據庫中，Oracle默認列上的值是在最小值與最大值之間均分佈的，當在計算cardinatity時，會以均勻分佈的方式計算，但是在實際生活中某些場景下數據並非均勻分佈。舉個列子，某公司有員工10000人，表A的列COL1記錄員工的績效(分別是：A、B、C、D，A最好，D最差)，那麼可能A佔了15%，B佔了60，C佔了20%，D佔了5%。很明顯在該場景下數據並非均勻分佈，假如以均勻分佈的方式去統計員工的績效，可能會導致執行計劃失准。
當列的數據分佈不均勻的時候，就需要統計列上的數據分佈情況，從而走出正確的執行計劃，列的直方圖統計信息就是記錄列上的數據分佈情況的。
（三）異常模擬
STEP1:創建測試表test01
create table test01 (id number, name varchar2(10) ); create index idx_test01_id on test01(id);
向test01中插入測試數據
begin insert into test01 values(1,'a'); for i in 1..10 loop insert into test01 values(2,'b'); end loop; for i in 1..100 loop insert into test01 values(3,'c'); end loop; for i in 1..1000 loop insert into test01 values(4,'d'); end loop; commit; end;
查看數據分佈情況：
SQL> SELECT ID,NAME,COUNT(*) FROM test01 GROUP BY ID,NAME ORDER BY COUNT(*); ID NAME COUNT(*) ---------- ---------- ---------- 1 a 1 2 b 10 3 c 100 4 d 1000
STEP2:收集統計信息，因為上面查詢過id列，故在收集統計信息的時候，會收集直方圖的統計信息
EXEC dbms_stats.gather_table_stats(ownname => 'LIJIAMAN',tabname => 'TEST01');
查看是否已經收集了直方圖信息，發現id列上已經收集
SQL> SELECT a.OWNER,a.TABLE_NAME,a.COLUMN_NAME,a.LOW_VALUE,a.HIGH_VALUE,a.NUM_BUCKETS,a.HISTOGRAM 2 FROM dba_tab_columns a 3 WHERE a.OWNER = 'LIJIAMAN' AND a.TABLE_NAME = 'TEST01'; OWNER TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE NUM_BUCKETS HISTOGRAM --------- ----------- ------------ ------------ ------------ ----------- --------------- LIJIAMAN TEST01 ID C102 C105 4 FREQUENCY LIJIAMAN TEST01 NAME 61 64 1 NONE
查看直方圖，已經將id列的4個值放入了4個bucket中：
SQL> SELECT * FROM dba_tab_histograms a WHERE a.OWNER = 'LIJIAMAN' AND a.TABLE_NAME = 'TEST01'; OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE ----------- ------------ ------------- --------------- -------------- ---------------------- LIJIAMAN TEST01 ID 1 1 LIJIAMAN TEST01 ID 11 2 LIJIAMAN TEST01 ID 111 3 LIJIAMAN TEST01 ID 1111 4 LIJIAMAN TEST01 NAME 0 5.036527952778 LIJIAMAN TEST01 NAME 1 5.192296858534
STEP3:查看id=1和id=4的執行計劃，當id=1時，走索引範圍掃描，當id=4時，走全表掃描
| id列存在直方圖統計信息，當id=1時，走索引範圍掃描 | id列存在直方圖統計信息，當id=4時，走全表掃描 |
SELECT * FROM test01 WHERE ID=1 Plan Hash Value : 1151852672 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 2 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 1 | 5 | 2 | 00:00:01 | | * 2 | INDEX RANGE SCAN | IDX_TEST01_ID | 1 | | 1 | 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("ID"=1) |
SELECT * FROM test01 WHERE ID=4 Plan Hash Value : 262542483 ----------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 5000 | 3 | 00:00:01 | | * 1 | TABLE ACCESS FULL | TEST01 | 1000 | 5000 | 3 | 00:00:01 | ----------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 1 - filter("ID"=4) |
STEP4:接下來模擬數據遷移，排除統計信息
導出表test01
expdp lijiaman/lijiaman directory=DUMPDIR tables=LIJIAMAN.TEST01 dumpfile =test01.dmp
刪除原來的表：
SQL> drop table test01; Table dropped
再次導入表，排除統計信息：
impdp lijiaman/lijiaman directory=DUMPDIR dumpfile =test01.dmp exclude=table_statistics
查看錶的統計信息，不存在統計信息：
SQL> SELECT a.OWNER,a.TABLE_NAME,a.COLUMN_NAME,a.LOW_VALUE,a.HIGH_VALUE,a.NUM_BUCKETS,a.HISTOGRAM 2 FROM dba_tab_columns a 3 WHERE a.OWNER = 'LIJIAMAN' AND a.TABLE_NAME = 'TEST01'; OWNER TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE NUM_BUCKETS HISTOGRAM -------------- --------------- --------------- ------------ ------------ ----------- --------------- LIJIAMAN TEST01 ID NONE LIJIAMAN TEST01 NAME NONE
STEP5:手動收集統計信息
EXEC dbms_stats.gather_table_stats(ownname => 'LIJIAMAN',tabname => 'TEST01');
發現統計信息已經收集，但是不存在直方圖的統計信息
SQL> SELECT a.OWNER,a.TABLE_NAME,a.COLUMN_NAME,a.LOW_VALUE,a.HIGH_VALUE,a.NUM_BUCKETS,a.HISTOGRAM 2 FROM dba_tab_columns a 3 WHERE a.OWNER = 'LIJIAMAN' AND a.TABLE_NAME = 'TEST01'; OWNER TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE NUM_BUCKETS HISTOGRAM --------- ----------- ----------- ----------- ----------- ----------- --------------- LIJIAMAN TEST01 ID C102 C105 1 NONE LIJIAMAN TEST01 NAME 61 64 1 NONE
STEP6:再次查看id=1和id=4的執行計劃，當id=1或id=4時，都走索引範圍掃描
| id列未收集直方圖統計信息，當id=1時，走索引範圍掃描 | id列未收集直方圖統計信息，當id=4時，走索引範圍掃描 |
SELECT * FROM test01 WHERE ID=1 Plan Hash Value : 1151852672 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 278 | 1390 | 2 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 278 | 1390 | 2 | 00:00:01 | | * 2 | INDEX RANGE SCAN | IDX_TEST01_ID | 278 | | 1 | 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("ID"=1) |
SELECT * FROM test01 WHERE ID=4 Plan Hash Value : 1151852672 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 278 | 1390 | 2 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 278 | 1390 | 2 | 00:00:01 | | * 2 | INDEX RANGE SCAN | IDX_TEST01_ID | 278 | | 1 | 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("ID"=4) |
STEP7:再次收集統計信息，因為使用過了id列作為查詢條件，故再次收集統計信息時，會收集id列的直方圖信息：
EXEC dbms_stats.gather_table_stats(ownname => 'LIJIAMAN',tabname => 'TEST01');
可以看到，此時已經收集了id列的直方圖統計信息：
SQL> SELECT a.OWNER,a.TABLE_NAME,a.COLUMN_NAME,a.LOW_VALUE,a.HIGH_VALUE,a.NUM_BUCKETS,a.HISTOGRAM 2 FROM dba_tab_columns a 3 WHERE a.OWNER = 'LIJIAMAN' AND a.TABLE_NAME = 'TEST01'; OWNER TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE NUM_BUCKETS HISTOGRAM ------------------------------ ------------------------------ ------------------------------ ------------- ------------- ----------- --------------- LIJIAMAN TEST01 ID C102 C105 4 FREQUENCY LIJIAMAN TEST01 NAME 61 64 1 NONE
執行計劃已經按照我們想要的方式走：
| id列重新收集直方圖統計信息，當id=1時，走索引範圍掃描 | id列重新收集直方圖統計信息，當id=4時，走全表掃描 |
SELECT * FROM test01 WHERE ID=1 Plan Hash Value : 1151852672 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 2 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 1 | 5 | 2 | 00:00:01 | | * 2 | INDEX RANGE SCAN | IDX_TEST01_ID | 1 | | 1 | 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("ID"=1) |
SELECT * FROM test01 WHERE ID=4 Plan Hash Value : 262542483 ----------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 5000 | 3 | 00:00:01 | | * 1 | TABLE ACCESS FULL | TEST01 | 1000 | 5000 | 3 | 00:00:01 | ----------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 1 - filter("ID"=4) |
（四）總結
在使用expdp/impdp進行導出/導入數據的時，統計信息是非常重要的，對於大部分統計信息，我們可以在導入結束之後收集獲得。但是對於列的直方圖統計信息，Oracle默認收集的方式是auto，即Oracle會根據用戶對列的使用情況進行判斷是否收集直方圖統計信息，然而數據剛遷移完成，在表還未使用的情況下收集統計信息，往往收集不到列的直方圖信息，這就造成了執行計劃異常，這種情況通常在下一次收集統計信息之後會有所改變。
參考文檔：
DBMS_STATS With METHOD_OPT =>’..SIZE auto’ May Not Collect Histograms (Doc ID 557594.1)
本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理
【其他文章推薦】
※網頁設計一頭霧水該從何著手呢? 台北網頁設計公司幫您輕鬆架站!
※網頁設計公司推薦不同的風格，搶佔消費者視覺第一線
※Google地圖已可更新顯示潭子電動車充電站設置地點!!
※廣告預算用在刀口上，台北網頁設計公司幫您達到更多曝光效益
※別再煩惱如何寫文案,掌握八大原則!
※網頁設計最專業,超強功能平台可客製化
※回頭車貨運收費標準
