1 real-time statistics 特性说明

Oracle 在 19c 中引入了 real-time statistics 的特性，官网说明如下：

https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/optimizer-statistics-concepts.html#GUID-972CC658-D615-4071-86BB-371D43D9CF95http://www.cndba.cn/dave/article/131495http://www.cndba.cn/dave/article/131495

Oracle Database 19c introduces real-time statistics, which extends online statistics gathering to also include conventional DML statements. Statistics can now be collected ‘on-the-fly’ during conventional DML operations. http://www.cndba.cn/dave/article/131495

http://www.cndba.cn/dave/article/131495

http://www.cndba.cn/dave/article/131495http://www.cndba.cn/dave/article/131495

Statistics are normally gathered by automatic statistics gather job which runs inside the database maintenance window – but that is just once a day. But for volatile tables statistics can go stale between DBMS_STATS job executions, so the new Oracle 19c feature of real-time statistics can help the optimizer generate more optimal plans for such volatile tables. Bulk load operations will gather all necessary statistics (pre Oracle 19c behavior)- However real-time statistics augment rather than replace traditional statistics.http://www.cndba.cn/dave/article/131495

http://www.cndba.cn/dave/article/131495

当启用该特性后，对于执行常规的DML操作，数据库也会自动ONLINE收集统计信息，可能触发Library Cache Lock争用、加载为0行时统计信息被自动收集为0，导致执行计划错误等缺陷，推荐关闭。

2 关闭 real-time statistics 特性

Disabling Real-time Statistics In 19c (Doc ID 2577100.1)
real-time Statistics Are Off by Default Starting in 19.10.0.0.0DBRU (Doc ID 2747507.1)http://www.cndba.cn/dave/article/131495

在 Oracle 19.10 之前没有参数来控制该特性， 所以在 19.1 到 19.9 之间的版本只能通过隐含参数来关闭该特性：

alter system set "_optimizer_gather_stats_on_conventional_dml"=FALSE scope=spfile;
alter system set "_optimizer_gather_stats_on_load"=FALSE scope=spfile;
alter system set "_optimizer_gather_stats_on_load_index"=FALSE scope=spfile;

到了 19.10 之后，Oracle 也意识到这个问题，所以增加了控制参数：optimizer_real_time_statistics并默认设置为 False。

OPTIMIZER_REAL_TIME_STATISTICS Initialization Parameter
When the OPTIMIZER_REAL_TIME_STATISTICS initialization parameter is set to TRUE, Oracle Database automatically gathers real-time statistics during conventional DML operations. The default setting is FALSE, which means real-time statistics are disabled.

SQL> show parameter 'OPTIMIZER_REAL_TIME_STATISTICS'

NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
optimizer_real_time_statistics       boolean         FALSE
SQL>

19.10 版本后启动该特性设置为 TRUE:

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>

版权声明：本文为博主原创文章，未经博主允许不得转载。