統計分析中不恰當method_opt使用可能造成執行計劃問題
對錶進行dbms_stats.gather_table_stats統計分析時，引數method_opt主要控制兩件事情：
1、決定哪些columns統計被收集
2、決定哪些columns直方圖被收集以及直方圖包含多少個buckets
以下是引數method_opt對錶統計影響實驗：
1、建立測試表
SQL>exec dbms_random.initialize(1);
SQL>create table t1
as
with generator as (
select --+ materialize
rownum pk
from all_objects
where rownum<=4000
)
select
/*+ ordered use_nl(v2)*/
rownum pk,
round(dbms_random.value(1,2)) a,
round(dbms_random.value(1,5)) b,
round(dbms_random.value(1,10)) c,
round(dbms_random.value(1,100)) d,
round(dbms_random.value(1,1000000)) e
from
generator v1,
generator v2
where
rownum<=1000000
/
建立T1表的唯一索引
SQL>create unique index PK_T1 on T1(PK);
2、採用dbms_stats預設統計分析或method_opt=>'for all columns size 1'
SQL>begin
dbms_stats.gather_table_stats(
wnname => user,
tabname => 'T1',
estimate_percent =>100,
cascade => true);
end;
/
檢視統計分析表T1資訊：
SQL>select
column_name,
num_distinct,
histogram, num_buckets,
to_char(LAST_ANALYZED, 'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
from user_tab_col_statistics
where table_name='T1';
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
------------ ------------ ------------ ----------- -------------------
PK 1000000 NONE 1 2009-24-08 11:15:18
A 2 NONE 1 2009-24-08 11:15:18
B 5 NONE 1 2009-24-08 11:15:18
C 10 NONE 1 2009-24-08 11:15:18
D 100 NONE 1 2009-24-08 11:15:18
E 632322 NONE 1 2009-24-08 11:15:18
表中的六個列都有進行了統計分析。
3、method_opt=>'for all indexed columns'
SQL>begin
dbms_stats.delete_table_stats(user, 'T1');
end;
/
SQL>begin
dbms_stats.gather_table_stats(
wnname => user,
tabname=>'T1',
estimate_percent=>100,
method_opt=> 'for all indexed columns',
--method_opt=> 'for all indexed columns size 1',
--method_opt=> 'for all indexed columns size auto',
--method_opt=> 'for all indexed columns size 254',
cascade => true);
end;
/
SQL>select
column_name,
num_distinct,
histogram, num_buckets,
to_char(LAST_ANALYZED, 'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
from user_tab_col_statistics
where table_name='T1';
method_opt=> 'for all indexed columns',輸出結果：
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
------------ ------------ ------------ ----------- -------------------
PK 1000000 HEIGHT BALANCED 75 2009-24-08 15:34:23
method_opt=> 'for all indexed columns size 1|auto',輸出結果：
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
------------ ------------ ------------ ----------- -------------------
PK 1000000 NONE 1 2009-24-08 15:37:40
method_opt=> 'for all indexed columns size 254',輸出結果：
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
------------ ------------ ------------ ----------- -------------------
PK 1000000 HEIGHT BALANCED 254 2009-24-08 15:34:23
mthod_opt=>'for all indexed columns' 只對索引列進行統計分析。
結論：採用method_opt=>‘for all indexed columns'引數，因為只統計索引列的資訊，造成其他列統計分析的缺失，會造成執行計劃錯誤，具體實驗如下：
實驗1：
SQL>select /*+ gather_plan_statistics */
count(*)
from t1
where a=1;
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.19 | 3737 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 10000 | 500K|00:00:00.01 | 3737 |
-------------------------------------------------------------------------------------
評估行數值=10000
實際行數值=500K
兩者的差異=500K/10K=50
因為列a沒有進行統計分析，所以對該列的評估值計算rows*1%=10000
實驗2：
SQL>select /*+ gather_plan_statistics */
count(*)
from t1
where a=1
and b=3;
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.19 | 3737 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 124K|00:00:00.12 | 3737 |
-------------------------------------------------------------------------------------
評估行數值=100
實際行數值=124K
兩者的差異=124K/100=1240
因為列a和B沒有進行統計分析，所以對該列的評估值計算rows*1%*1%=100
實驗3：
SQL>select /*+ gather_plan_statistics */
count(*)
from t1
where a=1
and b=3
and d+e>50;
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.25 | 3737 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 5 | 124K|00:00:00.25 | 3737 |
-------------------------------------------------------------------------------------
評估行數值=5
實際行數值=124K
兩者的差異=124K/5=25800
條件中包含未統計的列越多，執行計劃偏離越大，所以我們在進行統計分析中要慎用method_opt=>'for all indexed columns'。
當我們採用method_opt=>'for all columns size 1'，執行計劃中的評估值(E-ROWS)
和實際值(A-ROWS)差異將大大縮小。通常建議該差異值不要超過100。
備註:
以上資訊來源於Greg Rahn寫的《DBMS_STATS, METHOD_OPT and FOR ALL INDEXED COLUMNS》，網址：http://structureddata.org/2008/10/14/dbms_stats-method_opt-and-for-all-indexed-columns/
1、決定哪些columns統計被收集
2、決定哪些columns直方圖被收集以及直方圖包含多少個buckets
以下是引數method_opt對錶統計影響實驗：
1、建立測試表
SQL>exec dbms_random.initialize(1);
SQL>create table t1
as
with generator as (
select --+ materialize
rownum pk
from all_objects
where rownum<=4000
)
select
/*+ ordered use_nl(v2)*/
rownum pk,
round(dbms_random.value(1,2)) a,
round(dbms_random.value(1,5)) b,
round(dbms_random.value(1,10)) c,
round(dbms_random.value(1,100)) d,
round(dbms_random.value(1,1000000)) e
from
generator v1,
generator v2
where
rownum<=1000000
/
建立T1表的唯一索引
SQL>create unique index PK_T1 on T1(PK);
2、採用dbms_stats預設統計分析或method_opt=>'for all columns size 1'
SQL>begin
dbms_stats.gather_table_stats(
wnname => user,
tabname => 'T1',
estimate_percent =>100,
cascade => true);
end;
/
檢視統計分析表T1資訊：
SQL>select
column_name,
num_distinct,
histogram, num_buckets,
to_char(LAST_ANALYZED, 'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
from user_tab_col_statistics
where table_name='T1';
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
------------ ------------ ------------ ----------- -------------------
PK 1000000 NONE 1 2009-24-08 11:15:18
A 2 NONE 1 2009-24-08 11:15:18
B 5 NONE 1 2009-24-08 11:15:18
C 10 NONE 1 2009-24-08 11:15:18
D 100 NONE 1 2009-24-08 11:15:18
E 632322 NONE 1 2009-24-08 11:15:18
表中的六個列都有進行了統計分析。
3、method_opt=>'for all indexed columns'
SQL>begin
dbms_stats.delete_table_stats(user, 'T1');
end;
/
SQL>begin
dbms_stats.gather_table_stats(
wnname => user,
tabname=>'T1',
estimate_percent=>100,
method_opt=> 'for all indexed columns',
--method_opt=> 'for all indexed columns size 1',
--method_opt=> 'for all indexed columns size auto',
--method_opt=> 'for all indexed columns size 254',
cascade => true);
end;
/
SQL>select
column_name,
num_distinct,
histogram, num_buckets,
to_char(LAST_ANALYZED, 'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
from user_tab_col_statistics
where table_name='T1';
method_opt=> 'for all indexed columns',輸出結果：
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
------------ ------------ ------------ ----------- -------------------
PK 1000000 HEIGHT BALANCED 75 2009-24-08 15:34:23
method_opt=> 'for all indexed columns size 1|auto',輸出結果：
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
------------ ------------ ------------ ----------- -------------------
PK 1000000 NONE 1 2009-24-08 15:37:40
method_opt=> 'for all indexed columns size 254',輸出結果：
COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS LAST_ANALYZED
------------ ------------ ------------ ----------- -------------------
PK 1000000 HEIGHT BALANCED 254 2009-24-08 15:34:23
mthod_opt=>'for all indexed columns' 只對索引列進行統計分析。
結論：採用method_opt=>‘for all indexed columns'引數，因為只統計索引列的資訊，造成其他列統計分析的缺失，會造成執行計劃錯誤，具體實驗如下：
實驗1：
SQL>select /*+ gather_plan_statistics */
count(*)
from t1
where a=1;
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.19 | 3737 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 10000 | 500K|00:00:00.01 | 3737 |
-------------------------------------------------------------------------------------
評估行數值=10000
實際行數值=500K
兩者的差異=500K/10K=50
因為列a沒有進行統計分析，所以對該列的評估值計算rows*1%=10000
實驗2：
SQL>select /*+ gather_plan_statistics */
count(*)
from t1
where a=1
and b=3;
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.19 | 3737 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 124K|00:00:00.12 | 3737 |
-------------------------------------------------------------------------------------
評估行數值=100
實際行數值=124K
兩者的差異=124K/100=1240
因為列a和B沒有進行統計分析，所以對該列的評估值計算rows*1%*1%=100
實驗3：
SQL>select /*+ gather_plan_statistics */
count(*)
from t1
where a=1
and b=3
and d+e>50;
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.25 | 3737 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 5 | 124K|00:00:00.25 | 3737 |
-------------------------------------------------------------------------------------
評估行數值=5
實際行數值=124K
兩者的差異=124K/5=25800
條件中包含未統計的列越多，執行計劃偏離越大，所以我們在進行統計分析中要慎用method_opt=>'for all indexed columns'。
當我們採用method_opt=>'for all columns size 1'，執行計劃中的評估值(E-ROWS)
和實際值(A-ROWS)差異將大大縮小。通常建議該差異值不要超過100。
備註:
以上資訊來源於Greg Rahn寫的《DBMS_STATS, METHOD_OPT and FOR ALL INDEXED COLUMNS》，網址：http://structureddata.org/2008/10/14/dbms_stats-method_opt-and-for-all-indexed-columns/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-613088/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 通過執行計劃中的CONCATENATION分析sql問題SQL
- 透過執行計劃中的CONCATENATION分析sql問題SQL
- 一個執行計劃解析的小問題分析
- 執行計劃不穩定的原因分析
- (4) MySQL中EXPLAIN執行計劃分析MySqlAI
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- explain執行計劃分析AI
- SQL執行計劃分析SQL
- 由於統計量失真造成SQL執行計劃錯誤一例SQL
- 分析執行計劃最佳化SQLORACLE的執行計劃(轉)SQLOracle
- 交流（1）-- 執行計劃錯誤問題
- mysql的執行計劃快取問題MySql快取
- OGNL設計及使用不當造成的遠端程式碼執行漏洞
- 【PG執行計劃】Postgresql資料庫執行計劃統計資訊簡述SQL資料庫
- 執行計劃變化導致CPU負載高的問題分析負載
- 執行計劃-1：獲取執行計劃
- DB2執行計劃分析DB2
- SqlServer的執行計劃如何分析？SQLServer
- 【優化】ORACLE執行計劃分析優化Oracle
- 統計資訊不準確導致執行計劃走了笛卡爾積
- SQL執行計劃異常引起的效能問題SQL
- SQL執行計劃異常 引起的效能問題SQL
- 完美的執行計劃導致的效能問題
- 執行計劃的偏差導致的效能問題
- 使用10046事件跟蹤分析執行計劃事件
- 解決TOAD中執行計劃顯示報錯的問題
- [統計分析] DBMS_STATS, METHOD_OPT and FOR ALL INDEXED COLUMNSIndex
- 執行計劃
- PostgreSQL 查詢當前執行中sql的執行計劃——pg_show_plans模組SQL
- mysql 執行計劃索引分析筆記MySql索引筆記
- 看懂Oracle中的執行計劃Oracle
- oracle中開啟執行計劃Oracle
- 使用hint改變執行計劃
- oracle執行計劃的使用(EXPLAIN)OracleAI
- 使用sql profile固定執行計劃SQL
- 執行計劃中的COLLECTION ITERATOR PICKLER FETCH導致的效能問題
- Oracle訪問表的執行計劃Oracle
- Oracle 執行計劃 訪問路徑Oracle