使用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語句執行計劃的功能。注意該檢視查詢的有效性和侷限性。

使用V$SQL_PLAN檢視

使用V$SQL_PLAN檢視獲取曾經執行過的SQL語句執行計劃

相關文章