签到成功

知道了

CNDBA社区CNDBA社区

oracle12c-Online Statistics Gathering for Bulk Loads

2017-08-01 14:45 2225 0 原创 Oracle 12C
作者： Expect-乐
本文链接：http://www.cndba.cn/Expect-le/article/2036

1  说明

With online statistics gathering, statistics are automatically created as part of a bulk load operation such as a CREATE TABLE AS SELECT operation or an INSERT INTO ... SELECT operation on an empty table. Online statistic gathering eliminates the necessity to manually gather statistics after a bulk data load has occurred. It behaves in a similar manner to the statistics gathering done during a CREATE INDEX or REBUILD INDEX command.

在线收集统计信息会在如CREATE TABLE AS SELECT和对空表执行NSERT INTO ... SELECT操作后会自动收集统计信息。就不用手动在收集统计信息了。这种方式与 CREATE INDEX或REBUILD INDEX命令时所做的统计数据类似。

从oracle 12c开始支持CREATE TABLE AS SELECT和对空表执行NSERT INTO ... SELECT在线收集统计信息。

http://www.cndba.cn/Expect-le/article/2036

注意：在线收集统计信息是无法收集相关索引和创建直方图的。需要手动处理。http://www.cndba.cn/Expect-le/article/2036

2  实验

2.1   CREATE TABLE ... AS SELECT (CTAS)

SQL> create table cndba as select * from dba_tables;

Table created.

2.1.1  查看user_tables中的num_rows

SQL> SELECT table_name, num_rows, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')

  FROM user_tables

 WHERE table_name = 'CNDBA';  2    3  


TABLE_NAME   NUM_ROWS TO_CHAR(LAST_ANALYZ
---------- ---------- ---------------------------------------------
CNDBA	 2125 行数 2017-08-01 13:58:54 --收集统计信息时间

2.2   INSERT INTO ... SELECT

http://www.cndba.cn/Expect-le/article/2036

SQL> truncate table cndba;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO cndba select * from dba_tables;

2126 rows created.

2.2.1  查看统计信息收集时间

SQL> SELECT table_name, num_rows, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')

  FROM user_tables

 WHERE table_name = 'CNDBA';  2    3  

TABLE_NAME   NUM_ROWS TO_CHAR(LAST_ANALYZ
---------- ---------- -------------------
CNDBA	 2126 2017-08-01 14:03:23

2126：多了一个因为刚刚建了这个表

注意：是truncate表，而不是delete。在线收集统计信息，对象所在的段必须是空的。而delete无法做到。

测试一下：

SQL> delete from cndba;

2126 rows deleted.

SQL> commit;

Commit complete.

SQL> INSERT /*+ APPEND */ INTO cndba select * from dba_tables where rownum <=1000;

1000 rows created.   --只插入1000行，为了对比

SQL> commit;

Commit complete.

SQL> SELECT table_name, num_rows, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')

  FROM user_tables

 WHERE table_name = 'CNDBA';  2    3  

TABLE_NAME   NUM_ROWS TO_CHAR(LAST_ANALYZ

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

CNDBA	 2126 2017-08-01 14:03:23

可以看到统计信息里存的还是2126之前的数据。

2.3   使用HINT

通过使用NO_GATHER_OPTIMIZER_STATISTICS也可以不适用在线收集统计信息http://www.cndba.cn/Expect-le/article/2036

--当前统计信息

SQL> SELECT table_name, num_rows, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')

  FROM user_tables

 WHERE table_name = 'CNDBA';  2    3  

TABLE_NAME   NUM_ROWS TO_CHAR(LAST_ANALYZ

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

CNDBA	  199 2017-08-01 14:13:06

--truncat表

http://www.cndba.cn/Expect-le/article/2036

SQL> truncate table cndba;

Table truncated.

--插入200条数据

SQL> INSERT /*+ APPEND */ INTO cndba select /*+ NO_GATHER_OPTIMIZER_STATISTICS */ * from dba_tables where rownum <=200;

200 rows created.

SQL> SELECT table_name, num_rows, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')

  FROM user_tables

 WHERE table_name = 'CNDBA';  2    3  

TABLE_NAME   NUM_ROWS TO_CHAR(LAST_ANALYZ
---------- ---------- -------------------
CNDBA	  199 	2017-08-01 14:13:06  --数据条数和统计信息收集时间都没有变。

2.4   分区表

对分区表进行批量加载的操作，只会收集全局的统计信息，而不会收集分区上的收集信息。http://www.cndba.cn/Expect-le/article/2036

注意：是对分区表进行操作，而不是某个分区。下面会说明。http://www.cndba.cn/Expect-le/article/2036

2.4.1  插入分区表

--创建分区表

SQL> CREATE TABLE cndba_part

(id            NUMBER,

 created_date  DATE)

PARTITION BY RANGE (created_date)

(PARTITION tab1_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users,

 PARTITION tab1_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) TABLESPACE users);

Table created.

--插入数据

SQL> INSERT /*+ APPEND */ INTO cndba_part

SELECT level,

       TO_DATE('01/01/2016', 'DD/MM/YYYY')

FROM   dual

CONNECT BY level <= 100;

100 rows created.


SQL> COMMIT;

Commit complete.

--查看全局统计信息以及数据

SQL> SELECT table_name, num_rows, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')

  FROM user_tables

 WHERE table_name = 'CNDBA_PART';  2    3  


TABLE_NAME   NUM_ROWS TO_CHAR(LAST_ANALYZ
---------- ---------- -------------------
CNDBA_PART	  100 2017-08-01 14:20:58

--查看分区上的统计信息以及数据

SQL> col PARTITION_NAME for a20

SQL> SELECT table_name,

       partition_name,

       num_rows,

       to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss')

  FROM user_tab_partitions

 WHERE table_name = 'CNDBA_PART'

 ORDER BY partition_name;

  2    3    4    5    6    7  

TABLE_NAME PARTITION_NAME	  NUM_ROWS TO_CHAR(LAST_ANALYZ
---------- -------------------- ---------- -------------------
CNDBA_PART TAB1_2015   

CNDBA_PART TAB1_2016

没有相关统计信息数据。

2.4.2  插入某个分区

--清除统计信息

EXEC DBMS_STATS.delete_table_stats(USER, 'CNDBA_PART');

--truncate表

TRUNCATE TABLE CNDBA_PART;

--向TAB1_2016插入数据

INSERT /*+ APPEND */ INTO CNDBA_PART PARTITION (TAB1_2016)

SELECT level,

       TO_DATE('01/01/2016', 'DD/MM/YYYY')

FROM   dual

CONNECT BY level <= 100;

100 rows created.

--查看分区表CNDBA_PART统计信息

SQL> SELECT table_name, num_rows, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')

  FROM user_tables

 WHERE table_name = 'CNDBA_PART';  2    3  

TABLE_NAME   NUM_ROWS TO_CHAR(LAST_ANALYZ
---------- ---------- -------------------
CNDBA_PART

可以看到没有收集统计信息

 

--查看分区TAB1_2016统计信息

SQL> SELECT table_name,

       partition_name,

       num_rows,

       to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss')

  FROM user_tab_partitions

 WHERE table_name = 'CNDBA_PART'

 ORDER BY partition_name;  2    3    4    5    6    7  

TABLE_NAME PARTITION_NAME	  NUM_ROWS TO_CHAR(LAST_ANALYZ
---------- -------------------- ---------- -------------------
CNDBA_PART TAB1_2015

CNDBA_PART TAB1_2016	       100 2017-08-01 14:33:16

可以看到，只有TAB1_2016被收集统计信息。

3  总结

在线收集统计信息限制：

l  Index statistics or histograms. If those are required the must be gathered in a separate operation. The following call will gather missing statistics, but will not re-gather table or column statistics unless the existing statistics are already stale.

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TAB1', options => 'GATHER AUTO');

l  Non-empty segments, as described above.

l  Tables in built-in schemas. Only those in user-defined schemas.

l  Nested, index-organized or external tables.

l  Global temporary tables using the ON COMMIT DELETE ROWS clause.

http://www.cndba.cn/Expect-le/article/2036

l  Table with virtual columns.

l  Tables if the PUBLISH preference is set to FALSE for DBMS_STATS.

l  Tables with locked statistics.

l  Partitioned tables using incremental statistics, where the insert is not explicitly referencing a partition using the PARTITION clause.http://www.cndba.cn/Expect-le/article/2036

l  Tables loaded using multitable inserts.

参考文章：http://www.cndba.cn/Expect-le/article/2036

https://oracle-base.com/articles/12c/online-statistics-gathering-for-bulk-loads-12cr1

http://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#GUID-2712B321-9BC2-4B64-93BA-C93F23907033

 

 

版权声明：本文为博主原创文章，未经博主允许不得转载。

Online Statistics Gathering

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
Expect-乐

Expect-乐

关注

Without the continuous bitter cold, there can be no fragrant plum blossom

  • 336
    原创
  • 6
    翻译
  • 100
    转载
  • 41
    评论
  • 访问：1571348次
  • 积分：1957
  • 等级：核心会员
  • 排名：第4名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ