Oracle 12c - Online Statistics Gathering for Bulk-Load 資料批量新增的在線資訊收集

在12C 提出來的新功能 資料批量新增自動收集資訊 Online Statistics Gathering for Bulk-Load
通過這個功能, 在進行某項操作例如：create table as select 或是 insert into .. select * from table，統計資訊會自動的收集


這個功能可以節省一次手動執行analyze的步驟，也可能的減少前面幾次 full table scan，對於Table經常性大量新增資料的資料庫是有不錯的幫助

在對Table進行 Online Statistics Gathering for Bulk-Load 時，Oacle 是不會自動對 index 收集資訊以及直方圖，要是有這個需求，必須還要再進行 DBMS_STATS.GATHER_TABLE_STATS 一次





這個功能還是有所限制的，根據官方的文件說明限制有


It is in an Oracle-owned schema such as SYS.
It is a nested table.
It is an index-organized table (IOT).
It is an external table.
It is a global temporary table defined as ON COMMIT DELETE ROWS.
It has virtual columns.
It has a PUBLISH preference set to FALSE.
Its statistics are locked.
It is partitioned, INCREMENTAL is set to true, and extended syntax is not used.
It is loaded using a multitable insert statement.


PS:12C預設是把這個功能開啟的


以下是做的簡單測試

SQL>  create table test_online as select * from dba_objects ;

已建立表格.

目前歷時: 00:00:01.68

SQL> select num_rows,blocks,last_analyzed 
 2 from dba_tables where table_name ='TEST_ONLINE';


  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- --------------
     90855       1560 20-8月 -14

目前歷時: 00:00:00.12



SQL> alter session set "_optimizer_gather_stats_on_load"=false;

已更改階段作業.

目前歷時: 00:00:00.00

SQL> create table test_online2 as select * from dba_objects ;

已建立表格.

目前歷時: 00:00:01.17

SQL> select num_rows,blocks,last_analyzed 
 2 from dba_tables where table_name ='TEST_ONLINE2';

  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- --------------


目前歷時: 00:00:00.28
SQL>


這邊可以看到開啟參數後 create table 速度降低, 為了能自動收集資訊看來還是有所犧牲的  



在官方的說明文件裡還有提到， insert 要使用Online Statistics Gathering for Bulk-Load 時 必須使用加上 /*+APPEND */ hint.

以下是簡單的測試


SQL> create table test_online3 (t1 number, t2 varchar2(200));

已建立表格.

目前歷時: 00:00:00.04
SQL> insert  into test_online3 select 11,'123' 
 2 from dual connect by level<=9000;


已建立 9000 個資料列.

目前歷時: 00:00:00.11
SQL> select num_rows,blocks,last_analyzed 
 2 from dba_tables where table_name ='TEST_ONLINE3';

  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -------------------


目前歷時: 00:00:00.03

SQL>


SQL> create table test_online4 (t1 number, t2 varchar2(200));

已建立表格.

目前歷時: 00:00:00.01
SQL> insert /*+ append */  into test_online4 select 11,'123' 
 2 from dual connect by level<=9000;

已建立 9000 個資料列.

目前歷時: 00:00:00.11
SQL> select num_rows,blocks,last_analyzed 
 2 from dba_tables where table_name ='TEST_ONLINE4';

  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -------------------
      9000        100 2014-08-20 04:12:23

目前歷時: 00:00:00.04

SQL>