[20130918]12C表统计信息的在线收集.txt

在12c以前，如果使用ctas建立表，并不会自动收集表的统计信息，12c开始CTAS和IAS操作会收集统计信息。但是不收集直方图信息。
以及索引统计。

摘要：
twp-bp-for-stats-gather-12c-1967354.pdf
Best Practices for Gathering Optimizer Statistics with Oracle Database 12c

P16-17页：

Online statistics gathering

    In Oracle Database 12c, online statistics gathering "piggybacks" statistics gather as part of a direct-path data
loading operation such as, create table as select (CTAS) and insert as select (IAS) operations.  Gathering statistics as
part of the data loading operation, means no additional full data scan is required to have statistics available
immediately after the data is loaded.  

    Online statistics gathering does not gather histograms or index statistics, as these types of statistics require
additional data scans, which could have a large impact on the performance of the data load. To gather the necessary
histogram and index statistics without re-gathering the base column statistics use the DBMS_STATS.GATHER_TABLE_STATS
procedure with the new options parameter set to GATHER AUTO.

    The notes column "HISTOGRAM_ONLY" indicates that histograms were gathered without re- occurred: check the
execution plan to see if the new row source OPTIMIZER STATISTICS GATHERING appears in the plan or look in the new notes
column of the USER_TAB_COL_STATISTICS table for the status STATS_ON_LOAD.  gathering basic column statistics. There are
two ways to confirm online statistics gathering has occurred: check the execution plan to see if the new row source
OPTIMIZER STATISTICS GATHERING appears in the plan or look in the new notes column of the USER_TAB_COL_STATISTICS
table for the status STATS_ON_LOAD.

    Since online statistics gathering was designed to have a minimal impact on the performance of a direct path load
operation it can only occur when data is being loaded into an empty object. To ensure online statistics gathering kicks
in when loading into a new partition of an existing table, use extended syntax to specify the partition explicitly. In
this case partition level statistics will be created but global level (table level) statistics will not be updated. If
incremental statistics have been enabled on the partitioned table a synopsis will be created as part of the data load
operation.

自己做一些测试：
1.CTAS测试：

@ver

BANNER                                                                               CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t as select * from emp ;

Table created.

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  grp1wk6pwfkvr, child number 0

-------------------------------------

create table t as se

Plan hash value: 2748781111

--------------------------------------------------------------------------------------------------

| Id  | Operation                        | Name | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |

--------------------------------------------------------------------------------------------------

|   0 | CREATE TABLE STATEMENT           |      |        |     4 (100)|       |       |          |

|   1 |  LOAD AS SELECT                  |      |        |            |  1036K|  1036K| 1036K (0)|

|   2 |   OPTIMIZER STATISTICS GATHERING |      |     14 |     3   (0)|       |       |          |

|   3 |    TABLE ACCESS FULL             | EMP  |     14 |     3   (0)|       |       |          |

--------------------------------------------------------------------------------------------------

--可以发现执行计划存在OPTIMIZER STATISTICS GATHERING。

SCOTT@test01p> select table_name, num_rows, last_analyzed  from user_tables where table_name='T';

TABLE_NAME   NUM_ROWS LAST_ANALYZED

---------- ---------- -------------------

T                  14 2013-09-18 22:27:31

--可以发现收集了表的统计信息。

SCOTT@test01p> select column_name, num_distinct, histogram,notes  from user_tab_col_statistics   where table_name = 'T' ;

COLUMN_NAME          NUM_DISTINCT HISTOGRAM       NOTES

-------------------- ------------ --------------- -----------------------------------------

EMPNO                          14 NONE            STATS_ON_LOAD

ENAME                          14 NONE            STATS_ON_LOAD

JOB                             5 NONE            STATS_ON_LOAD

MGR                             6 NONE            STATS_ON_LOAD

HIREDATE                       13 NONE            STATS_ON_LOAD

SAL                            12 NONE            STATS_ON_LOAD

COMM                            4 NONE            STATS_ON_LOAD

DEPTNO                          3 NONE            STATS_ON_LOAD

8 rows selected.
--并没有建立直方图。

2.IAS测试：

SCOTT@test01p> drop table t purge ;

Table dropped.

SCOTT@test01p> create table t as select * from emp where 1=2;

Table created.

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  9r2x17fxjgutq, child number 0

-------------------------------------

create table t as se

Plan hash value: 2561008478

-----------------------------------------------------------------------

| Id  | Operation                        | Name | E-Rows | Cost (%CPU)|

-----------------------------------------------------------------------

|   0 | CREATE TABLE STATEMENT           |      |        |     1 (100)|

|   1 |  LOAD AS SELECT                  |      |        |            |

|   2 |   OPTIMIZER STATISTICS GATHERING |      |     14 |     3   (0)|

|*  3 |    FILTER                        |      |        |            |

|   4 |     TABLE ACCESS FULL            | EMP  |     14 |     3   (0)|

-----------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(NULL IS NOT NULL)

SCOTT@test01p> select table_name, num_rows, last_analyzed  from user_tables where table_name='T';

TABLE_NAME   NUM_ROWS LAST_ANALYZED

---------- ---------- -------------------

T                   0 2013-09-18 22:32:48

--依旧分析了表.

SCOTT@test01p> insert into t select * from emp where rownum

5 rows created.

SCOTT@test01p> commit ;

Commit complete.

SCOTT@test01p> select table_name, num_rows, last_analyzed  from user_tables where table_name='T';

TABLE_NAME   NUM_ROWS LAST_ANALYZED

---------- ---------- -------------------

T                   0 2013-09-18 22:32:48

--插入5行记录，可以发现没有 direct-path data loading operation，并不会分析表。

SCOTT@test01p> insert /*+ append */ into t select * from emp  ;

14 rows created.

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  99wpy6dhfk4j7, child number 0

-------------------------------------

insert /*+ append */ into t select * from emp

Plan hash value: 2748781111

-----------------------------------------------------------------------

| Id  | Operation                        | Name | E-Rows | Cost (%CPU)|

-----------------------------------------------------------------------

|   0 | INSERT STATEMENT                 |      |        |     3 (100)|

|   1 |  LOAD AS SELECT                  |      |        |            |

|   2 |   OPTIMIZER STATISTICS GATHERING |      |     14 |     3   (0)|

|   3 |    TABLE ACCESS FULL             | EMP  |     14 |     3   (0)|

-----------------------------------------------------------------------

SCOTT@test01p> commit ;

Commit complete.

SCOTT@test01p> select table_name, num_rows, last_analyzed  from user_tables where table_name='T';

TABLE_NAME   NUM_ROWS LAST_ANALYZED

---------- ---------- -------------------

T                   0  2013-09-18 22:32:48

--?? 奇怪这样并没有收集表的统计信息。

3.重新做IAS测试：

SCOTT@test01p> drop table t purge ;

Table dropped.

SCOTT@test01p> create table t as select * from emp where 1=2;

Table created.

SCOTT@test01p> insert /*+ append */ into t select * from emp  ;

14 rows created.

SCOTT@test01p> commit;

Commit complete.

SCOTT@test01p> select table_name, num_rows, last_analyzed  from user_tables where table_name='T';

TABLE_NAME   NUM_ROWS LAST_ANALYZED

---------- ---------- -------------------

T                  14 2013-09-18 22:41:44

--看来仅仅在空表的情况下才会统计，这样讲也有道理。因为它仅仅分析加载的部分信息，如果前面有插入，统计信息一定不准确。


4.如果CTAS不需要收集统计信息呢？可以使用提示NO_GATHER_OPTIMIZER_STATISTICS。再重复测试：

SCOTT@test01p> create table t as select /*+ NO_GATHER_OPTIMIZER_STATISTICS */ * from emp ;

Table created.

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  60f2a25kbbyz6, child number 0

-------------------------------------

create table t as se

--?为什么显示不全呢?

Plan hash value: 2748781111

----------------------------------------------------------------------------------------

| Id  | Operation              | Name | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |

----------------------------------------------------------------------------------------

|   0 | CREATE TABLE STATEMENT |      |        |     4 (100)|       |       |          |

|   1 |  LOAD AS SELECT        |      |        |            |  1036K|  1036K| 1036K (0)|

|   2 |   TABLE ACCESS FULL    | EMP  |     14 |     3   (0)|       |       |          |

----------------------------------------------------------------------------------------

SCOTT@test01p> select table_name, num_rows, last_analyzed  from user_tables where table_name='T';

TABLE_NAME   NUM_ROWS LAST_ANALYZED

---------- ---------- -------------------

T

SCOTT@test01p> select column_name, num_distinct, histogram,notes  from user_tab_col_statistics   where table_name = 'T' ;

no rows selected