巧用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	instance_number	begin_interval_time	end_interval_time
19948XXXX2	33676	1	05-8月 -13 09.00.09.903	05-8月 -13 09.30.10.113
19948XXXX2	33676	2	05-8月 -13 09.00.09.786	05-8月 -13 09.30.10.502

3.2 、通过 dbms_xplan.display_awr   与包含snap_id、instance_number信息的视图关联得到8月5日9：00—09：30时SQL_ID为 bj75p9188y410   的执行计划：

  SQL>select a.* from (select distinct dbid,sql_id, plan_hash_value from dba_hist_sqlstat

          wheresql_id = 'bj75p9188y410'

            andsnap_id =   33676

            andinstance_number = 2) b,          table ( dbms_xplan.display_awr ( db_id =>   19948XXXX2 , sql_id =>  b.sql_id , plan_hash_value =>  b.plan_hash_value ))  a;

结果如下：

SQL_ID bj75p9188y410

--------------------

select * from ( select distinct b.XXXX_id as

……   （为了信息脱敏，真实语句在此省略）

,'NLS_SORT=SCHINESE_XXXX'),b.XXXX_name  ) where rownum <= :1

Plan hash value: 3990363694

---------------------------------------------------------------------------------------------------------------------

| Id  |  Operation                                              | Name                                    | Rows     | Bytes | Cost (%CPU)| Time       |

---------------------------------------------------------------------------------------------------------------------

|   0 | SELECT  STATEMENT                             |                                             |            |           |      315 (100)|                 |

|   1 |  COUNT STOPKEY                                  |                                             |            |           |                     |                 |

|   2 |   VIEW                                                   |                                                |         1 |    180 |        315   (2)| 00:00:29 |

|   3 |    SORT ORDER BY STOPKEY                 |                                           |         1 |    151 |        315   (2)| 00:00:29 |

|   4 |     HASH UNIQUE                                    |                                              |         1 |    151 |        314   (1)| 00:00:29 |

|   5 |      FILTER                                              |                                              |            |           |                      |                |

|   6 |       NESTED LOOPS OUTER                   |                                           |         1 |    151 |         313   (1)| 00:00:29 |

|   7 |        NESTED LOOPS                              |                                            |         1 |      86 |           35   (0)| 00:00:04 |

|   8 |         TABLE ACCESS BY INDEX ROWID | LB_T_XXXX_PROVIDER            |        1 |      61 |           34    (0)| 00:00:04 |

|   9 |          INDEX RANGE SCAN                     | IDX_LB_T_XXXX_PROVIDER_003 | 183 |         |             3    (0)| 00:00:01 |

|  10 |         TABLE ACCESS BY INDEX ROWID | LA_XXXX                                 |         1 |      25 |            1    (0)| 00:00:01 |

|  11 |          INDEX UNIQUE SCAN                   | PK_LA_XXXX                           |         1 |           |            0    (0)|                 |

|  12 |        VIEW PUSHED PREDICATE            | LB_T_XXXX                             |         1 |      65 |         278   (1)| 00:00:26 |

|  13 |         MERGE JOIN OUTER                     |                                                |         1 |      64 |         278   (1)| 00:00:26 |

|  14 |          TABLE ACCESS BY INDEX ROWID| XXXX_SUPPLIER                     |         1 |      45 |        146    (0)| 00:00:14 |

|  15 |           INDEX FULL SCAN                       | PK_XXX_SUPPLIER                 |         1 |           |        145    (0)| 00:00:14 |

|  16 |          SORT JOIN                                  |                                                | 17998 |   333K|         132   (2)| 00:00:12 |

|  17 |           VIEW                                          |                                               | 17998 |   333K|         131   (1)| 00:00:12 |

|  18 |            SORT GROUP BY                       |                                                | 17998 |   544K|         131   (1)| 00:00:12 |

|  19 |             TABLE ACCESS FULL                | XXXX_SUPPLIER_CONTACT     | 30058 |   909K|         130   (0)| 00:00:12 |

---------------------------------------------------------------------------------------------------------------------

    此执行计划发生了严重的估算错误

       其实还有一种方法：使用awrsqrpi.sql和awrsqrpt.sql可以做到，该方法不在本文论坛之列。

4、小结

     通过上面查询结果可以得出，SQLID为的SQL语句，在AWR中总共保存有3种执行计划，但在在8月5日的9：00—09：30时第2节点上该语句的执行计划只有一种，选择了错误的执行计划。原因为统计信息不正确导致Rows估算错误，从而选择错误的执行计划。

    本文为“踩点”所作，转载请说明出处。谢谢合作！