Oracle12c统计信息应该如何分析

引言

统计信息是日常DBA做SQL优化重要考量指标，但很多时候您会发现，收集了统计信息，执行计划的a_rows和e_rows仍然数据相差较大，SQL还是执行的很慢。抛出去种种主观+客观的因素，今天我们实际要聊的话题是，如何最大限度保证统计信息的质量，并在必要时提供统计信息的纠正措施。是的，它的名字叫做Optimizer Statistics Advisor。自古以来，善守者，敌不知其所攻，善攻者，敌不知其所守。有了统计信息优化顾问，我们就等同于有了军师，有了提供合理优化建议的保证，600团队一向秉承把技术说透的理念为广大oracle爱好者，从业工作者服务。文章涉及知识点，理论点过多，如果愿意，请您耐心看完。

优化器统计信息顾问是在Oracle12cR2版本中被引入的，用于帮助验证优化器统计信息的质量，并在必要时提供纠正措施。DBMS_STATS包已经扩展到包括与advisor程序交互的例程，它是oracle内置的诊断产品，可以帮助分析统计信息，以及统计信息相关执行任务的质量问题。优化器统计信息顾问的任务在维护窗口中自动运行。当然，我们也可以根据需要运行或者关闭掉这个新特性。运行以后，我们可以随时查看advisor报告。如果advisor工具提出了建设性建议，那么在某些情况下，我们可以运行系统生成的脚本来纠正我们收集统计信息期间的一系列错误行为。

注意了同志们，优化器统计信息顾问使用与自动数据库诊断监视器(ADDM)、SQL性能分析器和其他顾问相同的advisor框架。以下为优化器统计信息顾问的整体执行分析步骤：

优化器统计信息顾问通过Oracle提供的规则列表工作，这些规则表示优化器统计信息的“最佳实践”。它检查每个规则，并在必要时生成结果、建议和操作，包括调用DBMS_STATS包以采取纠正措施。可以使用V$STATS_ADVISOR_RULES视图显示规则列表。随着时间的推移，随着后续补丁和版本的发布，它们可能会发生变化

 通常，优化器统计信息顾问在不遵循或违反特定规则时生成查找，尽管某些查找(如对象过时只提供信息。比方说，我们调用 DBMS_STATS.GATHER_TABLE_STATS 使用了ESTIMATE_PERCENT=>0.01，这违反了

ESTIMATE_PERCENT=>AUTO_SAMPLE_SIZE规则。又比如说：advisor工具可能发现在收集统计信息时不使用抽样的规则，建议指定

AUTO_SAMPLE_SIZE。advisor将建议存储在DBA_ADVISOR_RECOMMENDATIONS中

下图显示了工作流中的自动和手动路径。如果AUTO_STATS_ADVISOR_TASK在维护窗口中自动运行，那么我们的工作流将从查询报表开始。在手动工作流中，我们必须使用PL/SQL来创建和执行任务

具体步骤描述：

1. 手动执行Create an Optimizer Advisor task using DBMS_STATS.CREATE_ADVISOR_TASK

2. 可以通过 DBA_ADVISOR_EXECUTIONS 验证执行情况

3. DBMS_STATS.CONFIGURE_ADVISOR_ * _FILTER 为配置筛选器，此工作为可选部分

4. 手动执行 DBMS_STATS.EXECUTE_ADVISOR_TASK 开始执行顾问作业

5. 生成 advisor 报告

6. 自动统计信息的建议通过 DBMS_STATS.IMPLEMENT_ADVISOR_TASK ，手动需要借助 PLSQL ，运行DBMS_STATS.SCRIPT_ADVISOR_TASK 来实现

优化器统计信息顾问在维护窗口期间运行一个名为AUTO_STATS_ADVISOR_TASK的任务，因此在默认情况下，我们不需要做任何事情就可以开始查看关于数据库统计信息的建议。与其他顾问一样，我们使用dba_advisor_execution视图来标识感兴趣的执行

由于优化器Statistics Advisor使用普通的Advisor框架，所以可以在{CDB|DBA|ALL|USER} _advisor_%视图中查看Advisor执行的结果，但是有一种更简单的方法来获得结果。使用DBMS_STATS包中的REPORT_ADVISOR_TASK函数生成优化器统计信息顾问报告。它接受以下参数

可以使用DBMS_STATS包中的IMPLEMENT_ADVISOR_TASK函数直接实现优化器统计信息顾问建议。它接受以下参数

筛选器可以让我们在日常使用中更灵活，更有针对性，它能够从Optimizer Statistics Advisor任务中排除对象、设定规则和实施具体操作。

筛选器是使用DBMS_STATS将优化器Statistics Advisor任务限制为用户指定的规则、模式或操作集。

过滤器对于包含或排除特定的结果集非常有用。例如，我们可以将advisor任务配置为只包含scott模式的建议。此外，我们还可以为陈旧的统计数据排除所有违反规则的情况。过滤器的主要优点是能够忽略我们压根不感兴趣的建议，并减少advisor任务的开销。

优化器统计信息顾问筛选器样例：创建一个名为sh_obj_filter的过程，该过程将指定的任务限制为sh模式中的对象。

创建一个名为opr_filter的过程，该过程配置一个任务，以便对hr模式中收集表统计信息之外的所有操作提供建议。

最后我们通过一个案例来总结今天分享的优化器统计信息顾问功能，我们分别使用CREATE_ADVISOR_TASK和EXECUTE_ADVISOR_TASK函数创建一个新任务并执行它。这些参数可选地接受TASK_NAME和EXECUTION_NAME参数。如果没有指定，则创建系统生成的名称。然后生成与此任务相关的报告。

🔥 🔥 🔥  3月中旬开始，600团队开启线上免费公开课，届时请关注600公众号，我们会在公众号注明具体公开课程下载地址，课程完全免费

💓 主题包括但不限于如下

1. 结合作者在国网和石化以及五矿做开发DBA实体开发案例和群内其他成员案例；
   

2. 如何编写高效的SQL语句，以及PLSQL代码；

3. 如何分析awr报告，包括定位问题，诊断问题，如何看懂hanganalyze systemstate errorstack oradebug工具使用等等。

    😍 同时如果公开课反响不错，后期我们也会开放备份恢复，数据库性能优化和sql优化包括工作原理解析等实际生产环境遇到的案例。后期mysql和postgresql包括voltdb geode greenplum以及hadoop如果大家感兴趣，我们都可以在公开课内容体现，暂时以oracle为主，谢谢大家。

原创：如人饮水冷暖自知