首页 » ORACLE, ORACLE [C]系列 » DB Time 去哪了? Oracle 12C AWR 增加了on cpu runqueue

DB Time 去哪了? Oracle 12C AWR 增加了on cpu runqueue

是否遇到过在分析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”的百分比.

打赏

,

对不起,这篇文章暂时关闭评论。