Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › AWR top 5 events

wed

3 积分	0 好友	0 主题

发消息

AWR top 5 events

1^#

发表于 2012-7-3 22:40:55 | 查看: 6754| 回复: 2

AWR TOP 5 EVENT怎么用sql 取出来呢？dba_hist_sys_time_model,dba_hist_snapshot

分享0

收藏0 回复只看该作者道具举报

psufnxk2000

2^#

发表于 2012-7-4 00:02:49

statspack中。找spreport脚本，再找到里面执行了@@sprepins ，在后面这个脚本中搜索 ‘top’.  找到：
--  Top N Wait Events

col idle    noprint;
col event format a41       heading 'Top &&top_n_events Timed Events|~~~~~~~~~~~~~~~~~~|Event' trunc;
col waits format 999,999,990  heading 'Waits';
col time    format 99,999,990 heading 'Time (s)';
col pctwtt format 999.9       heading '%Total|Call|Time';
col avwait format 99990       heading 'Avg|wait|(ms)';

select event
   , waits
   , time
   , avwait
   , pctwtt
  from (select event, waits, time, pctwtt, avwait
      from (select e.event                            event
                  , e.total_waits - nvl(b.total_waits,0)  waits
                  , (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000  time
                  , decode( (e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL)
                           , ( (e.time_waited_micro - nvl(b.time_waited_micro,0)) / &&ustoms )
                              / (e.total_waits - nvl(b.total_waits,0))
                           )       avwait
                  , decode(:tct_us, 0, 0, 100 * (e.time_waited_micro - nvl(b.time_waited_micro,0)) / (:tct_us)
                           )                            pctwtt
               from stats$system_event b
                  , stats$system_event e
            where b.snap_id(+)       = :bid
               and e.snap_id          = :eid
               and b.dbid(+)          = :dbid
               and e.dbid             = :dbid
               and b.instance_number(+)  = :inst_num
               and e.instance_number    = :inst_num
               and b.event(+)          = e.event
               and e.total_waits       > nvl(b.total_waits,0)
               and e.event not in (select event from stats$idle_event)
            union all
            select 'CPU time'                            event
                  , to_number(null)                      waits
                  , :tcpu/100                            time
                  , to_number(null)                      avwait
                  , decode( :tct_us, 0, 0
                        , 100 * (:tcpu*10000) / :tct_us
                        )                               pctwait
               from dual
            where :tcpu > 0
            )
      order by time desc, waits desc
   )
where rownum <= &&top_n_events;

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2012-7-5 11:08:13

以下是一个例子可以找出AWR 一天间隔内的 TOP EVENT :

select Day, Event_name, Total_wait from (
select day, event_name, sum(event_time_waited) total_wait,
row_number() over (partition by day order by sum(event_time_waited) desc) rn from (
SELECT to_date(to_char(begin_interval_time,'dd/mm/yyyy'),'dd/mm/yyyy') day,s.begin_interval_time, m.*
FROM (SELECT ee.instance_number, ee.snap_id, ee.event_name,
ROUND (ee.event_time_waited / 1000000) event_time_waited,
ee.total_waits,
ROUND ((ee.event_time_waited * 100) / et.total_time_waited,
1
) pct,
ROUND ((ee.event_time_waited / ee.total_waits) / 1000
) avg_wait
FROM (SELECT ee1.instance_number, ee1.snap_id, ee1.event_name,
ee1.time_waited_micro
- ee2.time_waited_micro event_time_waited,
ee1.total_waits - ee2.total_waits total_waits
FROM dba_hist_system_event ee1 JOIN dba_hist_system_event ee2
ON ee1.snap_id = ee2.snap_id + 1
AND ee1.instance_number = ee2.instance_number
AND ee1.event_id = ee2.event_id
AND ee1.wait_class_id <> 2723168908
AND ee1.time_waited_micro - ee2.time_waited_micro > 0
UNION
SELECT st1.instance_number, st1.snap_id,
st1.stat_name event_name,
st1.VALUE - st2.VALUE event_time_waited,
1 total_waits
FROM dba_hist_sys_time_model st1 JOIN dba_hist_sys_time_model st2
ON st1.instance_number = st2.instance_number
AND st1.snap_id = st2.snap_id + 1
AND st1.stat_id = st2.stat_id
AND st1.stat_name = 'DB CPU'
AND st1.VALUE - st2.VALUE > 0
) ee
JOIN
(SELECT et1.instance_number, et1.snap_id,
et1.VALUE - et2.VALUE total_time_waited
FROM dba_hist_sys_time_model et1 JOIN dba_hist_sys_time_model et2
ON et1.snap_id = et2.snap_id + 1
AND et1.instance_number = et2.instance_number
AND et1.stat_id = et2.stat_id
AND et1.stat_name = 'DB time'
AND et1.VALUE - et2.VALUE > 0
) et
ON ee.instance_number = et.instance_number
AND ee.snap_id = et.snap_id
) m
JOIN
dba_hist_snapshot s ON m.snap_id = s.snap_id
) group by day ,event_name
order by day desc, total_wait desc
)where rn < 6
DAY EVENT_NAME TOTAL_WAIT
--------- ---------------------------------------------------------------- ----------
05-JUL-12 control file parallel write 4
05-JUL-12 os thread startup 4
05-JUL-12 DB CPU 2
05-JUL-12 buffer busy waits 0
05-JUL-12 reliable message 0
04-JUL-12 DB CPU 34
04-JUL-12 os thread startup 25
04-JUL-12 control file parallel write 25
04-JUL-12 db file async I/O submit 9
04-JUL-12 log file parallel write 6
03-JUL-12 DB CPU 33
03-JUL-12 control file parallel write 25
03-JUL-12 os thread startup 24
03-JUL-12 db file async I/O submit 13
03-JUL-12 log file parallel write 4
02-JUL-12 DB CPU 64
02-JUL-12 control file parallel write 26
02-JUL-12 os thread startup 24
02-JUL-12 db file async I/O submit 12
02-JUL-12 resmgr:cpu quantum 6
01-JUL-12 DB CPU 65
01-JUL-12 control file parallel write 25
01-JUL-12 os thread startup 25
01-JUL-12 db file async I/O submit 15
01-JUL-12 log file parallel write 5
30-JUN-12 DB CPU 57
30-JUN-12 os thread startup 26
30-JUN-12 control file parallel write 25
30-JUN-12 db file async I/O submit 13
30-JUN-12 log file parallel write 4
29-JUN-12 DB CPU 31
29-JUN-12 control file parallel write 25
29-JUN-12 os thread startup 24
29-JUN-12 db file async I/O submit 7
29-JUN-12 log file parallel write 5
28-JUN-12 DB CPU 26
28-JUN-12 control file parallel write 25
28-JUN-12 os thread startup 24
28-JUN-12 db file async I/O submit 9
28-JUN-12 log file parallel write 5
27-JUN-12 DB CPU 43
27-JUN-12 control file parallel write 25
27-JUN-12 os thread startup 25
27-JUN-12 db file async I/O submit 9
27-JUN-12 log file parallel write 7
26-JUN-12 os thread startup 1
26-JUN-12 control file parallel write 1
26-JUN-12 ADR file lock 0
26-JUN-12 buffer busy waits 0
26-JUN-12 db file sequential read 0

复制代码

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册