巧用DISPLAY_AWR函数与dba_hist_sqlstat结合查询SQL语句在指定节点指定时间范围内的历史执行计划

1、问题

       通过调用dbms_xplan包中DISPLAY_AWR函数（DBMS_XPLAN.DISPLAY_AWR）可以从AWR数据中查看到SQL语句的历史执行计划，但是，DISPLAY_AWR函数的可传入参数只有四种，分别为：sql_id、plan_hash_value、db_id、format，缺少与时间范围相关的参数、也没有instance_number相关参数。

       使用dbms_xplan.display_awr的简单方式，一般为：

         SQL>select * from table(dbms_xplan.display_awr(db_id=> 19948XXXX2,sql_id=> 'bj75p9188y410'));

 

        假如一套RAC环境，在8月5日的9：00—09：30时，2节点发生了CPU消耗非常高的情况，如果要分析是不是因为SQL_ID为bj75p9188y410 的语句的执行计划走错所致，这时，如果想用dbms_xplan.display_awr的简单查询方式来得到当时的执行计划，是无法实现的，那应该怎样查出该语句8月5日的9：00—09：30时第2节点上SQL_ID为bj75p9188y410的语句的执行计划是怎样子的呢？

2、分析

        如果通过DBMS_XPLAN.DISPLAY_AWR查看SQL语句的执行计划，将是从整个AWR数据库中查找，例如从AWR报告中查询SQL_ID为bj75p9188y410 的执行计划：

          SQL>select * from table(dbms_xplan.display_awr(db_id=> 19948XXXX2,sql_id=> 'bj75p9188y410'))

        为了以简短的篇幅展示出从AWR中总共查到了几种执行计划，我将语句改写如下：

          SQL> select * from table(dbms_xplan.display_awr(db_id=> 19948XXXX2,sql_id=> 'bj75p9188y410')) where     
               plan_table_output  like ('Planhash value%');

  结果如下：

1	Plan hash value: 6178145
2	Plan hash value: 2354817963
3	Plan hash value: 3990363694

        从此结果中看出，SQL_ID为bj75p9188y410 的语句在当前保留的AWR数据中存在三种执行计划。其中Plan hash value为3990363694的执行计划为错误的执行计划

实际生产环境中，在8月5日的9：00—09：30时，2节点发生了CPU消耗非常高的情况。现在就是要确认在此时间，该SQL_ID为bj75p9188y410的语句到底是使用哪个执行计划呢？

3、解决方法

3.1 、查到8月5日9：00—09：30的 snap_id

SQL>select dbid,snap_id,instance_number,begin_interval_time,end_interval_time

     fromdba_hist_snapshot

    wherebegin_interval_time >=to_date('2013-08-0509:00:00', 'yyyy-mm-dd hh24:mi:ss')

      andend_interval_time <=to_date('2013-08-0509:31:00', 'yyyy-mm-dd hh24:mi:ss')

结果为：

dbid

snap_id