oracle statspack的原理,安装,使用,生成报告等过程和方法等等可以参照下面的链接,非常详细的讲解。
http://www.itpub.net/thread-1283478-1-1.html
sprepins.sql里的SQL加工了一下,然后便于拷贝到EXCEL里
prompt ※※※※※実行SQL取得※※※※※
prompt
--
-- SQL statements ordered by Elapsed
--
prompt ElapsedTime (s)^ Executions^ Elap perExec (s)^%Total ^ CPUTime (s)^Physical Reads^Old Hash Value
prompt ----------^------------^----------^------^----------^---------------^----------
col aa format a82 heading -
'Elapsed Elap per CPU Old| Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value |---------- ------------ ---------- ------ ---------- --------------- ----------'
column ab format a10000 newline;
with temp as
( select *
from perfstat.stats$temp_sqlstats
where delta_elapsed_time/&&ustos >= 30
)
select /*+ orderd use_nl (topn st) */
decode(st.piece
,0,
'1' || '^' || lpad(to_char(delta_elapsed_time/&&ustos,'999990.00')
, 10) || '^' ||
lpad(to_char(delta_executions,'999,999,999')
,12) || '^' ||
lpad(decode(delta_executions
,0 , ' '
,to_char(delta_elapsed_time/&&ustos/delta_executions,'999990.00'))
,10) || '^' ||
lpad(decode(:dbtim, 0, ' '
,to_char(100*delta_elapsed_time/:dbtim, '990.0')
)
, 6) ||'^'||
lpad(to_char(delta_cpu_time/&&ustos,'99990.00')
, 10) || '^' ||
lpad(to_char(delta_disk_reads,'99,999,999,999')
,15) || '^' ||
lpad(topn.old_hash_value,10) || '^' ||
decode(topn.module, null, rpad('2' || '^'||' ',82) || '3' || '^' || '"' || st.sql_text || '"'
,rpad('2' || '^'||'Module: '||topn.module,82) || '3' || '^' || '"' || st.sql_text || '"')
,'3' || '^' ||'"' || st.sql_text || '"')aa
from temp topn
, stats$sqltext st
where st.old_hash_value(+) = topn.old_hash_value
and st.text_subset(+) = topn.text_subset
order by topn.delta_elapsed_time desc, topn.old_hash_value,st.piece;
prompt
prompt ※※※※※実行計画取得※※※※※
prompt
prompt Operation^ PHV/Object Name^Rows ^Bytes ^Cost^Old Hash Value
prompt ----------^------------^----------^------^----------^---------------^----------
column hv noprint;
break on hv skip 1;
select
rpad(substr(lpad(' ',1*(sp.depth-1))||sp.operation||
decode(sp.options, null,'',' '||sp.options), 1, 32), 33, ' ')||'^'||
rpad(decode(id, 0, '----- '||to_char(sp.plan_hash_value)||' -----'
, substr(decode(substr(sp.object_name, 1, 7), 'SYS_LE_', null, sp.object_name)
||' ',1, 20)), 21, ' ')||'^'||
lpad(decode(sp.cardinality,null,' ',
decode(sign(sp.cardinality-1000), -1, sp.cardinality||' ',
decode(sign(sp.cardinality-1000000), -1, trunc(sp.cardinality/1000)||'K',
decode(sign(sp.cardinality-1000000000), -1, trunc(sp.cardinality/1000000)||'M',
trunc(sp.cardinality/1000000000)||'G')))), 7, ' ') || '^' ||
lpad(decode(sp.bytes,null,' ',
decode(sign(sp.bytes-1024), -1, sp.bytes||' ',
decode(sign(sp.bytes-1048576), -1, trunc(sp.bytes/1024)||'K',
decode(sign(sp.bytes-1073741824), -1, trunc(sp.bytes/1048576)||'M',
trunc(sp.bytes/1073741824)||'G')))), 6, ' ') || '^' ||
lpad(decode(sp.cost,null,' ',
decode(sign(sp.cost-10000000), -1, sp.cost||' ',
decode(sign(sp.cost-1000000000), -1, trunc(sp.cost/1000000)||'M',
trunc(sp.cost/1000000000)||'G'))), 8, ' ') || '^' ||
temp.old_hash_value cc
, temp.old_hash_value hv
from stats$sql_plan sp
, ( select spu.plan_hash_value,spu.old_hash_value
from stats$sql_plan_usage spu
, ( select *
from perfstat.stats$temp_sqlstats
where delta_elapsed_time/&&ustos >= 30
) topn
where spu.old_hash_value = topn.old_hash_value
and spu.text_subset = topn.text_subset
and spu.snap_id between :bid and :eid
and spu.dbid = :dbid
and spu.instance_number = :inst_num
and spu.plan_hash_value > 0
group by spu.plan_hash_value, spu.old_hash_value) temp
where sp.plan_hash_value = temp.plan_hash_value
order by temp.old_hash_value,sp.plan_hash_value, sp.id
;