今天中午，突然接收到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！！

上文中提到的指令碼

【Oracle】如何檢視sql 執行計劃的歷史變更

相關文章