1 Automatic Report Capturing 特性说明

Oracle 在 12c 中引入了一个新特性：”Automatic Report Capturing Feature”。MMON_SLAVE会识别资源密集型SQL，并为这些SQL自动生成SQL monitoring报告。http://www.cndba.cn/dave/article/131497

As part of this feature, some monitoring SQLs are executed by MMON_SLAVE to identify the resource-intensive SQLs and generate the SQL Monitoring report automatically for those SQLs.
Those SQLs consume little more CPU and it is expected behavior being a new feature. Such monitoring queries can be identified from (G)V$SQLSTA.http://www.cndba.cn/dave/article/131497

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

官方的说法是消耗一点 CPU，但实际上该特性有很多的 bug，包括更多的 CPU 和其他错误。

High CPU Usage and/or Frequent Occurrences of ORA-12850 or ORA-12751 For Monitor Queries by MMON From 12.1 (Doc ID 2102131.1)
ORA-12850 is Raised by Mmon Slave Automatic Report Flush Action (Doc ID 2217969.1)http://www.cndba.cn/dave/article/131497

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

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

2 关闭该特性

Oracle 19c 查看隐含参数视图
https://www.cndba.cn/dave/article/131496http://www.cndba.cn/dave/article/131497

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

可以通过隐含参数_report_capture_cycle_time禁用该特性：http://www.cndba.cn/dave/article/131497

SQL> set lines 190
SQL> select paramName,SessionValue,InstanceValue from hparams where paramname='_report_capture_cycle_time';

PARAMNAME                                          SESSIONVALUE    INSTANCEVALUE
-------------------------------------------------- --------------- ---------------
_report_capture_cycle_time                         60              60

SQL>

该数据默认是 60，即使在最新的 19.21 中默认值依旧是 60。 生产环境建议修改成 0. http://www.cndba.cn/dave/article/131497

SQL> alter system set "_report_capture_cycle_time"=0;

System altered.

SQL>  select paramName,SessionValue,InstanceValue from hparams where paramname='_report_capture_cycle_time';

PARAMNAME                                          SESSIONVALUE    INSTANCEVALUE
-------------------------------------------------- --------------- ---------------
_report_capture_cycle_time                         0               0

SQL>

官网也解释这个参数可以放心修改，修改为 0 仅禁用12c中引入的自动报告捕获功能。它不会禁用原始的SQL监控框架。SQL监控可以很好地使用，没有任何问题。

There is no negative impact in setting the above parameter as it disables only the automatic report capturing feature introduced in 12c. It does not disable the original SQL monitoring framework. SQL monitoring can be used very well without any issues.

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