oracle 11g 扩展统计信息extended_stats

53 阅读 0 评论 35 点赞

我是靠谱客的博主彩色水杯，最近开发中收集的这篇文章主要介绍oracle 11g 扩展统计信息extended_stats，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

oracle 11g在统计信息收集方面增加了扩展统计信息的特性,它可以收集一个表中相关列上的统计信息,也可以收集函数表达式上的
统计信息.使选择率,成本的估计更加准确,也更容易走正确的执行计划.在相关列上收集统计信息,好处还是很明显的.例如两列在逻辑
上有一定的关系,但如果只是对这两个列单独做统计信息的收集,根据多条件的选择率计算{(A AND B的选择率为:OPSEL[a]*OPSEL[b]);
(A OR B 的选择率为:OPSEL[a]+OPSEL[b]-OPSEL[a]OPSEL[b]);(NOT A的选择率为:1-OPSEL[a])},估算出来的选择率就可能偏差很大.

以下测试:
DB Version:11.2.0.4
----产生测试数据
drop table scott.test01 purge;
create table scott.test01
as select * from dba_objects;

--把object_name 更新为和object_type一样,用于测试.
update scott.test01
set object_name=object_type;
commit;

1.收集单列统计信息,查看执行计划
--收集单列统计信息
begin
dbms_stats.gather_table_stats('scott','test01');
end;
--查看表的行数
select table_name,num_rows from dba_tables
where owner = 'SCOTT' and table_name = 'TEST01';
/*
TABLE_NAME NUM_ROWS
TEST01 87212
*/
--产生语句的执行计划
explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX';

SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation,
       options,
       object_name,
       cardinality,
       bytes,
       io_cost,
       cpu_cost,
       cost,
       time
FROM plan_table
START WITH id = 0
CONNECT BY PRIOR id = parent_id;
/*
OPERATION OPTIONS OBJECT_NAME CARDINALITY BYTES IO_COST CPU_COST COST TIME
SELECT STATEMENT      41 3362 347 35338490 348 5
TABLE ACCESS FULL TEST01 41 3362 347 35338490 348 5
*/
这里可以看到,估算的返回行数是41,显然和实际相差很远
rollback;

--行数估算
select rpad(column_name, 30, ' ') column_name,
       rpad(num_distinct, 8, ' ') num_distinct,
       rpad(utl_raw.cast_to_varchar2(low_value), 15, ' ') low_value,
       rpad(utl_raw.cast_to_varchar2(high_value), 10, ' ') high_value,
       rpad(num_nulls, 8, ' ') num_nulls,
       rpad(avg_col_len, 6, ' ') avg_col_len,
       rpad(density, 20, ' ') density,
       histogram
from dba_tab_col_statistics
where owner = 'SCOTT'
   and table_name = 'TEST01'
   and column_name in ('OBJECT_NAME','OBJECT_TYPE');
/*
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE NULLABLE NUM_NULLS AVG_COL_LEN DENSITY HISTOGRAM
OBJECT_NAME   46        CLUSTER          XML SCHEMA Y         0         9       .0217391304347826     NONE
OBJECT_TYPE   46        CLUSTER          XML SCHEMA Y         0         9       .0217391304347826     NONE
*/
估算的返回行数是41,是由两个列的density相乘再乘以表的行数得到,.0217391304347826*.0217391304347826*87212=41.2155009451796=41

2.收集多列扩展统计信息,查看执行计划
--收集多列扩展统计信息
begin
dbms_stats.gather_table_stats('scott','test01',method_opt =>'for columns (object_name,object_type)');
end;

--产生语句的执行计划
explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX';

SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation,
       options,
       object_name,
       cardinality,
       bytes,
       io_cost,
       cpu_cost,
       cost,
       time
FROM plan_table
START WITH id = 0
CONNECT BY PRIOR id = parent_id;
/*
OPERATION OPTIONS OBJECT_NAME CARDINALITY BYTES IO_COST CPU_COST COST TIME
SELECT STATEMENT           5303   498482   347   36285951   348   5
TABLE ACCESS   FULL   TEST01   5303   498482   347   36285951   348   5
*/
这里可以看到,估算的返回行数是5303,已经基本上和实际返回行数相近.

PS:
1.扩展统计信息的收集,可以用select dbms_stats.create_extended_stats('scott','test01','(object_name,object_type)')from dual
创建扩展统计列,然后dbms_stats.gather_table_stats('scott','test01')收集统计信息,也可以直接在
dbms_stats.gather_table_stats中的method_opt属性同时建立扩展统计又收集统计数据.
2.oracle 11g不仅可以收集多列扩展统计信息,还可以收集函数和表达式的扩展统计信息.

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

转载于:http://blog.itpub.net/28539951/viewspace-1735346/