statspack使用方法小结

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
;