統計分析中不恰當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》，網址：