通常我们查看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语句执行计划的功能。注意该视图查询的有效性和局限性。
转载：http://blog.itpub.net/519536/viewspace-694274
