Oracle 19c 新特性实时统计信息收集(real-time statistics) 说明 -- cnDBA.cn

1 实时统计信息收集(real-time statistics) 概述

为了解决统计信息不准确的问题，Oracle 在Oracle 12c中引入了在线统计信息收集功能，但只能用在CTAS(create table as select)和IIS(insert into select) using Direct-Path Inserts这些类型的操作。

Oracle 19c 新特性批量加载的在线统计信息收集(Online Statistics Gathering for Bulk-Load) 说明
https://www.cndba.cn/dave/article/4369

在Oracle 19c中进一步增加了这个功能，引入了实时统计信息收集(real-time statistics)的特性。官网对该特性的说明如下：

Real-Time Statistics
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-concepts.html#GUID-769E609D-0312-43A7-9581-3F3EACF10BA9

实时统计（Real-Time Statistics）扩展了对常规DML语句的在线支持。因为用DBMS_STATS作业收集统计信息可能会存在统计信息陈旧（stale statistics）的问题，所以实时统计可以帮助优化器生成更优的执行计划。

相对于传统的DBMS_STATS作业收集的统计信息，19c中支持的在线统计信息（Online statistics）已经支持批量加载（Online Statistics Gathering for Bulk-Load）和常规DML（Real-Time Statistics）。在线统计信息的主要目的也是尽可能的减少统计信息陈旧给优化器带来的误导。

虽然在线统计信息（Online statistics）会收集bulk load操作所有必要的统计信息，但是real-time statistics 只是统计信息收集补充，其只收集了最基本的统计信息，不能取代传统的统计信息收集，所有传统的DBMS_STATS定时收取统计信息还需要继续运行。

有两种方法可以验证进行了real-time statistics gathering：

1.执行计划的Note中会出现：stats for conventional DML
2.”DBA_TAB_COL_STATISTICS” 和 “DBA_TAB_STATISTICS” 视图的NOTES 列中会显示STATS_ON_CONVENTIONAL_DML,SCOPE 列会显示成SHARED。

如果想禁用该特性，可以在SQL中添加Hint： /+NO_GATHER_OPTIMIZER_STATISTICS/：

SELECT /+ NO_GATHER_OPTIMIZER_STATISTICS / …

实时统计信息收集由optimizer_real_time_statistics参数控制，该参数默认值为False，即默认没有启用改功能。按官网的说法，该参数在Oracle 21c 以后才生效。但是在19.10 的单实例上已经可以设置。

https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/OPTIMIZER_REAL_TIME_STATISTICS.html#GUID-313C8DE2-6F01-49E3-962F-653065C14F57

[dave@www.cndba.cn ~]$ sql / as sysdba

SQLcl: Release 19.1 Production on Tue Jan 26 17:00:43 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> show parameter optimizer_real_time_statistics
NAME                           TYPE    VALUE
------------------------------ ------- -----
optimizer_real_time_statistics boolean FALSE
SQL> alter system set optimizer_real_time_statistics=true;

System altered.


SQL> show parameter optimizer_real_time_statistics
NAME                           TYPE    VALUE
------------------------------ ------- -----
optimizer_real_time_statistics boolean TRUE
SQL>

当OPTIMIZER_REAL_TIME_STATISTICS初始化参数设置为true时，Oracle数据库在常规DML操作期间自动收集实时统计信息。默认设置为false，这意味着禁用实时统计。

按MOS的说法，在Siebel DB中，建议关闭Real-Time Statistics。

Steps To Disable Real Time Statistics From 19c (Doc ID 2700103.1)
http://www.cndba.cn/cndba/dave/article/4370

2 操作示例

该部分示例官方文档上描述的非常详细，可以直接参考：

10.3.3.3.4 Real-Time Statistics: Example
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-concepts.html#GUID-D55D673B-1FD9-45C9-A109-F440B7E96E62

2.1 非Exadata 环境测试

在非XD的19c中，该特性默认没有启用，测试之前需要先启用改特性。

[dave@www.cndba.cn ~]$ sql / as sysdba

SQLcl: Release 19.1 Production on Tue Jan 26 17:00:43 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> show parameter optimizer_real_time_statistics
NAME                           TYPE    VALUE
------------------------------ ------- -----
optimizer_real_time_statistics boolean FALSE
SQL> alter system set optimizer_real_time_statistics=true;

System altered.


SQL> show parameter optimizer_real_time_statistics
NAME                           TYPE    VALUE
------------------------------ ------- -----
optimizer_real_time_statistics boolean TRUE
SQL>

在PDB中进行测试：

[dave@www.cndba.cn ~]$ sql dave/dave@dave

SQLcl: Release 19.1 Production on Tue Jan 26 17:43:15 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Tue Jan 26 2021 17:43:15 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0


SQL> show parameter optimizer_real_time_statistics
NAME                           TYPE    VALUE
------------------------------ ------- -----
optimizer_real_time_statistics boolean TRUE
SQL>


SQL> create table anqing as select * from dba_objects where 1=2;

Table created.

SQL>  insert /*+append*/ into anqing select * from dba_objects;

72631 rows created.

SQL> commit;

Commit complete.

--批量加载在线收集正常：
SQL>  select  TABLE_NAME, NUM_ROWS, BLOCKS, NOTES, SCOPE from  DBA_TAB_STATISTICS where  table_name='ANQING';

TABLE_NAME        NUM_ROWS     BLOCKS NOTES                     SCOPE
--------------- ---------- ---------- ------------------------- -------
ANQING               72631       1443                           SHARED

SQL> select TABLE_NAME,COLUMN_NAME,NOTES,SCOPE,LAST_ANALYZED from dba_tab_col_statistics where owner='DAVE' and table_name='ANQING';

TABLE_NAME      COLUMN_NAME                                   NOTES                     SCOPE   LAST_ANALYZED
--------------- --------------------------------------------- ------------------------- ------- -------------------
ANQING          CREATED_APPID                                 STATS_ON_LOAD             SHARED  2021/01/26 18:27:39
ANQING          CREATED_VSNID                                 STATS_ON_LOAD             SHARED  2021/01/26 18:27:39
ANQING          MODIFIED_APPID                                STATS_ON_LOAD             SHARED  2021/01/26 18:27:39
ANQING          MODIFIED_VSNID                                STATS_ON_LOAD             SHARED  2021/01/26 18:27:39
ANQING          OWNER                                         STATS_ON_LOAD             SHARED  2021/01/26 18:27:39
ANQING          OBJECT_NAME                                   STATS_ON_LOAD             SHARED  2021/01/26 18:27:39
ANQING          SUBOBJECT_NAME                                STATS_ON_LOAD             SHARED  2021/01/26 18:27:39
……

采用普通的insert into（没有添加append）：

SQL>  insert into anqing select * from dba_objects;
72631 rows created.

SQL> commit;
Commit complete.

SQL> select table_name,last_analyzed,notes from dba_tab_statistics where owner='DAVE' and table_name='ANQING';

TABLE_NAME      LAST_ANALYZED       NOTES
--------------- ------------------- --------------------
ANQING          2021/01/26 17:47:43

SQL>  select TABLE_NAME,COLUMN_NAME,NOTES,HISTOGRAM,LAST_ANALYZED from dba_tab_col_statistics where owner='DAVE' and table_name='ANQING';
TABLE_NAME      COLUMN_NAME          NOTES                HISTOGRAM       LAST_ANALYZED
--------------- -------------------- -------------------- --------------- -------------------
ANQING          OWNER                STATS_ON_LOAD        NONE            2021/01/26 17:47:43
ANQING          OBJECT_NAME          STATS_ON_LOAD        NONE            2021/01/26 17:47:43
ANQING          SUBOBJECT_NAME       STATS_ON_LOAD        NONE            2021/01/26 17:47:43
ANQING          OBJECT_ID            STATS_ON_LOAD        NONE            2021/01/26 17:47:43
ANQING          DATA_OBJECT_ID       STATS_ON_LOAD        NONE            2021/01/26 17:47:43
ANQING          OBJECT_TYPE          STATS_ON_LOAD        NONE            2021/01/26 17:47:43

SQL> select  TABLE_NAME, NUM_ROWS, BLOCKS, NOTES, SCOPE from  DBA_TAB_STATISTICS where  table_name='ANQING';

TABLE_NAME        NUM_ROWS     BLOCKS NOTES                SCOPE
--------------- ---------- ---------- -------------------- -------
ANQING                   0          0                      SHARED

但是实时统计信息收集不能生效。

因为默认情况下缓存在SGA的Buffer中统计信息每隔15分钟会写入数据字典，强制将优化器统计信息刷到数据字典：

SQL> exec  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.

SQL>  select  TABLE_NAME, NUM_ROWS, BLOCKS, NOTES, SCOPE from  DBA_TAB_STATISTICS where  table_name='ANQING';

TABLE_NAME        NUM_ROWS     BLOCKS NOTES                SCOPE
--------------- ---------- ---------- -------------------- -------
ANQING                   0          0                      SHARED

依旧不能生效。

2.2 Exadata 环境中测试

因为官网文档中说optimizer_real_time_statistics 参数是Oracle 21c 以后才生效。虽然我们在19c 中可以修改该参数，但是在上小结测试并没有生效。

手头没有XD 环境，所以通过”_exadata_feature_on” 参数模拟。

启用XD特性：

[dave@www.cndba.cn ~]$ sql / as sysdba

SQLcl: Release 19.1 Production on Tue Jan 26 18:39:14 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> alter system set "_exadata_feature_on"=true scope=spfile;

System altered.

SQL> startup force

Total System Global Area   1543500824 bytes
Fixed Size                    9135128 bytes
Variable Size              1308622848 bytes
Database Buffers            218103808 bytes
Redo Buffers                  7639040 bytes
Database mounted.
Database opened.
SQL>

普通方式插入数据：

SQL> insert into anqing(OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID) select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID*3,DATA_OBJECT_ID*4 from dba_objects;

72631 rows created.

SQL> commit;

Commit complete.

查询验证：

SQL> select  TABLE_NAME, NUM_ROWS, BLOCKS, NOTES, SCOPE from  DBA_TAB_STATISTICS where  table_name='ANQING';

TABLE_NAME             NUM_ROWS     BLOCKS NOTES                     SCOPE
-------------------- ---------- ---------- ------------------------- -------
ANQING                    72631       1443                           SHARED

SQL> select TABLE_NAME,COLUMN_NAME,NOTES,SCOPE,LAST_ANALYZED from dba_tab_col_statistics where owner='DAVE' and table_name='ANQING';

TABLE_NAME           COLUMN_NAME                                   NOTES                                    SCOPE   LAST_ANAL
-------------------- --------------------------------------------- ---------------------------------------- ------- ---------
ANQING               CREATED_APPID                                 STATS_ON_LOAD                            SHARED  26-JAN-21
……
ANQING               DEFAULT_COLLATION                             STATS_ON_LOAD                            SHARED  26-JAN-21
ANQING               DUPLICATED                                    STATS_ON_LOAD                            SHARED  26-JAN-21
ANQING               SHARDED                                       STATS_ON_LOAD                            SHARED  26-JAN-21
ANQING               OWNER                                         STATS_ON_CONVENTIONAL_DML                SHARED  26-JAN-21
ANQING               OBJECT_NAME                                   STATS_ON_CONVENTIONAL_DML                SHARED  26-JAN-21
ANQING               SUBOBJECT_NAME                                STATS_ON_CONVENTIONAL_DML                SHARED  26-JAN-21
ANQING               OBJECT_ID                                     STATS_ON_CONVENTIONAL_DML                SHARED  26-JAN-21
ANQING               DATA_OBJECT_ID                                STATS_ON_CONVENTIONAL_DML                SHARED  26-JAN-21

31 rows selected.

DBA_TAB_STATISTICS 数据还没有通过，但是dba_tab_col_statistics 已经同步了。

刷新统计信息，在查询，数据已经同步了：

SQL> exec  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.


SQL> select  TABLE_NAME, NUM_ROWS, BLOCKS, NOTES, SCOPE from  DBA_TAB_STATISTICS where  table_name='ANQING';

TABLE_NAME             NUM_ROWS     BLOCKS NOTES                     SCOPE
-------------------- ---------- ---------- ------------------------- -------
ANQING                    72631       1443                           SHARED
ANQING                   145262       3930 STATS_ON_CONVENTIONAL_DML SHARED

验证执行计划：

SQL> select count(1) from anqing where object_id > 100;

  COUNT(1)
----------
    290258

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6f2nnkzzy500t, child number 0
-------------------------------------
select count(1) from anqing where object_id > 100

Plan hash value: 1100838991

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |  1068 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| ANQING |   145K|   708K|  1068   (1)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID">100)

Note
-----
   - dynamic statistics used: statistics for conventional DML


23 rows selected.

3 小结

根据之前的说明和测试，可以推出如下结论：

1.实时统计信息收集(real-time statistics) 是Oracle 19c 引入的新特性，但在19c 中只能在Exadata 环境中使用。
2.在Oracle 21c 之后，改特性可以在非Exadata 环境中使用。
3.找了一个有19.7 XD 环境的兄弟帮我查询了一下，并未发现optimizer_real_time_statistics这个参数。因为我的测试版本是19.10，所以目前还无法验证在19.10 以下版本的XD中能否正常使用。目前通过测试可以得出的结论在19.10 的XD 环境中，实时统计信息收集是可以正常工作的。

签到成功

CNDBA社区