【避坑指南】12.2 新功能优化器统计顾问（Optimizer Statistics Advisor）及常见问题和解决方法

发布日期：2024-09-09 来源: 网络 阅读量（）

出品丨TeacherWhat

题图：Oracle Middle East Headquarter

关键字：Optimizer Statistics Advisor，统计信息 ，ORA-20001 ，ORA-4036，AUTO_STATS_ADVISOR_TASK

正文约4000字，建议阅读时间8分钟

目录结构：

1. 概述

2. 问题1：ORA-12012, ORA-20001 error错误

3. 问题2：SYSAUX表空间增长过快，导致可用表空间不足

4. 问题3：CDB/PDB中EXECUTION_DAYS_TO_EXPIRE设置问题

5. 问题4：超过PGA限制引起ORA-4036

6.问题5：禁用优化器统计顾问功能

对于Oracle的CBO优化器而言，统计信息是其最重要的组成部分。

因此，Oracle在统计信息的准确性方面不断地进行优化和加强。

从12.2版本开始，Oracle推出了优化器统计顾问（Optimizer Statistics Advisor）,根据事先定义的规则，定期执行，提供给用户参考的统计信息收集相关的建议。

详细参考：

统计信息收集相关文章内容：

【常用命令】自动统计收集的停止（无效）和启动（有效）

自动统计信息收集（Automatic Optimizer Statistics Collection）

在12.2.0.1以后的版本上运行该功能时，遇到问题的报告很多, 但是Oracle也在不断地修正着相关地问题，所以保持数据库为最新版本通常是一个最佳的选择。

由于12.2.0.1等版本上的设计不足(Bug) 等，可能会在告警日志（alert log）或者JOB跟踪日志中，发现ORA-12012, ORA-20001等错误。

错误例：

可以参考如下方法尝试解决。

由于优化器统计顾问的执行，SYSAUX表空间增长过快，导致可用表空间不足。

问题特征：

解决方法：

1. 尝试删除Statistics Advisor 任务(AUTO_STATS_ADVISOR_TASK)，然后重建。

具体删除方法例:

2. 缩短任务执行历史的保存时间

3. 12.2.0.1版本由于Bug 26764561的影响，即使EXECUTION_DAYS_TO_EXPIRE设成10天后，优化器统计顾问相关段依然不断增加。 所以首先看看这个bug是否被修复，如果存在则建议打上补丁或打上最新的RU。

参考：

※注意打补丁后的Postinstallation内容

4.关于EXECUTION_DAYS_TO_EXPIRE的默认值：

对于多租户环境中，CDB/PDB的设置相对独立，互相不影响。

EXECUTION_DAYS_TO_EXPIRE的CDB/PDB设置测试例：

PDB/CDB的JOB关联性的测试：

在执行统计信息收集JOB等的过程中会调用优化器统计顾问任务。

如果Statistics Advisor 任务的数据过多，有可能导致在执行统计信息收集JOB过程中引发ORA-4036.

例如输出的跟踪日志中的HEAP DUMP中qosadvCreateSu Chunk占用了较大空间，qosadvCreateSu是Statistics Advisor运行时所需的heap。

可以通过定期删除Statistics Advisor 任务的数据 或者增加PGA_AGGREGATE_LIMIT的大小来解决。

由于这个功能可能引起一些问题，所以Oracle通过Enghance Bug 26749785提供了禁用该功能.

Bug 26749785将在未来版本20.1中默认修复，12.2/18c/19c的版本可以通过申请Bug 26749785的补丁来增加这个功能。

打了Bug 26749785的补丁后，就可以通过如下方法将这个功能禁用。

参考：

——End——