檢視sql 執行計劃的歷史變更
檢視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 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！
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 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！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2142876/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Oracle】如何檢視sql 執行計劃的歷史變更OracleSQL
- 檢視歷史執行計劃
- Oracle檢視sql_id 的歷史執行計劃OracleSQL
- 【最佳化】檢視歷史的執行計劃之檢視AWR報告
- 檢視sql執行計劃SQL
- 如何檢視SQL的執行計劃SQL
- 檢視SQL的執行計劃方法SQL
- Oracle 檢視SQL的執行計劃OracleSQL
- 透過SQL_ID檢視SQL歷史執行資訊SQL
- 通過SQL_ID檢視SQL歷史執行資訊SQL
- 【Explain Plan】檢視SQL的執行計劃AISQL
- 使用PL/SQL檢視執行計劃SQL
- 檢視sql執行計劃--set autotraceSQL
- 10G中檢視歷史執行計劃資訊
- sql執行計劃變更和刪除快取中執行計劃的方法SQL快取
- [oracle] 查詢歷史會話、歷史執行計劃Oracle會話
- oracle 執行計劃變更Oracle
- Oracle檢視正在執行的SQL以及執行計劃分析OracleSQL
- 檢視sql執行計劃方法彙總SQL
- MySQL 5.7 檢視理解SQL執行計劃MySql
- Oracle SQL執行計劃歷史資訊表DBA_HIST_SQL_PLAN的使用OracleSQL
- 檢視執行計劃
- Oracle中檢視sql命令歷史,檢視rman命令歷史OracleSQL
- 根據SQL_ID檢視執行計劃SQL
- 多種方法檢視Oracle SQL執行計劃OracleSQL
- 檢視sql執行計劃--set autotrace [final]SQL
- oracle10g 檢視SQL執行計劃OracleSQL
- 透過查詢檢視sql執行計劃SQL
- Oracle檢視歷史TOP SQLOracleSQL
- ORACLE執行計劃的檢視Oracle
- 檢視執行計劃的方法
- 檢視SQL執行計劃的幾種常用方法YQSQL
- 檢視執行計劃（一）
- 檢視執行計劃（二）
- oracle實用sql(15)--檢視SQL執行計劃的順序OracleSQL
- 【檢視】使用V$SQL_PLAN檢視獲取曾經執行過的SQL語句執行計劃SQL
- 檢視Oracle SQL執行計劃方法比較、分析OracleSQL
- SQLPLUS檢視oracle sql執行計劃命令SQLOracle