查看sql 执行计划的历史变更 DBA_HIST_SQL_PLAN DBA_HIST_SQLSTAT DBA_HIST_SNAPSHOT 查看语句的历史执行信息，是否发生变化，何时发生了变化。如果发生了变化,找出以前的执行计划,与当前的执行计划进行对比,有什么不同。 使用如下sql 可以发现某个sql的执行计划什么时候发生了变化！ select distinct SQL_ID, PLAN_HASH_VALUE, to_char(TIMESTAMP, 'yyyymmdd hh34: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 hh34:mi:ss') from DBA_HIST_SQL_PLAN where sql_id = '68wnxdjxwwn2h' and plan_hash_value in (1542630049, 2754593971, 2620382595) order by TIMESTAMP ,ID; 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！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
