ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 基于AWR实现STATSPACK报告(4-等待事件)

基于AWR实现STATSPACK报告(4-等待事件)

原创 Linux操作系统 作者:redhouser 时间:2011-07-01 16:52:31 0 删除 编辑

Wait Events  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> s - second, cs - centisecond,  ms - millisecond, us - microsecond
-> %Timeouts:  value of 0 indicates value was < .5%.  Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
                                                                    Avg
                                                %Time Total Wait   wait    Waits
Event                                    Waits  -outs   Time (s)   (ms)     /txn
--------------------------------- ------------ ------ ---------- ------ --------
gc buffer busy                         138,659      1     11,485     83      1.4
db file scattered read                 663,066      0        380      1      6.7
db file sequential read                416,636      0        348      1      4.2
gc cr multi block request            1,176,466      0        240      0     11.8
db file parallel read                  180,453      0        178      1      1.8
gc current block 2-way                 323,241      0        150      0      3.2
log file sync                           98,293      0        102      1      1.0
db file parallel write                  36,723      0         74      2      0.4
gc cr block 2-way                      121,117      0         64      1      1.2
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT e.event_name,
       e.total_waits - nvl(b.total_waits, 0) waits,
       decode((e.total_waits - nvl(b.total_waits, 0)),
              0,
              to_number(NULL),
              100 * (e.total_timeouts - nvl(b.total_timeouts, 0)) /
              (e.total_waits - nvl(b.total_waits, 0))) pctto,
       (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)) / 1000) /
              (e.total_waits - nvl(b.total_waits, 0))) avwt_fmt,
       (e.total_waits - nvl(b.total_waits, 0)) / &tran txwaits,
       decode(e.wait_class, 'Idle', 0, 99) idle
  FROM dba_hist_system_event b, dba_hist_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_id(+) = e.event_id
   AND e.total_waits > nvl(b.total_waits, 0)
   AND e.event_name NOT IN ('smon timer',
                            'pmon timer',
                            'dispatcher timer',
                            'dispatcher listen timer',
                            'rdbms ipc message')
   AND (&timstat IN ('FALSE', 'INCONSISTENT') -- No valid timings - use # waits to filter
        OR (&timstat = 'TRUE' -- Valid timings - only show if time > threshold ms
        AND ((e.time_waited_micro - nvl(b.time_waited_micro, 0)) / 1000 / 1000) >=
        &total_event_time_s_th))
 ORDER BY idle, TIME DESC, waits DESC;
其中:
tran-->ucom+urol
SELECT sum(e.VALUE) - sum(b.VALUE)
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name in('user commits','user rollbacks');

timstat-->
case when (consistant begin and end) then
  SELECT VALUE
  FROM dba_hist_parameter t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid
   AND t.parameter_name = 'timed_statistics';
else
 'INCONSISTENT'
end case

total_event_time_s_th-->.001
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Background Wait Events  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> %Timeouts:  value of 0 indicates value was < .5%.  Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
                                                                    Avg
                                                %Time Total Wait   wait    Waits
Event                                    Waits  -outs   Time (s)   (ms)     /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file parallel write                  36,850      0         75      2      0.4
log file parallel write                 98,295      0         60      1      1.0
events in waitclass Other              121,386     33         40      0      1.2
control file sequential read             7,314      0         12      2      0.1
Log archive I/O                            354      0          4     12      0.0
control file parallel write              1,421      0          4      3      0.0
log file sequential read                   299      0          2      6      0.0
os thread startup                           10      0          0     31      0.0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT e.event_name,
       e.total_waits - nvl(b.total_waits, 0) waits,
       decode((e.total_waits - nvl(b.total_waits, 0)),
              0,
              to_number(NULL),
              100 * (e.total_timeouts - nvl(b.total_timeouts, 0)) /
              (e.total_waits - nvl(b.total_waits, 0))) pctto,
       (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)) / 1000) /
              (e.total_waits - nvl(b.total_waits, 0))) avwt_fmt,
       (e.total_waits - nvl(b.total_waits, 0)) / &tran txwaits,
       decode(e.wait_class, 'Idle', 0, 99) idle
  FROM dba_hist_system_event b, dba_hist_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_id(+) = e.event_id
   AND e.total_waits > nvl(b.total_waits, 0)
   AND (&timstat IN ('FALSE', 'INCONSISTENT') -- No valid timings - use # waits to filter
        OR (&timstat = 'TRUE' -- Valid timings - only show if time > 1ms
        AND ((e.time_waited_micro - nvl(b.time_waited_micro, 0)) / 1000 / 1000) >=
        &total_event_time_s_th))
 ORDER BY idle, TIME DESC, waits DESC;
其中:
***该sql中没有体现Background条件***
tran-->ucom+urol
SELECT sum(e.VALUE) - sum(b.VALUE)
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name in('user commits','user rollbacks');

timstat-->
case when (consistant begin and end) then
  SELECT VALUE
  FROM dba_hist_parameter t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid
   AND t.parameter_name = 'timed_statistics';
else
 'INCONSISTENT'
end case

total_event_time_s_th-->.001
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

Wait Event Histogram  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
                           Total ----------------- % of Waits ------------------
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
Backup: sbtbackup             1                                            100.0
Backup: sbtend                1  100.0
buffer deadlock               1  100.0
control file parallel writ 1434   69.8  18.8   5.9   3.7    .8    .3    .8
control file sequential re 9423   94.7   1.7   2.0   1.0    .2    .1    .4    .0
cursor: mutex X              38  100.0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***dba_hist_*中不存在v$event_histogram数据,在AWR中无法实现类似功能.
with event_histogram as (
  select /*+ inline ordered index(h) index(se) */
         h.snap_id
       , se.event
       , sum(h.wait_count) total_waits
       , sum(case when (h.wait_time_milli = 1)
                  then (nvl(h.wait_count,0)) else 0 end) to1
       , sum(case when (h.wait_time_milli = 2)
                  then (nvl(h.wait_count,0)) else 0 end) to2
       , sum(case when (h.wait_time_milli = 4)
                  then (nvl(h.wait_count,0)) else 0 end) to4
       , sum(case when (h.wait_time_milli = 8)
                  then (nvl(h.wait_count,0)) else 0 end) to8
       , sum(case when (h.wait_time_milli = 16)
                  then (nvl(h.wait_count,0)) else 0 end) to16
       , sum(case when (h.wait_time_milli = 32)
                  then (nvl(h.wait_count,0)) else 0 end) to32
       , sum(case when (h.wait_time_milli between 64 and 1024)
                  then (nvl(h.wait_count,0)) else 0 end) to1024
       , sum(case when (1024 < h.wait_time_milli)
                  then (nvl(h.wait_count,0)) else 0 end) over
       , decode(i.event, null, 0, 99)                    idle
    from stats$event_histogram h
       , stats$system_event    se
       , stats$idle_event      i
   where se.event_id           = h.event_id
     and se.snap_id            = h.snap_id
     and i.event(+)            = se.event
     and se.instance_number    = :inst_num
     and se.dbid               = :dbid
     and h.instance_number     = :inst_num
     and h.dbid                = :dbid
     and '&event_histogram' = 'Y'
   group by h.snap_id
       , se.event
       , decode(i.event, null, 0, 99)
  )
select e.event
     , lpad(case
              when e.total_waits - nvl(b.total_waits,0) <= 9999
                   then to_char(e.total_waits - nvl(b.total_waits,0))||' '
              when trunc((e.total_waits - nvl(b.total_waits,0))/1000) <= 9999
                   then to_char(trunc((e.total_waits - nvl(b.total_waits,0))/1000))||'K'
              when trunc((e.total_waits - nvl(b.total_waits,0))/1000000) <= 9999
                   then to_char(trunc((e.total_waits - nvl(b.total_waits,0))/1000000))||'M'
              when trunc((e.total_waits - nvl(b.total_waits,0))/1000000000) <= 9999
                   then to_char(trunc((e.total_waits - nvl(b.total_waits,0))/1000000000))||'G'
              when trunc((e.total_waits - nvl(b.total_waits,0))/1000000000000) <= 9999
                   then to_char(trunc((e.total_waits - nvl(b.total_waits,0))/1000000000000))||'T'
              else substr(to_char(trunc((e.total_waits - nvl(b.total_waits,0))/1000000000000000))||'P', 1, 5) end
            , 5, ' ')                                                              total_waits
     , substr(to_char(decode(e.to1-nvl(b.to1,0),0,to_number(NULL),(e.to1-nvl(b.to1,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to1
     , substr(to_char(decode(e.to2-nvl(b.to2,0),0,to_number(NULL),(e.to2-nvl(b.to2,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to2
     , substr(to_char(decode(e.to4-nvl(b.to4,0),0,to_number(NULL),(e.to4-nvl(b.to4,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to4
     , substr(to_char(decode(e.to8-nvl(b.to8,0),0,to_number(NULL),(e.to8-nvl(b.to8,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to8
     , substr(to_char(decode(e.to16-nvl(b.to16,0),0,to_number(NULL),(e.to16-nvl(b.to16,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to16
     , substr(to_char(decode(e.to32-nvl(b.to32,0),0,to_number(NULL),(e.to32-nvl(b.to32,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to32
     , substr(to_char(decode(e.to1024-nvl(b.to1024,0),0,to_number(NULL),(e.to1024-nvl(b.to1024,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to1024
     , substr(to_char(decode(e.over-nvl(b.over,0),0,to_number(NULL),(e.over-nvl(b.over,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) over
  from ( select *
           from event_histogram
          where snap_id          = :bid) b
     , ( select *
           from event_histogram
          where snap_id          = :eid) e
 where b.event(+) = e.event
   and (e.total_waits - nvl(b.total_waits,0)) > 0
 order by e.idle, e.event;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

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

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    826166