在数据仓库环境中，我们经常会遇到表被truncate并且新数据（通常为千万行）插入的情况。如果针对包含新插入的数据的表运行报表，除非我们去收集新的统计信息，否则优化器很难选择正确的执行计划。因此，为了解决此问题，Oracle数据库12c引入了Online统计信息收集，但是这仅适用于通过CREATE TABLE AS SELECT语句以及使用APPEND hint进行直接路径插入的表。而为了进一步增强功能，Oracle Database 19c引入了实时统计信息，它扩展了在线统计信息的收集范围，还包括常规的DML语句。
通常，统计信息是由自动统计信息收集作业收集的，该作业在数据库维护窗口内运行，一般是一天一次。但是对于刚刚提到的场景，统计信息在DBMS_STATS作业执行之前就已经过期陈旧，因此，Oracle 19c新功能实时统计信息可以帮助应用程序不受此问题的困扰。
批量插入的操作将收集必要的统计信息(Oracle 19c之前的行为)，而实时统计信息会增加而不是取代传统统计信息。下面我们来测试一下这个新功能
create table t1 as select * from dba_objects;
select distinct object_type from t1 where owner='SYS';
select * from table (dbms_xplan.display_cursor);
SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7k4h0792x1vdx, child number 0
-------------------------------------
select distinct object_type from t1 where owner='SYS'
Plan hash value: 2134347679
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 393 (100)| |
| 1 | HASH UNIQUE | | 45 | 675 | 393 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 2895 | 43425 | 392 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
19 rows selected.
现在，我们往表中插入一些数据，我们要让表中的行数加倍。在较早的Oracle版本中，优化器可能会选择错误的计划，因为它“不知道”表中发生了某些DML，而表中的行数却增加了2倍以上。但是现在在Oracle 19c中，我们可以看到，作为INSERT语句的一部分，还执行了OPTIMIZER STATISTICS GATHERING操作。
SQL> insert into t1 select * from dba_objects; 72375 rows created. SQL> commit; Commit complete. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID db5vba95ya3r4, child number 1 ------------------------------------- insert into t1 select * from dba_objects Plan hash value: 2114560210 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | 396 (100)| | | 1 | LOAD TABLE CONVENTIONAL | T1 | | | | | | 2 | OPTIMIZER STATISTICS GATHERING | | 66748 | 30M| 396 (3)| 00:00:01 | | 3 | VIEW | DBA_OBJECTS | 66748 | 30M| 396 (3)| 00:00:01 | | 4 | UNION-ALL | | | | | | | 5 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 26 | 0 (0)| | |* 6 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| | |* 7 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 40 | 3 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | |* 11 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 26 | 0 (0)| | |* 12 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| | |* 13 | FILTER | | | | | | |* 14 | HASH JOIN | | 72361 | 10M| 336 (2)| 00:00:01 | | 15 | TABLE ACCESS FULL | USER$ | 127 | 2286 | 4 (0)| 00:00:01 | |* 16 | HASH JOIN | | 72361 | 9610K| 332 (2)| 00:00:01 | | 17 | INDEX FULL SCAN | I_USER2 | 127 | 3175 | 1 (0)| 00:00:01 | |* 18 | TABLE ACCESS FULL | OBJ$ | 72361 | 7843K| 331 (2)| 00:00:01 | | 19 | NESTED LOOPS | | 1 | 32 | 4 (0)| 00:00:01 | | 20 | NESTED LOOPS | | 1 | 23 | 3 (0)| 00:00:01 | | 21 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 10 | 2 (0)| 00:00:01 | |* 22 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | |* 23 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 1 (0)| 00:00:01 | |* 24 | INDEX RANGE SCAN | I_OBJ1 | 1 | 9 | 1 (0)| 00:00:01 | |* 25 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 2 (0)| 00:00:01 | |* 26 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 | |* 27 | TABLE ACCESS BY INDEX ROWID | SEQ$ | 1 | 8 | 1 (0)| 00:00:01 | |* 28 | INDEX UNIQUE SCAN | I_SEQ1 | 1 | | 0 (0)| | |* 29 | TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 | |* 30 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1 (0)| 00:00:01 | |* 31 | TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 | |* 32 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1 (0)| 00:00:01 | | 33 | NESTED LOOPS SEMI | | 1 | 29 | 2 (0)| 00:00:01 | |* 34 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 | |* 35 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 | | 36 | NESTED LOOPS | | 1 | 36 | 3 (0)| 00:00:01 | | 37 | TABLE ACCESS FULL | LINK$ | 1 | 18 | 2 (0)| 00:00:01 | | 38 | TABLE ACCESS CLUSTER | USER$ | 1 | 18 | 1 (0)| 00:00:01 | |* 39 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| | -------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("S"."OBJ#"=:B1) 7 - filter("UE"."TYPE#"=:B1) 8 - access("UE"."USER#"=:B1) 10 - access("EO"."OBJ#"=:B1) 11 - filter(BITAND("S"."XPFLAGS",34368126976)=0) 12 - access("S"."OBJ#"=:B1) 13 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>2 AND "O"."TYPE#"<>6) OR ("O"."TYPE#"=1 AND IS NULL) OR ("O"."TYPE#"=2 AND =1) OR ("O"."TYPE#"=6 AND =1)) AND (BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR ("O"."TYPE#"<>88 AND IS NULL) OR ( IS NOT NULL AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))))) 14 - access("O"."SPARE3"="U"."USER#") 16 - access("O"."OWNER#"="U"."USER#") 18 - filter(("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."TYPE#"<>10 AND "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0)) 22 - access("I"."OBJ#"=:B1) 23 - filter(("I"."BO#"="T"."OBJ#" AND BITAND("T"."PROPERTY",36893488147419103232)=368934881474191032 32)) 24 - access("IO"."OBJ#"="I"."BO#" AND "IO"."TYPE#"=2) filter("IO"."TYPE#"=2) 25 - filter(BITAND("T"."PROPERTY",36893488147419103232)=0) 26 - access("T"."OBJ#"=:B1) 27 - filter((BITAND("S"."FLAGS",1024)=0 OR "S"."FLAGS" IS NULL)) 28 - access("S"."OBJ#"=:B1) 29 - filter("TYPE#"=:B1) 30 - access("UE"."USER#"=:B1) 31 - filter("UE"."TYPE#"=:B1) 32 - access("UE"."USER#"=:B1) 34 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) )) 35 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#") 39 - access("L"."OWNER#"="U"."USER#") Note ----- - dynamic statistics used: statistics for conventional DML 92 rows selected.
当对SQL语句进行硬解析时，我们可以看到优化器检测到表中已经添加了其他行，在执行计划的第2步出现了”OPTIMIZER STATISTICS GATHERING”，同时在最下面注意部分中也指出了：dynamic statistics used: statistics for conventional DML。接下来由于方便快速演示，我们需要强制数据库将优化器统计信息写入到数据字典。
SQL>EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SQL> select distinct object_type from t1 where owner='SYS';
OBJECT_TYPE
-----------------------
INDEX
JOB
CLUSTER
SYNONYM
LOB PARTITION
UNIFIED AUDIT POLICY
SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7k4h0792x1vdx, child number 1
-------------------------------------
select distinct object_type from t1 where owner='SYS'
Plan hash value: 2134347679
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1173 (100)| |
| 1 | HASH UNIQUE | | 45 | 3555 | 1173 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 8685 | 670K| 1172 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
Note
-----
- dynamic statistics used: statistics for conventional DML
23 rows selected.
当对SQL语句进行硬解析时，我们可以看到优化器检测到表中已经添加了其他行，同时在最下面注意部分中也指出了这一点：dynamic statistics used: statistics for conventional DML
在DBA_TAB_STATISTICS中我们可以查询到实时统计信息。在notes列中表示为STATS_ON_CONVENTIONAL_DML.
SQL> SELECT NUM_ROWS,LAST_ANALYZED, BLOCKS, NOTES FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'T1' ;
NUM_ROWS LAST_ANALYZED BLOCKS NOTES
---------- ------------------- ---------- -------------------------
72374 2020-02-29 02:14:29 1439
217124 2020-02-29 02:19:05 4308 STATS_ON_CONVENTIONAL_DML
如果想要禁用这个功能，可以使用NO_GATHER_OPTIMIZER_STATISTICS hint来实现。
Post a Comment