脚本涉及:gethidpar.sql 获取隐含参数
gettrcname.sql 获取当前trace文件名称
getplan.sql 获取sql语句的执行计划,通过v$sql_plan视图。
gettrcname.sql 获取当前trace文件名称
getplan.sql 获取sql语句的执行计划,通过v$sql_plan视图。
gethidpar.sql内容如下:
set linesize 120
col name for a30
col value for a20
col describ for a60
col name for a30
col value for a20
col describ for a60
select x.ksppinm name, y.ksppstvl value, x.ksppdesc describ
from sys.x$ksppi x,sys.x$ksppcv y
where x.indx = y.indx
and x.ksppinm like '%&par%'
from sys.x$ksppi x,sys.x$ksppcv y
where x.indx = y.indx
and x.ksppinm like '%&par%'
/
gettrcname.sql 内容如下:
select
a.value || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' trace_file_name
from (select value from v$parameter where name = 'user_dump_dest')a,
(select substr(value,-6,1) symbol from v$parameter where name = 'user_dump_dest') b,
(select instance_name from v$instance) c,
(select spid from v$session s,v$process p,v$mystat m where s.paddr = p.addr and s.sid = m.sid and m.statistic#=0) d
select
a.value || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' trace_file_name
from (select value from v$parameter where name = 'user_dump_dest')a,
(select substr(value,-6,1) symbol from v$parameter where name = 'user_dump_dest') b,
(select instance_name from v$instance) c,
(select spid from v$session s,v$process p,v$mystat m where s.paddr = p.addr and s.sid = m.sid and m.statistic#=0) d
/
getplan.sql 内容如下:
set linesize 120
col operation format a55
col cost format 99999
col kbytes format 999999
col object format a25
set linesize 120
col operation format a55
col cost format 99999
col kbytes format 999999
col object format a25
select hash_value,child_number,
lpad(' ',2*depth) || operation || ' ' || options || decode(id,0,substr(optimizer,1,6) || 'Cost=' || to_char(cost)) operation,
object_name object,
cost,
round(bytes/1024) kbytes
from v$sql_plan
where hash_value in (
select a.sql_hash_value from v$session a,v$session_wait b
where a.sid = b.sid and b.event = '&waitevent')
order by hash_value,child_number,id;
/
lpad(' ',2*depth) || operation || ' ' || options || decode(id,0,substr(optimizer,1,6) || 'Cost=' || to_char(cost)) operation,
object_name object,
cost,
round(bytes/1024) kbytes
from v$sql_plan
where hash_value in (
select a.sql_hash_value from v$session a,v$session_wait b
where a.sid = b.sid and b.event = '&waitevent')
order by hash_value,child_number,id;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/607244/viewspace-765493/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/607244/viewspace-765493/