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>-> 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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-701150/