使用V$SQL_PLAN檢視獲取曾經執行過的SQL語句執行計劃
通常我們檢視SQL語句的執行計劃都是通過EXPLAIN PLAN或者AUTOTRACE來完成。但是這些檢視方法有一個限制，它們都是人為觸發而產生的，無法獲得資料庫系統中曾經執行過的SQL語句執行計劃。
V$SQL_PLAN檢視彌補了這個這個功能缺陷。使用這個檢視可以獲得當前資料庫例項library cache中儲存的SQL執行計劃。由於是在記憶體中儲存的，因此這個檢視所能檢視的資訊也有限制，如果資訊已被換出記憶體，將無法檢視到。
這裡給出V$SQL_PLAN檢視的使用方法。
1.準備測試環境
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> create table t (x varchar2(8));
Table created.
sec@ora10g> insert into t values ('secooler');
1 row created.
sec@ora10g> select * from t where x = 'secooler';
X
--------
secooler
sec@ora10g> create index i_t on t(x);
Index created.
2.執行SQL語句
sec@ora10g> select * from t where x = 'secooler';
X
--------
secooler
3.通過V$SQL檢視獲取SQL語句的HASH_VALUE
sec@ora10g> select hash_value,address,sql_text from v$sql where sql_text like '%secooler%';
HASH_VALUE ADDRESS
---------- --------
SQL_TEXT
------------------------------------------------------------------------------
2119188747 2F9FD1F8
select hash_value,address,sql_text from v$sql where sql_text like '%secooler%'
1200605713 2FAFAA68
select * from t where x = 'secooler'
可見，剛剛執行過的兩條包含“secooler”關鍵字的SQL已經顯示出來。最後面的SQL語句便是我們要找的SQL。
4.通過查詢V$SQL_PLAN檢視構造執行計劃
select '| Operation |Object Name | Rows | Bytes| Cost |'
as "Explain Plan in library cache:" from dual
union all
select rpad('| '||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'||
rpad(decode(id, 0, '----------------------------',
substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
||' ',1, 30)), 31, ' ')||'|'|| lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
from v$sql_plan sp
where sp.hash_value=&hash_value;
Enter value for hash_value: 1200605713
old 22: where sp.hash_value=&hash_value
new 22: where sp.hash_value=1200605713
Explain Plan in library cache:
-------------------------------------------------------------------------------------------
| Operation |Object Name | Rows | Bytes| Cost |
| SELECT STATEMENT |---------------------------- | | | 1 |
| INDEX RANGE SCAN |I_T | 1 | 9 | 1 |
可見，記憶體library cache中存放的SQL執行計劃已經盡收眼底。
5.驗證執行計劃
這裡使用AUTOTRACE功能對上述SQL語句的執行計劃進行驗證。
sec@ora10g> set autotrace on
sec@ora10g> select * from t where x = 'secooler';
X
--------
secooler
Execution Plan
----------------------------------------------------------
Plan hash value: 2616361825
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_T | 1 | 6 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"='secooler')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
執行計劃與V$SQL_PLAN中記錄的內容一致。
6.小結
V$SQL_PLAN檢視的使用體現了在記憶體中檢視已被執行過的SQL語句執行計劃的功能。注意該檢視查詢的有效性和侷限性。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28260988/viewspace-749055/，如需轉載，請註明出處，否則將追究法律責任。