AWR--service statistics
近期发现一个奇怪的现象。数据库报告上看负载非常高,可是cpu和等待事件都非常低,不知道消耗的资源跑到哪里去了?
Snap Id |
Snap Time | Sessions | Cursors/Session | |
---|---|---|---|---|
Begin Snap: | 5073 | 17-5月 -14 04:00:20 | 127 | 78.8 |
End Snap: | 5074 | 17-5月 -14 05:13:36 | 363 | 19.5 |
Elapsed: | 73.27 (mins) | |||
DB Time: | 1,196.25 (mins) |
Top 5 Timed Events
Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
---|---|---|---|---|---|
latch: library cache | 3,174 | 6,933 | 2,184 | 9.7 | Concurrency |
latch free | 977 | 6,530 | 6,684 | 9.1 | Other |
latch: shared pool | 4,021 | 1,929 | 480 | 2.7 | Concurrency |
cursor: pin S wait on X | 82,902 | 1,620 | 20 | 2.3 | Concurrency |
CPU time | 1,073 | 1.5 |
Service Statistics
- ordered by DB Time
Service Name | DB Time (s) | DB CPU (s) | Physical Reads | Logical Reads |
---|---|---|---|---|
SYS$USERS | 67,080.30 | 454.30 | 43,604 | 15,183,498 |
scmis | 4,523.20 | 588.30 | 0 | 22,868,201 |
SYS$BACKGROUND | 0.00 | 0.00 | 109 | 77,783 |
能够看到数据库软件消耗的资源不多。
找到问题症结:再看servicestatistics总的SYS$USERS消耗最多,这个是何方圣神呢?官方文档的解释:the SYS$USERS
serviceis
the default service name used when a user session is established withoutexplicitly identifying its service name。
就是说SYS$USERS这是一个缺省的服务名。当用户的session建立的时候没有明白的标示符。
那说明此时有其它的服务在数据库server上跑,仅仅实用操作系统层面上做诊断。用shell写一个top的脚本监控一下,是哪个进程导致。最后诊断出是数据库server在某个时间段不响应,简单的说是硬件的问题。