一个执行计划异常变更的案例 - 外传之SQL AWR

之前的几篇文章： 《一个执行计划异常变更的案例 - 前传》 《一个执行计划异常变更的案例 - 外传之绑定变量窥探》 《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》 《一个执行计划异常变更的案例 - 外传之rolling invalidation》 《一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)》 《一个执行计划异常变更的案例 - 外传之查询执行计划的几种方法》 《一个执行计划异常变更的案例 - 外传之AWR》 《一个执行计划异常变更的案例 - 外传之ASH》

《一个执行计划异常变更的案例 - 外传之查询执行计划的几种方法》曾介绍过一些常用的查看执行计划的方法，今儿再说说如何通过AWR查看执行计划。

创建SQL AWR的前提是SQL要被采集至AWR中，才能使用awrsqrpt.sql脚本利用sql_id创建SQL AWR报告，创建过程和AWR类似，需要先选择报告格式、实例序号、创建的天数、快照起始和结束ID，

这里写图片描述

这里写图片描述

这里写图片描述

最重要的一步就是输入sql_id，其中sql_id可以从AWR报告，或者dba_hist_sqltext等视图中获取。

这里写图片描述

当前路径下就会产生这份SQL AWR报告。

这里写图片描述

SQL AWR报告中会有一些数据库和SQL基本信息，

这里写图片描述

针对SQL，包含一些基本的统计信息，

这里写图片描述

补充：这篇文章《How to get execution statistics and history for a SQL (文档 ID 1371778.1)》介绍了另外一种从内存或AWR查询SQL执行统计信息的方法。

还会包含执行计划，

这里写图片描述

这里要说的是我在测试的过程中，曾经想执行一个普通的SQL，然后能创建出SQL AWR报告，却碰见了一个问题：该SQL未被AWR捕获，执行awrsqrpt.sql脚本输入sql_id会提示未找到。

解决方法： 方法1： 修改AWR采集默认的topnsql参数，默认值是DEFAULT，可以改为1000或MAXIMUM，用于测试，生产环境采集越多，消耗的系统资源越大，需要权衡。 参考《How to Control the Set of Top SQLs Captured During AWR Snapshot Generation (文档 ID 554831.1)》

It is possible to set the value for this setting to a very high number to capture the complete set of SQL in the cursor cache. Since the TOPNSQL setting affects the number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, and Version Count) this may lead to space and performance issues since there will be more data to collect and store. The value for this setting is not affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection.

exec dbms_workload_repository.modify_snapshot_settings(topnsql => '参数值');

此处参数可以设置为整数或字符型两种。 (a) 设置为整型，则代表刷新SQL至AWR的top条数，最小值是30，最大值时50000，空值表示使用当前参数值。 (b) 设置为字符型， DEFAULT-若statistics参数值是TYPICAL，则选择前30条SQL，若statistics参数值是ALL，则选择前100条SQL。 MAXIMUM-捕获cursor cache中所有SQL。 N-等同于(a)的设置。

这里写图片描述

方法2：还可以使用add_colored_sql将指定SQL采集至AWR中，

采集sql_id为fv6c79ub89g75的SQL，
exec dbms_workload_repository.add_colored_sql('fv6c79ub89g75');
查询执行手工采集的SQL，
select * from sys.wrm$_colored_sql;
删除采集到的sql_id为fv6c79ub89g75的SQL，
exec dbms_workload_repository.remove_colored_sql('fv6c79ub89g75');

方法3： 确保设置了statistics_level参数值为ALL或TYPICAL。 control_management_pack_access参数值为DIAGNOSTIC+TUNING。

方法4： 手工创建快照时设置flush_level参数为ALL，

exec dbms_workload_repository.create_snapshot(flush_level=>'ALL');

我用的11.2.0.4的库，其中方法三是前提，使用方法一、二和四，均可以使用awrsqrpt.sql脚本创建SQL AWR，只是还是有一个问题未解决，就是SQL执行计划未被捕获，提示的是No data exists for this section of the report.，如果局部出现这个错误，属于正常现象，表示因为未达到标准，所以未被采集。

这里写图片描述

由于该SQL只执行了一次，可能并未是TOP N的SQL，通过上面的方法可以采集SQL，却尚未找出执行计划未被采集的条件和方法，如果有朋友了解或使用过，还请来指点迷经，谢谢。

总结： 使用awrsqrpt.sql可以来创建SQL AWR，查看SQL执行的统计信息、执行计划等，但前提是要被AWR采集，介绍了几种手工采集SQL至AWR的方法。 尚未解决的问题就是如何采集指定的执行计划。