基於AWR實現STATSPACK報告(4-等待事件)
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 基於AWR實現STATSPACK報告(5-TOPSQL)SQL
- 基於AWR實現STATSPACK報告(7-TOPSEGMENT)
- 基於AWR實現STATSPACK報告(2-系統效率)
- 基於AWR實現STATSPACK報告(3-RAC統計)
- 基於AWR實現STATSPACK報告(6-例項元件)元件
- 基於AWR實現STATSPACK報告(1-系統負載)負載
- 基於AWR實現STATSPACK報告(8-例項元件二)元件
- statspack中報告中的等待事件事件
- ASH, AWR , 等待事件事件
- wait等待事件及其處理方法 awr top5 報告AI事件
- 【Oracle】-【心境】【AWR】- 等待事件的基準時間Oracle事件
- AWR報告基礎操作
- Statspack報告分析—第四部分：Top 5 Wait Events 5大等待事件AI事件
- statspack 報告分析
- shell指令碼實現自動生成awr報告指令碼
- 基於等待事件的效能診斷事件
- 詳解statspack 報告
- 關於類似於awr的效能分析報告
- 基於等待事件的效能診斷(轉)事件
- statspack報告分析摘錄
- Statspack分析報告說明
- Statspack分析報告詳解
- 對於AWR報告的幾個片段分析。
- Oracle生成awr報告Oracle
- AWR解析報告分析
- mysql-awr報告MySql
- Oracle 生成awr報告Oracle
- oracle效能awr報告Oracle
- 【AWR】Oracle批量生成awr報告指令碼Oracle指令碼
- Statspack分析報告詳解 (zt)
- Statspack分析報告詳解(1)
- Statspack分析報告詳解(2)
- Statspack分析報告詳解(3)
- Statspack分析報告詳解(4)
- Statspack分析報告詳解(轉)
- 基於spring實現事件驅動Spring事件
- 手工生成AWR分析報告
- Oracle AWR報告大綱Oracle