【Oracle】如何查看sql 执行计划的历史变更

   今天中午，突然接收到active session 数目飙高的报警，查看数据库，对于一个oltp 类型的查询本应该走index range scan 却变成全部是 direct path read ，所有的sql 走了全表扫描。悲剧的是那个表是一个历史表 185g。。故造成了许多session堆积，前台应用受到影响。回到问题本身，如果查看sql执行计划的变更？？

oracle 10g 以后可以通过下面的三个视图查询到sql执行计划的历史信息：

dba_hist_sql_plan

dba_hist_sqlstat

dba_hist_snapshot

查看语句的历史执行信息，是否发生变化，何时发生了变化。如果发生了变化,找出以前的执行计划,与当前的执行计划进行对比,有什么不同。

使用如下sql 可以发现某个sql的执行计划什么时候发生了变化！

select distinct sql_id,plan_hash_value,to_char(timestamp,'yyyymmdd hh24:mi:ss')  timestamp

from dba_hist_sql_plan 

where sql_id='68wnxdjxwwn2h' order by timestamp;

sql_id        plan_hash_value timestamp

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

68wnxdjxwwn2h       235510920 20111020 21:25:23

68wnxdjxwwn2h      1542630049 20120612 11:57:23

68wnxdjxwwn2h      2754593971 20120612 12:43:34

查看出来执行计划的变化之后 可以使用如下sql查看发生了那些变化！

col options for a15

col operation for a20

col object_name for a20

select plan_hash_value,id,operation,options,object_name,depth,cost,to_char(timestamp,'yyyymmdd hh24:mi:ss')

    from dba_hist_sql_plan  

    where sql_id ='68wnxdjxwwn2h' 

    and plan_hash_value in (1542630049,2754593971,2620382595)

    order by id,timestamp;

plan_hash_value         id operation             options         object_name                 cost to_char(timestamp

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

      235510920          0 select statement                                                    39 20111020 21:25:23

      235510920          1 nested loops                                                           20111020 21:25:23

      235510920          2 nested loops                                                        39 20111020 21:25:23

      235510920          3 view                                                                11 20111020 21:25:23

      235510920          4 window                sort pushed rank                               11 20111020 21:25:23

      235510920          5 table access          full            c_etl_data_validity           10 20111020 21:25:23

      235510920          6 partition list        iterator                                       2 20111020 21:25:23

      235510920          7 index                 range scan      idx_c_mem_xxxxxxxx_id          2 20111020 21:25:23

      235510920          8 table access          by local index  c_mem_xxxxxxxx_fatdt0          4 20111020 21:25:23

                                                 rowid

     1542630049          0 select statement                                                  7854 20120612 11:57:23 

     1542630049          1 nested loops                                                      7854 20120612 11:57:23

     1542630049          2 view                                                                28 20120612 11:57:23

     1542630049          3 window                sort pushed rank                              28 20120612 11:57:23

     1542630049          4 table access          full            c_etl_data_validity           27 20120612 11:57:23

     1542630049          5 partition list        iterator                                    7826 20120612 11:57:23

     1542630049          6 table access          full            c_mem_xxxxxxxx_fatdt0       7826 20120612 11:57:23

     2754593971          0 select statement                                                    43 20120612 12:43:34

     2754593971          1 px coordinator                                                         20120612 12:43:34

     2754593971          2 px send               qc (random)     :tq10001                         20120612 12:43:34

     2754593971          3 nested loops                                                           20120612 12:43:34

     2754593971          4 nested loops                                                        43 20120612 12:43:34

     2754593971          5 buffer                sort                                             20120612 12:43:34

     2754593971          6 px receive                                                             20120612 12:43:34

     2754593971          7 px send               broadcast       :tq10000                         20120612 12:43:34

     2754593971          8 view                                                                28 20120612 12:43:34

     2754593971          9 window                sort pushed rank                              28 20120612 12:43:34

     2754593971         10 table access          full            c_etl_data_validity           27 20120612 12:43:34

     2754593971         11 px partition list     iterator                                       2 20120612 12:43:34

     2754593971         12 index                 range scan      idx_c_mem_xxxxxxxx_id          2 20120612 12:43:34

     2754593971         13 table access          by local index  c_mem_xxxxxxxx_fatdt0         15 20120612 12:43:34

     2620382595          0 select statement                                                     5 20120612 18:27:37

     2620382595          1 table access          by index rowid  c_mem_xxxxxxxx_bakup           5 20120612 18:27:37

     2620382595          2 index                 range scan      idx_c_mem_xxxxxxxx_ba          3 20120612 18:27:37

                                                                 kup_id

33 rows selected.

 <b>从上面的结果中可以看出 执行计划在11:57 时出现改变 c_mem_xxxxxxxx_fatdt0有之前的index range scan 变为了full table scan！！</b>

其他相关文章

<a href="http://space.itpub.net/133735/viewspace-707229">http://space.itpub.net/133735/viewspace-707229</a>

<a href="http://kerryosborne.oracle-guy.com/.s/coe_xfr_sql_profile.sql">http://kerryosborne.oracle-guy.com/scripts/coe_xfr_sql_profile.sql</a>