CNDBA社区
Oracle 19c 新特性 批量加载的在线统计信息收集(Online Statistics Gathering for Bulk-Load) 说明
作者：
dave
1 批量加载的在线统计信息收集概述
在Oracle 12c 之前的版本存在2种统计信息：
Oracle Statistic 统计信息 小结
http://blog.csdn.net/tianlesoftware/archive/2009/10/15/4668723.aspxOracle 性能优化 之 扩展统计信息
https://www.cndba.cn/dave/article/4367
统计信息是生成执行计划的重要的依据。 在12c 之前的统计信息只能通过 Scheduler Job或者手工来收集。 对于数据量变化较大的表就可能存在统计信息陈旧的问题，从而到时相应的SQL 性能出现急剧下降。 因此Oracle 在12c 中引入了Online Statistics Gathering for Bulk-Load 特性，针对批量数据加载的在线的统计信息收集。
官方文档上对Online Statistics Gathering的说明如下：
在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 控制，默认启用：
[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/来禁止该特性，如下：
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’);
同时在以下情况，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。
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
版权声明：本文为博主原创文章，未经博主允许不得转载。
