【Oracle Database 12c新特性】Online Statistics Gathering for Bulk-Load 针对批量数据加载的在线统计信息收集

Oracle database 12c中提出了Online Statistics Gathering for Bulk-Load 针对批量数据加载的在线统计信息收集的新特性。

 

通过online statistics gathering，当出现某些批量数据加载操作例如CREATE TABLE AS SELECT CTAS操作 或者 针对一个空表的INSERT INTO … SELECT操作时，统计信息将被自动收集。

 

online statistics gathering省略了当一个批量数据加载后的必要手动统计信息收集操作； 大家还记得我们在讲10/11g 性能调优时 关于数据量大幅变化操作后的手动收集统计信息建议吗？　实际上这个特性一定程度就是为了解决这里还需要手动去收集一次的麻烦。　　这个特性表现的很像之前的CREATE INDEX或REBUILD INDEX时自动完成的统计信息收集。  Oracle通过内部维护操作来维护CTAS或物化视图刷新的统计信息更新 。

 

在数据仓库中，用户经常需要加载大量的数据到数据库中； 这里online statistics gathering 就可以起到作用。

Oracle Database 12c中默认启用这种自动统计信息收集特性，主要的收益在于提升批量加载数据后的SQL性能和可管理性，不在需要用户介入来人工收集了。 由于不在需要手动收集统计信息， 所以也就避免了后续的一次可能的全表扫描。

当使用Online Statistics Gathering时，数据库不收集索引统计信息和直方图。如果确实需要索引统计信息和直方图，则Oracle推荐在批量加载数据后再次使用DBMS_STATS.GATHER_TABLE_STATS。   默认情况下 DBMS_STATS.GATHER_TABLE_STATS仅收集缺失的统计信息，因此当你在bulk load批量加载后执行DBMS_STATS.GATHER_TABLE_STATS，数据库将仅仅收集索引统计信息和直方图histograms， 而表和字段的统计信息将不再被收集。

 

补充1点： SYS用户的对象不启用Online Statistics Gathering，不要使用SYS用户去测试该特性。

 

Online Statistics Gathering for Bulk-Load 的其他限制：

 

1. It is in an Oracle-owned schema such as SYS.
2. It is a nested table.
3. It is an index-organized table (IOT).
4. It is an external table.
5. It is a global temporary table defined as ON COMMIT DELETE ROWS.
6. It has virtual columns.
7. It has a PUBLISH preference set to FALSE.
8. It is partitioned, INCREMENTAL is set to true, and extended syntax is not used.

 

FROM 孟买-老托拉呱的笔记：

在Oracle Database 12c中，如下两种Bulk-Load方式下，系统将会自动收集表上的统计信息
¤ CTAS – Create Table As Select …
¤ IIS – Insert Into … Select …

说明：（1）必须是使用direct path insert到一个空表/空分区的情况下

（2）如果是空分区表，收集的是global statistics而不是partition-level statistics。

如果是插入到指定的分区/子分区（空)，则收集partition-level statistics而不是global statistics。比如Insert Into sales PARTITION(sales_q1_2013) Select …
如果在插入前，分区sales_q1_2013是空的（其他分区不论是否为空），那么就会收集统计信息。如果表上启用了Incremental Statistics Maintenance属性（11gR2开始提供的特性），那么同时也会自动该分区的摘要(synopsis）信息。

（3）如果rollback，统计信息自动删除。

（4）这个特性，不收集index statistics or histograms，所以，如果需要，Oracle推荐通过DBMS_STATS.GATHER_TABLE_STATS(options => ‘GATHER AUTO’…)
来收集index statistics or histograms。

这就有点象从10g版本开始create index/rebuild index自动收集统计信息的意思了。在12c之前的版本，DBA是需要及时（数据插入之后）手工去收集Statistics，否则可能会在后面的使用中导致不正确的执行计划的出现。

 

 

 

隐藏参数_optimizer_gather_stats_on_load(enable/disable online statistics gathering，默认为TRUE)控制该Online Statistics Gathering for Bulk-Load特性是否打开，默认是打开的。

除了设置_optimizer_gather_stats_on_load=false之外还可以通过NO_GATHER_OPTIMIZER_STATISTICS(QKSFM_DBMS_STATS)的HINT来避免使用Online Statistics Gathering特性。 与之相对的是 GATHER_OPTIMIZER_STATISTICS。

 

测试1： Create table AS select 耗时上启用Online Statistics Gathering大约增加15%

 

 

SQL> create table online_gather as select rownum t1, 'maclean' t2 from dual connect by level<=900000; 

表已创建。 

已用时间:  00: 00: 01.09 

SQL> select num_rows,blocks from dba_tables where table_name='ONLINE_GATHER';

  NUM_ROWS     BLOCKS
---------- ----------
    900000       2282

已用时间:  00: 00: 00.17

SQL> alter session set "_optimizer_gather_stats_on_load"=false;

会话已更改。

已用时间:  00: 00: 00.00

SQL> create table online_gather2 as select rownum t1, 'maclean' t2 from dual connect by level<=900000; 

表已创建。 

已用时间:  00: 00: 00.93 

SQL> select num_rows,blocks from dba_tables where table_name='ONLINE_GATHER2';

  NUM_ROWS     BLOCKS
---------- ----------

已用时间:  00: 00: 00.09

 

 

 

 

2、测试 bulk load insert

 

 

conn malcean/maclean

SQL> create table online_load (t1 int, t2 varchar2(200));

表已创建。

SQL> insert into online_load select rownum t1, 'maclean' t2 from dual connect by level<=900000;

已创建 900000 行。

SQL> commit;

提交完成。

SQL> select num_rows,blocks from dba_tables where table_name='ONLINE_LOAD';

NUM_ROWS BLOCKS
---------- ----------

// 注意仅有INSERT APPEND的情况下才会触发Online Statistics Gathering

SQL> create table online_load1 (t1 int, t2 varchar2(200));

表已创建。

SQL> insert /*+ append */ into online_load1 select rownum t1, 'maclean' t2 from dual connect by level<=900000; 已创建 900000 行。 SQL> commit;

提交完成。

SQL> select num_rows,blocks from dba_tables where table_name='ONLINE_LOAD1';

  NUM_ROWS     BLOCKS
---------- ----------
    900000       2282