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 | g" scope="col">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”的百分比.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。