Oracle 19c新功能实时统计
发布于：2021-02-21 00:00:51
在数据仓库环境中，我们经常遇到表被截断，新数据（通常是数百万行）被删除的情况上膛了，但是当报表运行在那些带有新加载数据的表上时，除非我们去收集新的统计数据，否则优化器可能会选择一个次优的计划。
因此，为了解决这个问题，Oracle Database 12c引入了在线统计数据收集，但这仅适用于那些通过CREATE TABLE AS SELECT语句以及使用APPEND提示的直接路径插入加载数据的表。
Oracle Database 19c引入了实时统计，它将在线统计数据收集扩展到也包括常规的DML语句。
统计信息通常由在数据库维护窗口中运行的自动统计信息收集作业收集，但这只是每天一次。
但是对于易失性表，在DBMS_STATS作业执行之间，统计信息可能会过时，因此新的oracle19c实时统计信息功能可以帮助优化器为此类易失性表生成更优化的计划。
大容量加载操作将收集所有必要的统计信息（在oracle19c之前的行为），但是实时统计信息将增强而不是取代传统的统计信息。
我们有一个名为MYOBJECTS c的表，当前有47974行。
SQL> select distinct object_type from myobjects_19c where owner='SYS';
OBJECT_TYPE
-----------------------
INDEX
CLUSTER
TABLE PARTITION
...
...
VIEW
JAVA RESOURCE
26 rows selected.
SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8rbtnvw2uw67f, child number 0
-------------------------------------
select distinct object_type from myobjects_19c where owner='SYS'
Plan hash value: 1625058500
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 258 (100)| |
| 1 | HASH UNIQUE | | 26 | 364 | 258 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| MYOBJECTS_19C | 47974 | 655K| 257 (1)| 00:00:01 |
------------------------------------------------------------------------------------
我们现在在表中插入一些额外的行–基本上将表中的行数增加一倍。
在早期版本中，优化器现在有可能选择次优计划，因为它“不知道”表上发生了一些DML活动，现在表中的行数增加了两倍。
但是现在在oracle19c中，我们可以看到，作为INSERT语句的一部分，优化器的统计信息收集操作也被执行了。
SQL> insert into myobjects_19c
select * from myobjects;
47974 rows created.
SQL> commit;
Commit complete.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ahudb149n8f2f, child number 0
-------------------------------------
insert into myobjects_19c select * from myobjects
Plan hash value: 3078646338
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 273 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | MYOBJECTS_19C | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 47974 | 11M| 273 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL | MYOBJECTS | 47974 | 11M| 273 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: statistics for conventional DML
当SQL语句发生硬解析时，我们可以看到优化器检测到表中添加了其他行。
这也在注释部分指出：使用的动态统计信息：常规DML的统计信息。
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> select distinct object_type from myobjects_19c where owner='SYS';
OBJECT_TYPE
-----------------------
INDEX
CLUSTER
TABLE PARTITION
...
...
VIEW
JAVA RESOURCE
26 rows selected.
SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8rbtnvw2uw67f, child number 0
-------------------------------------
select distinct object_type from myobjects_19c where owner='SYS'
Plan hash value: 1625058500
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 624 (100)| |
| 1 | HASH UNIQUE | | 26 | 2054 | 624 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| MYOBJECTS_19C | 95948 | 7402K| 621 (1)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
Note
-----
- dynamic statistics used: statistics for conventional DML
实时统计信息以表示STATS_ON_CONVENTIONAL_DML在NOTES列中的* _TAB_STATISTICS和* _TAB_COL_STATISTICS意见。
SQL> SELECT NUM_ROWS, BLOCKS, NOTES FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'MYOBJECTS_19C' ; NUM_ROWS BLOCKS NOTES ---------- ---------- -------------------------------------------------- 47974 938 95948 2284 STATS_ON_CONVENTIONAL_DML
作者介绍
