【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

                                                 ROWID

     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.

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

其他相关文章

http://space.itpub.net/133735/viewspace-707229

上文中提到的 coe_xfr_sql_profile.sql 脚本

http://kerryosborne.oracle-guy.com/scripts/coe_xfr_sql_profile.sql

时间： 2024-08-25 22:40:28

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

查看SQL执行计划常用方法

不论是做为开发DBA还是维护DBA,总是或多或少地遇到SQL执行效率或者说SQL调优问题,查看执行计划是必须的.一般我们可以用3种方法查看: 一.explain plan for 举例就足以说明其用法 sys@ORCL> explain plan for 2 select sysdate from dual; Explained. sys@ORCL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -----------

存储过程中查看sql执行计划的方法

如果sql在存储过程中,用set autotrace traceonly的方法一般不易直接查看,本文尝试了两种方法搜集存储过程中的执行计划 一 explain plan方法 测试用的存储过程 declare   p varchar2(10) ; begin   p:='15%';   execute immediate 'explain plan for select  h.id,h.phone from test.test_his H where h.phone like :1'   usin

在Oracle中启用AutoTrace查看SQL执行计划

  通过以下方法可以把Autotrace的权限授予Everyone, 如果你需要限制Autotrace权限,可以把对public的授权改为对特定user的授权. D:oracleora92>sqlplus /nolog SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 6月 3 15:16:03 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL>

Oracle技术：如何使用ordered提示改变SQL执行计划

ORDERED提示强制Oracle按照From子句中表出现的顺序进行表连接. 通过ordered提示,可以避免CBO SQL解析过程中的表连接评估,从而避免Oracle产生错误的执行计划,或者强制Oracle按照我们指定的方式执行. 在很多时候,当我们清楚地了解数据结构和数据分布之后,就可以通过ORDERED提示来提高SQL性能. 通过以下例子我们来说明一下Ordered提示的作用. 1.不加Hints时SQL的执行计划 我们可以通过10053事件跟踪一下该SQL的解析: 查看Trace文件可以

dbms_shared_pool.purge 清理某个SQL执行计划

dbms_shared_pool.purge 清理某个SQL执行计划 在日常管理中,经常有让sql重新解析的需求,比如说使用了bind peeking,第一次绑定特定值的时候执行计划走的特别糟,因为绑定变量导致之后的语句不作重新解析,重用了最差的执行计划,这时候我们希望重新解析来得到一个相对好的执行计划,常见的方法有: a.alter system flush shared_pool; b.对语句中的对象做个ddl ; --只会重新生成一个子游标 c.重新收集统计信息 但是这些操作的影响都比较大

PostgreSQL Oracle兼容性之 - 锁定执行计划(Outline system)

背景 绑定SQL执行计划,大家一定会想到SQL HINT,通过HINT告诉优化器你要用什么访问方法,用什么JOIN方法,JOIN的顺序,驱动表等等. 但是SQL HINT有一定的弊端,它需要修改应用程序中的SQL语句,把SQL加上HINT. 对pg_hint_plan感兴趣的同学,可以参考我写到文档https://yq.aliyun.com/articles/57945 但是,有什么方法可以在不修改应用程序,不修改SQL的情况下,锁定SQL的执行计划呢? 锁定执行计划 要锁定执行计划,同样要用到

关于SQL执行计划错误导致临时表空间不足的问题_oracle

故障现象:临时表空间不足的问题已经报错过3次,客户也烦了,前两次都是同事添加5G的数据文件,目前已经达到40G,占用临时表空间主要是distinct 和group by 以及Union all 表数据量在200W左右,也不至于把40G的临时表空间撑爆. 原因分析:既然排序用不了这么多临时表空间应该是别的原因造成. 从包含故障时间段的AWR报告中可以看出这一阶段DBtime蛮高的,并且sql execute elapsed time 竟然占到了99.43%,可以断定是SQL语句引起的. 通过TOP

如何分析ORACLE的SQL执行计划 .

1,先举个例子: -------------------------------------------------------------------------------- | Id  | Operation                        | Name                    | Rows  | Byt -------------------------------------------------------------------------------

如何获取SQL执行计划

*********************************************************** ----1:获取"刚刚"的执行计划display_cursor *********************************************************** Explain plan命令在Oracle中,可以对后面的SQL语句进行直接的解析,将执行计划保存在一个plan_table的中间表中.之后通过dbms_xplan包的方法进行获取. s