签到成功

知道了

CNDBA社区CNDBA社区

Oracle 19c 新特性 批量加载的在线统计信息收集(Online Statistics Gathering for Bulk-Load) 说明

2021-01-26 13:33 244 0 原创 Oracle 19c
作者: Dave

1 批量加载的在线统计信息收集概述

在Oracle 12c 之前的版本存在2种统计信息:

https://www.cndba.cn/dave/article/4369

Oracle Statistic 统计信息 小结
http://blog.csdn.net/tianlesoftware/archive/2009/10/15/4668723.aspx

Oracle 性能优化 之 扩展统计信息
https://www.cndba.cn/dave/article/4367

统计信息是生成执行计划的重要的依据。 在12c 之前的统计信息只能通过 Scheduler Job或者手工来收集。 对于数据量变化较大的表就可能存在统计信息陈旧的问题,从而到时相应的SQL 性能出现急剧下降。 因此Oracle 在12c 中引入了Online Statistics Gathering for Bulk-Load 特性,针对批量数据加载的在线的统计信息收集。

https://www.cndba.cn/dave/article/4369

官方文档上对Online Statistics Gathering的说明如下:

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-concepts.html#GUID-FEEF0915-FE19-4C10-BED0-EB8ED90529A4

https://www.cndba.cn/dave/article/4369
https://www.cndba.cn/dave/article/4369

在12c 以后的版本中,在进行批量数据加载操作时,比如使用 direct path insert的INSERT INTO … SELECT 和 CREATE TABLE AS SELECT,Oracle 会自动对操作对象收集统计信息。

默认情况下,parallel insert使用的是direct path insert, 我们也可以通过/+APPEND/ hint 来强制使用direct path insert。

有两种方法可以验证进行了online statistics gathering:

1)执行计划显示”OPTIMIZER STATISTICS GATHERING”操作。
2)”user_tab_col_statistics” 视图的NOTE 列会显示:STATS_ON_LOAD

Online Statistics Gathering for Bulk-Load 特性由隐藏参数_optimizer_gather_stats_on_load 控制,默认启用:

https://www.cndba.cn/dave/article/4369
https://www.cndba.cn/dave/article/4369
https://www.cndba.cn/dave/article/4369

[dave@www.cndba.cn ~]$ sql / as sysdba

SQLcl: Release 19.1 Production on Tue Jan 26 12:41:53 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0


SQL> 
SQL> select name,value, description from all_parameters where name='_optimizer_gather_stats_on_load';

NAME                                VALUE           DESCRIPTION
----------------------------------- --------------- --------------------------------------------------
_optimizer_gather_stats_on_load     TRUE            enable/disable online statistics gathering

SQL>

我们可以通过修改该参数来禁用该特性,也可以通过HINT /+NO_GATHER_OPTIMIZER_STATISTICS/来禁止该特性,如下:

https://www.cndba.cn/dave/article/4369

CREATE TABLE CNDBA AS
  SELECT /*+NO_GATHER_OPTIMIZER_STATISTICS*/ * FROM DAVE;
From: Online Statistics Gathering for Bulk Loads (Doc ID 2019634.1)

另外需要注意一点就是Online Statistics Gathering 操作不会自动创建直方图, 如果需要直方图,需要手工调用DBMS_STATS.GATHER_TABLE_STATS 来创建。 比如:

EXEC DBMS_STATS.GATHER_TABLE_STATS(user, ‘CNDBA’, options=>’GATHER AUTO’);https://www.cndba.cn/dave/article/4369

同时在以下情况,Online Statistics Gathering 也不会自动收集:

1)The object contains data. Bulk loads only gather online statistics automatically when the object is empty.
2)It is in an Oracle-owned schema such as SYS.
3)It is one of the following types of tables: nested table, index-organized table (IOT), external table, or global temporary table defined as ON COMMIT DELETE ROWS.
4)It has a PUBLISH preference set to FALSE.
5)Its statistics are locked.
6)It is loaded using a multitable INSERT statement.

简单的说使用Online Statistics Gathering 必须使用direct path insert到一个空表/空分区,并且不支持SYS用户的对象。

2 操作示例

2.1 以CREATE TABLE…AS SELECT..方式进行批量加载

[dave@www.cndba.cn ~]$ sql dave/dave@dave

SQLcl: Release 19.1 Production on Tue Jan 26 13:09:42 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Tue Jan 26 2021 13:09:43 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0


SQL>create table cndba as select * from dba_objects;

SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

Session altered.

SQL> col table_name for a15

SQL> select TABLE_NAME,LAST_ANALYZED,NOTES from DBA_TAB_STATISTICS where owner='DAVE' and table_name='CNDBA';

TABLE_NAME      LAST_ANALYZED       NOTES
--------------- ------------------- -------------------------
CNDBA           2021/01/26 13:12:21


SQL> select TABLE_NAME,COLUMN_NAME,NOTES,HISTOGRAM,LAST_ANALYZED from dba_tab_col_statistics where owner='DAVE' and table_name='CNDBA';

TABLE_NAME      COLUMN_NAME     NOTES                HISTOGRAM       LAST_ANALYZED
--------------- --------------- -------------------- --------------- -------------------
CNDBA           OWNER           STATS_ON_LOAD        NONE            2021/01/26 13:12:21
CNDBA           OBJECT_NAME     STATS_ON_LOAD        NONE            2021/01/26 13:12:21
CNDBA           SUBOBJECT_NAME  STATS_ON_LOAD        NONE            2021/01/26 13:12:21
CNDBA           OBJECT_ID       STATS_ON_LOAD        NONE            2021/01/26 13:12:21
CNDBA           DATA_OBJECT_ID  STATS_ON_LOAD        NONE            2021/01/26 13:12:21
……

注意这里NOTES 字段为STATS_ON_LOAD。https://www.cndba.cn/dave/article/4369

2.2 以INSERT INTO … SELECT..方式进行批量加载

[dave@www.cndba.cn ~]$ sql dave/dave@dave

SQLcl: Release 19.1 Production on Tue Jan 26 13:09:42 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Tue Jan 26 2021 13:09:43 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0


SQL> create table dave as select * from dba_objects where 1=2;

Table created.

SQL> select table_name,last_analyzed,notes from dba_tab_statistics where owner='DAVE' and table_name='DAVE';

TABLE_NAME      LAST_ANALYZED                   NOTES
--------------- ------------------------------- --------------------
DAVE

SQL> insert into dave select * from dba_objects;

72625 rows created.

SQL> commit;

Commit complete.

--没有走direct path insert,因此没有统计信息被收集
SQL> select table_name,last_analyzed,notes from dba_tab_statistics where owner='DAVE' and table_name='DAVE';

TABLE_NAME      LAST_ANALYZED                   NOTES
--------------- ------------------------------- --------------------
DAVE

--INSERT INTO ... SELECT 插入到一个空表且使用 direct path insert的时候统计信息才会被收集
SQL> create table ustc as select * from dba_objects where 1=2;

Table created.

SQL> insert /*+append*/ into ustc select * from dba_objects;

72626 rows created.

SQL> commit;

Commit complete.

SQL> select table_name,last_analyzed,notes from dba_tab_statistics where owner='DAVE' and table_name='USTC';

TABLE_NAME      LAST_ANALYZED       NOTES
--------------- ------------------- --------------------
USTC            2021/01/26 13:26:28

SQL>

2.3 禁用该特性

如果不想在批量加载的时候收集统计信息,可以使用hint:NO_GATHER_OPTIMIZER_STATISTICS。

[dave@www.cndba.cn ~]$ sql dave/dave@dave

SQLcl: Release 19.1 Production on Tue Jan 26 13:09:42 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Tue Jan 26 2021 13:09:43 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0



SQL> create table hefei as select /*+NO_GATHER_OPTIMIZER_STATISTICS */ * from dba_objects;

Table created.

SQL> select TABLE_NAME,LAST_ANALYZED,NOTES from DBA_TAB_STATISTICS where owner='DAVE' and table_name='HEFEI';

TABLE_NAME      LAST_ANALYZED                   NOTES
--------------- ------------------------------- --------------------
HEFEI

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

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

Dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 1832
    原创
  • 2
    翻译
  • 417
    转载
  • 119
    评论
  • 访问:3164481次
  • 积分:2888
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by ZhiXinTech · 皖ICP备19020168号·

        QQ交流群