Oracle 19c 新特性 批量加载的在线统计信息收集(Online Statistics Gathering for Bulk |
您所在的位置：网站首页 › 看的高级替换词 › Oracle 19c 新特性 批量加载的在线统计信息收集(Online Statistics Gathering for Bulk |
|
1 批量加载的在线统计信息收集概述
在Oracle 12c 之前的版本存在2种统计信息：https://www.cndba.cn/dave/article/4369https://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 特性，针对批量数据加载的在线的统计信息收集。 官方文档上对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：https://www.cndba.cn/dave/article/4369 1）执行计划显示”OPTIMIZER STATISTICS GATHERING”操作。2）”user_tab_col_statistics” 视图的NOTE 列会显示：STATS_ON_LOADhttps://www.cndba.cn/dave/article/4369 Online Statistics Gathering for Bulk-Load 特性由隐藏参数_optimizer_gather_stats_on_load 控制，默认启用：https://www.cndba.cn/dave/article/4369 [[email protected] ~]$ 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 来创建。 比如：https://www.cndba.cn/dave/article/4369 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用户的对象。https://www.cndba.cn/dave/article/4369 2 操作示例 2.1 以CREATE TABLE…AS SELECT..方式进行批量加载 [[email protected] ~]$ 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..方式进行批量加载 [[email protected] ~]$ 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。 [[email protected] ~]$ 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版权声明：本文为博主原创文章，未经博主允许不得转载。 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |