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 过程已成功完成。


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 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


   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

看到在扩展列上没有直方图

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  |      |    20 |   120 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    20 |   120 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


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


已选择13行。

再次收集了统计信息，这次的评估是返回了20行，这个是正确的

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


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