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>1Plan 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 statement23 rows selected.
这个评估结果和实际返回结果是完全一致的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27064837/viewspace-746143/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27064837/viewspace-746143/