是否遇到过在分析AWR报告时,明明AAS很高,但从Top 10 Foreground Events by Total Wait Time 上看top event使用的百分比加起来离100%很远? 那DB TIME去哪了?下面我附一个11G(11.2.0.4 RAC on AIX) AWR 案例,这个问题在12c的AWR中提供了TOP EVENT。
Host Name
Platform
CPUs
Cores
Sockets
Memory (GB)
ANBOB.COM
AIX-Based Systems (64-bit)
64
16
241.00
Snap Id
Snap Time
Sessions
Cursors/Session
Instances
Begin Snap:
30330
05-Mar-19 16:00:50
1422
3.0
2
End Snap:
30331
05-Mar-19 17:00:06
904
4.1
2
Elapsed:
59.28 (mins)
DB Time:
24,019.53 (mins)
Top 10 Foreground Events by Total Wait Time
Event
Waits
Total Wait Time (sec)
Wait Avg(ms)
% DB time
Wait Class
DB CPU
51.4K
3.6
direct path read
64,724
3903.5
60
.3
User I/O
db file sequential read
219,201
1459.1
7
.1
User I/O
gc buffer busy acquire
273,842
1062.4
4
.1
Cluster
log file sync
36,584
882.1
24
.1
Commit
reliable message
102,567
854.4
8
.1
Other
gc current block 2-way
188,043
738.8
4
.1
Cluster
gc cr block 2-way
65,141
625.5
10
.0
Cluster
gc cr grant 2-way
103,189
241
2
.0
Cluster
gc current block busy
5,347
114.1
21
.0
Cluster
Statistic Name
Time (s)
% of DB Time
sql execute elapsed time
1,437,961.72
99.78
DB CPU
51,438.20
3.57
parse time elapsed
389.48
0.03
connection management call elapsed time
270.42
0.02
hard parse elapsed time
244.00
0.02
PL/SQL execution elapsed time
161.11
0.01
hard parse (sharing criteria) elapsed time
46.25
0.00
sequence load elapsed time
11.70
0.00
hard parse (bind mismatch) elapsed time
6.41
0.00
PL/SQL compilation elapsed time
1.16
0.00
repeated bind elapsed time
0.13
0.00
failed parse elapsed time
0.03
0.00
DB time
1,441,171.91
background elapsed time
2,121.28
background cpu time
84.06
从上面的数据看24,019/59.28 ≈407 AAS >64 Cpus *3, 说明数据库有严重的性能问题。但是从event的百分比看加起来都不足5%. 那db Time去哪了?
DB Time
• Total time in database calls by foreground sessions
• Includes CPU time, IO time and non-idle wait time +Wait on CPU queue
• Total DB time = sum of DB time for all active sessions
过高的应用高并发或CPU耗尽,大量的前台进程就会等待在CPU queue队列,在cpu分片上频发switch , interrupt. 而且db cpu 不包含on queue, 下面看12c 的AWR, 增加了top event, “CPU+ WAIT ON CPU”。 这样就可以就可以计算”on cpu runqueue”的百分比.
打赏
微信扫一扫,打赏作者吧~