oracle擴充統計資訊

SQL> create table t as select mod(rownum,50) as n1,mod(rownum,50) as n2 from dual connect by level<=1000;

SQL> exec dbms_stats.gather_table_stats(user,'t');

SQL> select column_name,histogram from user_tab_col_statistics where table_name='T';

COLUMN_NAME   HISTOGRAM

N1                                NONE

N2                                       NONE

看到是沒有直方圖資訊的

SQL> select count(*) from t where n1=42 and n2=42;

  COUNT(*)

----------

        20

SQL> explain plan for  select * from t where n1=42 and n2=42;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT  |      |     1 |     6 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |     6 |     3   (0)| 00:00:01 |

在評估結果集的時候，oracle認為傳回了1行記錄，但是實際上是傳回的20條記錄

SQL> SELECT dbms_stats.create_extended_stats(ownname=>user, tabname=>'t', extension=>'(n1,n2)') from

DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>USER,TABNAME=>'T',EXTENSION=>'(N1,N2)'

SYS_STUBZH0IHA7K$KEBJVXO5LOHAS

SQL> execute dbms_stats.gather_table_stats(user,'t');

PL/SQL 過程已成功完成。

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("N1"=42 AND "N2"=42)

在建立了擴充統計資訊後，重新收集，依然還是評估傳回了1行

SQL> select table_name,column_name,density,histogram from user_tab_col_statistics where table_name='T';

TABLE_NAME           COLUMN_NAME             DENSITY HISTOGRAM

-------------------- -------------------- ---------- --------------------

T                    N1                                                       .0005          FREQUENCY

T                    N2                                                      .0005           FREQUENCY

T                    SYS_STUBZH0IHA7K$KEB        .02                NONE

                     JVXO5LOHAS

看到在擴充列上沒有直方圖

|   0 | SELECT STATEMENT  |      |    20 |   120 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |    20 |   120 |     3   (0)| 00:00:01 |

已選擇13行。

再次收集了統計資訊，這次的評估是傳回了20行，這個是正确的

COLUMN_NAME          HISTOGRAM

-------------------- --------------------

N1                   FREQUENCY

N2                   FREQUENCY

SYS_STUBZH0IHA7K$KEB FREQUENCY

JVXO5LOHAS

現在擴充列上也有了直方圖資訊

注意的就是擴充列上要有直方圖資訊，如果沒有的化，優化器可能對傳回的記錄數的評估還是錯誤的

擴充列是11g的特性，10g是沒有的，但是如果10g上出現了這種2個字段的and出現了平谷錯誤，要怎麼處理呢？

可以加大動态采樣的級别，采集更多的樣本，達到正确評估資料的目的。參考連結如下：

http://scn.sap.com/community/oracle/blog/2013/06/04/oracle-db-optimizer-part-v--introduction-of-dynamic-sampling-and-why-is-it-used-in-sap-bi-environments-by-sap-default