oracle 19c新特性-Real-time Stats

在数据仓库环境中,我们经常会遇到表被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来实现。

参考文档:https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-concepts.html#GUID-D55D673B-1FD9-45C9-A109-F440B7E96E62

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *