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

素颜马尾好姑娘i 2023-01-18 09:04 87阅读 0赞

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

\-----------------------------------------------------------------------------------

\-----------------------------------------------------------------------------------

| 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值

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle SQL语句的执行计划分析与优化指导

相关 Oracle-如何查看sql语句的执行计划

相关 oracle根据sql_id查sql语句,根据SQL Id获得SQL语句的执行计划

相关 get_hash_value oracle,通过hash_value获取sql语句执行计划

相关 oracle sql执行计划查看

相关通过分析SQL语句的执行计划优化SQL(总结)

相关 oracle获取的执行计划区别及获取真实执行计划

相关使用 EXPLAIN PLAN 获取SQL语句执行计划

相关 Oracle通过执行计划查看查询语句是否使用索引

相关 Oracle 查看sql语句执行计划方法