通过hash_value获取sql语句执行计划

当我们没有权限访问业务表，但是需要查看shared pool中部分sql语句的执行计划，原则上来说，查询v$sql_plan视图结合hash_value可以实现，但是因为这个是表格形式，看起来不太美观，和我们长看的执行计划有一定的出入，这里提供两个脚本，实现查看该种情况下的执行计划。
oracle 9i

[oracle@xifenfei ~]$ more get_plan.sql 
set pagesize 0
set linesize 150
set serveroutput on size 10000
col plan_table_output format a125
undefine hash_value
set verify off feedback off
var hash_value varchar2(20)
begin
  :hash_value := '&hash_value';
end;
/
insert into plan_table
      (statement_id,timestamp,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates
      )
select distinct hash_value,sysdate,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates
  from v$sql_plan
 where hash_value = :hash_value
/
col piece noprint
select distinct piece,sql_text from v$sqltext where hash_value = :hash_value order by piece
/
@?/rdbms/admin/utlxplp.sql
set linesize 80
set verify on feedback on pagesize 1000

oracle 10g/11g

[oracle@xifenfei ~]$ more get_plan.sql 
set pagesize 0
set linesize 150
set serveroutput on size 10000
col plan_table_output format a125
undefine hash_value
set verify off feedback off
var hash_value varchar2(20)
begin
  :hash_value := '&hash_value';
end;
/
insert into plan_table
      (statement_id,timestamp,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates,
       plan_id,OBJECT_ALIAS,DEPTH,PROJECTION,TIME,QBLOCK_NAME
      )
select distinct hash_value,sysdate,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates,
       :hash_value,OBJECT_ALIAS,DEPTH,PROJECTION,TIME,QBLOCK_NAME
  from v$sql_plan
 where hash_value = :hash_value
/
col piece noprint
select distinct piece,sql_text from v$sqltext where hash_value = :hash_value order by piece
/
@?/rdbms/admin/utlxplp.sql
set linesize 80
set verify on feedback on pagesize 1000

使用方法

SQL> SELECT hash_value FROM V$SQL WHERE SQL_TEXT 
  2  LIKE 'SELECT * FROM SYS.SMON_SCN_TIME';

HASH_VALUE
----------
3019898357

SQL> @get_plan.sql
Enter value for hash_value: 3019898357
SELECT * FROM SYS.SMON_SCN_TIME

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| SMON_SCN_TIME |     1 |  1163 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

补充说明
其实9i和10g/11g中得出执行计划的出入就是在plan_table表上
在9i中：plan_table表需要通过脚本创建并且授权

SQL> connect / as sysdba;
SQL> @?/rdbms/admin/utlxplan.sql;
SQL> create public synonym plan_table for plan_table; --建立同义词
SQL> grant all on plan_table to public;--授权所有用户

在10g/11g中：plan_table表系统自带，不需要创建。因为plan_table表中含有plan_id列，而得出执行计划时该列不能为空，所以上面脚本中对于10/11g数据库必须要填充plan_id值