基於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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- statspack、awr、addm,ash影片分享
- AWR報告基礎操作
- 基於等待事件的效能診斷(轉)事件
- 基於spring實現事件驅動Spring事件
- 【AWR】Oracle批量生成awr報告指令碼Oracle指令碼
- AWR TOP SQL實現SQL
- Solidity事件，等待事件Solid事件
- 基於Redis實現一套支援排隊等待的限流器Redis
- Oracle生成awr報告操作步驟Oracle
- 【TUNE_ORACLE】等待事件之等待事件類別Oracle事件
- ORACLE AWR效能報告和ASH效能報告的解讀Oracle
- 關於enq: TX - allocate ITL entry等待事件ENQ事件
- awr-----一份經典的負載很高的awr報告負載
- awr報告每天自動生成指令碼指令碼
- 12.2 如何單為PDB建立AWR報告
- 基於事件匯流排EventBus實現郵件推送功能事件
- 基於Guava API實現非同步通知和事件回撥GuavaAPI非同步事件
- [20201204]關於等待事件Log File Sync.txt事件
- Selenium等待事件Waits事件AI
- 使用EventNext實現基於事件驅動的業務處理事件
- 基於rust實現的事件系統，支援同步和非同步Rust事件非同步
- Oracle 12.2 physical standby備庫收集AWR報告Oracle
- 【AWR】Oracle資料庫建立awr基線Oracle資料庫
- 本機生成遠端資料庫AWR報告資料庫
- 【效能調優】Oracle AWR報告指標全解析Oracle指標
- read by other session等待事件Session事件
- log file sync等待事件事件
- ORACLE 常見等待事件Oracle事件
- latch等待事件彙總事件
- Latch free等待事件(轉)事件
- gc cr request等待事件GC事件
- 【等待事件】library cache pin事件
- 【等待事件】log file sync事件
- 達夢資料庫AWR報告日常管理方法資料庫
- 【TUNE_ORACLE】等待事件之日誌等待“log file sync”Oracle事件
- 【TUNE_ORACLE】等待事件之IO等待“read by other session”Oracle事件Session
- 【TUNE_ORACLE】等待事件之IO等待“direct path read”Oracle事件
- 【TUNE_ORACLE】等待事件之IO等待“direct path write”Oracle事件
- Latch free等待事件四(轉)事件