获取cpu time和wait time变化趋势的2个脚本

1. 收集数据库所有非空闲等待事件的等待时间之和。

set pages 9999;
set linesize 120
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select
    oldsn.snap_time b_time,
    newsn.snap_time e_time,
   (newsn.snap_time-oldsn.snap_time)*86400 "Elapsed time(s)" ,
    round(sum(newevent.time_waited_micro - nvl(oldevent.time_waited_micro,0))/1000000) wait_time
from
   perfstat.stats$system_event oldevent,
   perfstat.stats$system_event newevent,
   (select snap_id,snap_time,rownum rn from perfstat.stats$snapshot where to_char(snap_time,'mi')='00'  order by 1) newsn,
   (select snap_id,snap_time,rownum rn from perfstat.stats$snapshot where to_char(snap_time,'mi')='00'  order by 1) oldsn
where
   newevent.snap_id = newsn.snap_id
and
   oldevent.snap_id = oldsn.snap_id
and
   oldsn.rn = newsn.rn-1
and
   newevent.event = oldevent.event
and
   newevent.event not in (select event from stats$idle_event)
and
   oldevent.event not in (select event from stats$idle_event)
group by
oldsn.snap_time,newsn.snap_time;

2.收集数据库cpu time相关信息

set pages 9999;
set linesize 120
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select
    oldsn.snap_time b_time,
    newsn.snap_time e_time,
    (newsn.snap_time-oldsn.snap_time)*86400 "interval",
    round((newcputime.value-oldcputime.value)/100) "Elapsed time(s) ",
    newparsetime.value-oldparsetime.value "parse time cpu",
    newrecurtime.value-oldrecurtime.value "recursive cpu usage",
    round(((newusercommit.value-oldusercommit.value) + (newuserroll.value-olduserroll.value))/3600,2)  "trans per second"
from
   perfstat.stats$sysstat oldcputime,
   perfstat.stats$sysstat newcputime,
   perfstat.stats$sysstat oldparsetime,
   perfstat.stats$sysstat newparsetime,
   perfstat.stats$sysstat oldrecurtime,
   perfstat.stats$sysstat newrecurtime,
   perfstat.stats$sysstat oldusercommit,
   perfstat.stats$sysstat newusercommit,
   perfstat.stats$sysstat olduserroll,
   perfstat.stats$sysstat newuserroll,
  (select snap_id,snap_time,rownum rn from perfstat.stats$snapshot where to_char(snap_time,'mi')='00'  order by 1) newsn,
  (select snap_id,snap_time,rownum rn from perfstat.stats$snapshot where to_char(snap_time,'mi')='00'  order by 1) oldsn
where
   newcputime.snap_id = newsn.snap_id
and
   newparsetime.snap_id = newsn.snap_id
and
   newrecurtime.snap_id = newsn.snap_id
and
   newusercommit.snap_id = newsn.snap_id
and
   newuserroll.snap_id = newsn.snap_id
and
   oldcputime.snap_id = oldsn.snap_id
and
   oldparsetime.snap_id = oldsn.snap_id
and
   oldrecurtime.snap_id = oldsn.snap_id
and
   oldusercommit.snap_id = oldsn.snap_id
and
   olduserroll.snap_id = oldsn.snap_id
and
   oldsn.rn = newsn.rn-1
and
  oldcputime.name = 'CPU used by this session'
and
  newcputime.name = 'CPU used by this session'
and
  oldparsetime.name = 'parse time cpu'
and
  newparsetime.name = 'parse time cpu'
and
  oldrecurtime.name = 'recursive cpu usage'
and
  newrecurtime.name = 'recursive cpu usage'
and
  newusercommit.name = 'user commits'
and
  oldusercommit.name ='user commits'
and
   newuserroll.name = 'user rollbacks'
and
   olduserroll.name = 'user rollbacks'
--and
--   (newcputime.value-oldcputime.value) > 0
--and
--   (newparsetime.value-oldparsetime.value) > 0
order by 1;

      根据以上两个sql获取相关的cpu time和wait time变化趋势，可以画出相关的趋势图，分析数据库性能变化趋势。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-557941/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-557941/