当我们没有权限访问业务表,但是需要查看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值