oracle12c生成的awr在哪里,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”的百分比.

打赏

微信扫一扫，打赏作者吧～