12c bulk load统计信息自动收集

从12c开始,对于data load操作,比如CTAS或者direct path insert(append)等的操作,统计信息是自动进行收集的。这个特性叫做online statistics gathering。
官方文档链接http://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL347

本实验参考了jonathan lewis的创建表的脚本。测试了直接append insert的情况下表没有INDEX的情况、有INDEX的情况下、收集extended统计信息的情况。

实验环境 OEL6.5+12.1.0.2 RAC

1.对表直接进行append insert
create table t (id1 number, id2 number);
insert /*+ append */
into t
select object_id, data_object_id
from all_objects
where
rownum <= 100000;
;
commit;

89210 rows created.

select blocks,num_rows from user_tables where table_name = ‘T’;
BLOCKS NUM_ROWS
———- ———-
151 89210
select column_name, num_distinct, num_nulls, density, low_value, high_value from user_tab_cols where table_name = ‘T’;
COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY LOW_VALUE HIGH_VALUE
——————– ———— ———- ———- ———– ———-
ID1 89210 0 .00001121 C103 C30A150E
ID2 5929 83244 .000168663 80 C30A150E

2.删除原表的数据,重新加载不同数量的数据,统计信息收集正常
truncate table t;

insert /*+ append */
into t
select object_id, data_object_id
from all_objects
where
rownum <= 5000;
;
commit;

select blocks,num_rows from user_tables where table_name = ‘T’;
BLOCKS NUM_ROWS
———- ———-
11 5000

select column_name, num_distinct, num_nulls, density, low_value, high_value from user_tab_cols where table_name = ‘T’;
COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY LOW_VALUE HIGH_VALUE
——————– ———— ———- ———- ——————————– ————-
ID1 5000 0 .0002 C103 C23459
ID2 1159 3804 .000862813 C103 C22E62

3.创建INDEX情况下的表和索引的统计信息收集情况
runcate table t;
create index idx_t_id1 on t(id1); –创建index测试是否自动收集statistics

insert /*+ append */
into t
select object_id, data_object_id
from all_objects
where
rownum <= 100000;
;
commit;

select blocks,num_rows from user_tables where table_name = ‘T’;
select column_name, num_distinct, num_nulls, density, low_value, high_value from user_tab_cols where table_name = ‘T’;
select table_name,index_name,index_type,leaf_blocks,distinct_keys,clustering_factor,num_rows from user_indexes where index_name = ‘IDX_T_ID1’;

BLOCKS NUM_ROWS
———- ———-
151 89213

COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY LOW_VALUE HIGH_VALUE
—————————————— ———- ———- ————– ————————
ID1 89213 0 .000011209 C103 C30A1514
ID2 5932 83244 .000168577 80 C30A1514

TABLE_NAME INDEX_NAME INDEX_TYPE LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
—————— —————- ————————— ———– ————- —————– ———-
T IDX_T_ID1 NORMAL 0 0 0 0

INDEX的统计信息没有自动收集。

4.创建EXTENDED STATISTICS的情况
drop index idx_t_id1;
select dbms_stats.create_extended_stats(user,’t’,'(id1, id2)’) name from dual; –创建extended statistics

insert /*+ append */
into t
select object_id, data_object_id
from all_objects
where
rownum <= 100000;
;
commit;

select blocks,num_rows from user_tables where table_name = ‘T’;
select column_name, num_distinct, num_nulls, density, low_value, high_value from user_tab_cols where table_name = ‘T’;

BLOCKS NUM_ROWS
———- ———-
151 89212

COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY LOW_VALUE HIGH_VALUE
———————————- ———— ———- ———- ————————– ———————————————————————-
ID1 89212 0 .000011209 C103 C30A1511
ID2 5931 83244 .000168606 80 C30A1516
SYS_STU3RTXGYOX7NS$MIUDXQDMQ0C 88984 0 .000011238 C8024401220D283B17 CA132D43551A2325573809

12.1.0.1版本下在加载数据之前进行创建extended statistics后会造成统计信息不能收集到。
详见Bug 18425876 : EXTENDED STATS PREVENTS AUTOMATIC STATS GATHERING

日志 | 此条目发表在CBO分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s