组合列统计信息收集

转自：http://www.muzijiang.cn/index.php/2012/04/gather_columns_group_stats/

上一篇帖子，我们提到通过正确的收集列的统计信息来避免Oracle在计算时得出错误的结果。但我们实例中的SQL非常简单（只有一个条件），实际生产环境中的语句要复杂的多。类似下面的这条SQL语句的查询也非常多见，来看看这种情况下优化器的计算。

SQL > select /*+ gather_plan_statistics */ count(*) from t1 where c+d>1;

 

  COUNT(*)
———-
     93978

 

SQL > select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

PLAN_TABLE_OUTPUT
————————————————————————————-
SQL_ID  6frrqz2cqtknp, child number 1
————————————-
select /*+ gather_plan_statistics */        count(*)       from t1
where c+d>:"SYS_B_0"

Plan hash value: 3724264953

————————————————————————————-
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
————————————————————————————-
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.04 |     364 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |   5000 |  93978 |00:00:01.69 |     364 |
————————————————————————————-

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

   2 – filter("C"+"D">:SYS_B_0)

19 rows selected.

从执行计划的输出结果中可以看出，优化器的评估结果再次出现了20倍左右的误差。造成这种误差的原因是，虽然有C和D列的统计信息，但是并不存在C+D的统计信息。

Oracle 10g解决方案

可以通过设置初始化参数dynamic_sampling=4，在SQL语句执行过程中动态收集，从而得出正确的评估值。

关于dynamic_sampling参数的收集级别，可以查看 http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm#i43032

SQL> alter session set optimizer_dynamic_sampling=4;

 

Session altered.

 

SQL> select /*+ gather_plan_statistics */ count(*) from t1 where c+d>1;

 

  COUNT(*)
———-
     93978

 

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

PLAN_TABLE_OUTPUT
————————————————————————————–
SQL_ID  6frrqz2cqtknp, child number 0
————————————-
select /*+ gather_plan_statistics */        count(*)       from t1
where c+d>:"SYS_B_0"

Plan hash value: 3724264953

————————————————————————————-
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
————————————————————————————-
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.05 |     364 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |  93837 |  93978 |00:00:02.82 |     364 |
————————————————————————————-

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

   2 – filter("C"+"D">:SYS_B_0)

Note
—–
   – dynamic sampling used for this statement

 

23 rows selected.

Oracle 11g解决方案

11g中引入了著名的“Extended Statistics”，解决多列组合查询或者谓词中存在表达式时，优化器如何准确评估的问题。

和extended statistics相关视图：

• user_stat_extensions

基于上面的查询语句，创建C+D列的扩展统计。

SQL> select dbms_stats.create_extended_stats(user, 't1', '(c+d)') from dual;

 

dbms_stats.create_extended_stats(user,'t1','(c+d)')
——————————————————————————–
sys_sturfyc68xoinde_ih320edb2h

 

SQL> select * from USER_STAT_EXTENSIONS;

 

TABLE_NAME   EXTENSION_NAME                EXTENSION   CREATO DRO
————– —————————— ———— —— —-
T1          SYS_STURFYC68XOINDE_IH320EDB2H ("C"+"D")   USER   YES

 

SQL> select /*+ gather_plan_statistics */ count(*) from t1 where c+d>1;

 

  COUNT(*)
———-
     93978

 

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
SQL_ID 1dp0vu169g6xt, child number 1
————————————-
select /*+ gather_plan_statistics */ count(*) from t1 where c+d>1

Plan hash value: 3724264953

——————————————————————————————————
| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |  A-Time   | Buffers | Reads  |
——————————————————————————————————
|   0 | SELECT STATEMENT          |      |  1   |       |    1   |00:00:00.79 | 361     |  243 |
|   1 |  SORT AGGREGATE           |      |  1   |     1 |    1   |00:00:00.79 | 361     |  243 |
|*  2 |   TABLE ACCESS STORAGE FULL| T1    |  1   |  93978 |  93978 |00:00:00.09 | 361     |  243 |
——————————————————————————————————

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

   2 – filter("C"+"D">1)

Note
—–
   – cardinality feedback used for this statement

23 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14710393/viewspace-751764/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14710393/viewspace-751764/