11g新特性:Extended Statistics Collect

在11g中，dbms_stats package提供了组合列的统计信息的收集，如果在where条件中使用了组合列进行查询，则优化器将会得到更准确的统计信息，进而输出执行计划时，可以输出更接近与真实数据的统计结果，11g的Extended Statistics包含了组合列与表达式统计信息的收集，表过式统计信息收集主要是针对函数索引字段，下面就组合列统计信息的收集做一下测试：

[@more@]

SQL> create index idx_object_objtype on tmp_objects(object_type,owner) compute statistics;
Index created.

SQL> select index_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,CLUSTERING_FACTOR,NUM_ROWS
from user_indexes
where index_name='IDX_OBJECT_OBJTYPE';
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
------------------------------ ----------- ------------- ----------------- ----------
IDX_OBJECT_OBJTYPE 2 11240 203 208057 3189632

SQL>select count(*)
from tmp_objects
where owner='SYSTEM' and object_type='TABLE';

--------------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 58 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX RANGE SCAN|IDX_OBJECT_OBJTYPE| 15712 | 230K| 58 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE' AND "OWNER"='SYSTEM')

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
33 consistent gets
0 physical reads
0 redo size

SQL> declare
v_name varchar2(30);
begin
v_name := dbms_stats.create_extended_stats('yekai','tmp_objects','(object_type,owner)');
end;

SQL> select dbms_stats.show_extended_stats_name('yekai','tmp_objects','(object_type,owner)') as ex_name
from dual;
EX_NAME
---------------------------------
SYS_STU_UCG1E7VH65UFFJ55F00#FU

SQL> select *
from user_stat_extensions
where table_name='TMP_OBJECTS'

TABLE_NAME EXTENSION_NAME EXTENSION CREATOR DROPPA
------------ -------------------------------- -------------------------------- ------------ ------
TMP_OBJECTS SYS_STU_UCG1E7VH65UFFJ55F00#FU ("OBJECT_TYPE","OWNER") USER YES

SQL> BEGIN
dbms_stats.gather_table_stats(
ownname => 'yekai',
tabname => 'tmp_objects',
method_opt => 'for all columns size skewonly for columns (cust_state_province,country_id) skewonly');
END;

SQL> select e.extension col_group, t.num_distinct, t.histogram
from user_stat_extensions e, user_tab_col_statistics t
where e.extension_name = t.column_name and t.table_name = 'TMP_OBJECTS';

COL_GROUP NUM_DISTINCT HISTOGRAM
------------------------- ------------ ------------------
("OBJECT_TYPE","OWNER") 203 FREQUENCY

SQL>select count(*)
from tmp_objects
where owner='SYSTEM' and object_type='TABLE';

--------------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 44 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | INDEX RANGE SCAN|IDX_OBJECT_OBJTYPE| 11673 | 170K| 44 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("OBJECT_TYPE"='TABLE' AND "OWNER"='SYSTEM')

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
33 consistent gets
0 physical reads
0 redo size

说明：请大家对比收集组合列统计信息前后对tmp_objects进行查询的执行计划，你会发现组合列统计信息收集前SQL的Bytes是230k Cost是58，组合列统计信息收集后SQL的Bytes是170k，Cost是44，这就是组合列统计信息的优势。

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

转载于:http://blog.itpub.net/16648/viewspace-966459/