首页 > 代码库 > DB buffer bussy wait 分析一例
DB buffer bussy wait 分析一例
DB层分析OI
DB层分析OI的信息如下：
1. 异常时间段, Logical reads:/ Physical reads/ Physical write 指标都低于正常时间段。说明数据库本身消耗i/o 并不高。但是nmon显示disk 读写非常高，
同时现场分析， I/O 资源消耗最大可达40M-70M/s,任务可以顺利完成。同时注意到
通过topas 来看，在一些时候，hdisk 在 tps和kbps 为0的情况下，磁盘繁忙程度达到99%，所以建议如下：
是这个现在已有的信息能够看到的一些问题，还需要OS层面给一些东西，需要OS介入帮助看看底层是否有什么问题。请主机组检查存储
2. 等待事件buffer busy waits 占首位，消耗了很多资源，主要是sql 2mwvn9xwq1tz3 消耗。建议检查sql
检查这个sql 执行计划2mwvn9xwq1tz3。
|
Elapsed Time (s) |
Executions |
Elapsed Time per Exec (s) |
%Total |
%CPU |
%IO |
SQL Id |
SQL Module |
SQL Text |
|
80,962.92 |
24,804 |
3.26 |
33.97 |
0.00 |
0.00 |
2mwvn9xwq1tz3 |
e:IEU:cp:IEU_WL_CS |
select (RUNNING_PROCESSES-MAX_... |
分析sql 结果：
Sql 执行计划没有变化。主要消耗在buffer busy wait ,说明当时缓存区争用，怀疑跟系统I/O 处理不过来，导致缓存存在问题。
SQL_ID 2mwvn9xwq1tz3
--------------------
select (RUNNING_PROCESSES-MAX_PROCESSES) ,MAX_PROCESSES
,NVL(SLEEP_SECONDS,0) ,DIAGNOSTIC_LEVEL into :b0,:b1,:b2,:b3:b4 from
FND_CONCURRENT_QUEUES where ((DBLICATION_ID=:b5 and
CONCURRENT_QUEUE_ID=:b6) and (TARGET_NODE=:b7 or (TARGET_NODE is null
and :b7 is null )))
Plan hash value: 356935968
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| FND_CONCURRENT_QUEUES | 1 | 22 | 1 (0)| 00:00:01 |
| 2 | INDEX UNIQUE SCAN | FND_CONCURRENT_QUEUES_U1 | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------------------------
详细分析如下:
-》 1 。正常时间段：等待事件正常， Logical reads:/ Physical reads/ Physical write 指标都高于 异常时间段。
|
|
Snap Time |
Sessions |
Cursors/Session |
|
|
Begin Snap: |
36769 |
12-6月 -17 20:00:52 |
216 |
10.5 |
|
End Snap: |
36773 |
13-6月 -17 00:00:22 |
229 |
9.3 |
|
Elapsed: |
|
239.51 (mins) |
|
|
|
DB Time: |
|
1,570.72 (mins) |
|
|
Top 5 Timed Foreground Events
|
Event |
Waits |
Time(s) |
Avg wait (ms) |
% DB time |
Wait Class |
|
db file sequential read |
5,750,530 |
23,950 |
4 |
25.41 |
User I/O |
|
log buffer space |
72,789 |
15,988 |
220 |
16.96 |
Configuration |
|
DB CPU |
|
10,941 |
|
11.61 |
|
|
SQL*Net message from dblink |
61,789 |
7,296 |
118 |
7.74 |
Network |
|
direct path read |
385,065 |
6,336 |
16 |
6.72 |
User I/O |
Segments by Buffer Busy Waits
% of Capture shows % of Buffer Busy Waits for each top segment compared
with total Buffer Busy Waits for all segments captured by the Snapshot
|
Owner |
Tablespace Name |
Object Name |
Subobject Name |
Obj. Type |
Buffer Busy Waits |
% of Capture |
|
MSC |
DBS_TS_INTERFACE |
MSC_ST_SYSTEM_ITEMS |
SYSTEM_ITEMS_3021 |
TABLE PARTITION |
13,842 |
45.12 |
|
DATALOAD |
TS_DATA_LOAD |
ZTE_ERP_WIP_DETAIL |
|
TABLE |
6,058 |
19.75 |
|
DBLSYS |
DBS_TS_TX_DATA |
FND_CONCURRENT_QUEUES |
|
TABLE |
5,119 |
16.69 |
Load Profile
|
Per Second |
Per Transaction |
Per Exec |
Per Call |
|
|
DB Time(s): |
6.6 |
0.5 |
0.00 |
0.11 |
|
DB CPU(s): |
0.8 |
0.1 |
0.00 |
0.01 |
|
Redo size: |
6,145,675.1 |
497,949.9 |
|
|
|
Logical reads: |
223,510.5 |
18,109.8 |
|
|
|
Block changes: |
20,265.5 |
1,642.0 |
|
|
|
Physical reads: |
2,507.0 |
203.1 |
|
|
|
Physical writes: |
3,069.3 |
248.7 |
|
|
|
User calls: |
60.5 |
4.9 |
|
|
|
Parses: |
3.3 |
0.3 |
|
|
|
Hard parses: |
0.9 |
0.1 |
|
|
|
W/A MB processed: |
3.7 |
0.3 |
|
|
|
Logons: |
0.2 |
0.0 |
|
|
|
Executes: |
2,988.9 |
242.2 |
|
|
|
Rollbacks: |
0.3 |
0.0 |
|
|
|
Transactions: |
12.3 |
|
|
-》 2 .异常时间段。等待事件buffer busy waits 占首位，消耗了很多资源，主要是sql 2mwvn9xwq1tz3 消耗。
Logical reads:/ Physical reads/ Physical write 指标都低于正常时间段。说明数据库本身消耗i/o 并不高。，
|
|
Snap Time |
Sessions |
Cursors/Session |
|
|
Begin Snap: |
36817 |
14-Jun-17 20:00:02 |
228 |
12.8 |
|
End Snap: |
36821 |
15-Jun-17 00:00:21 |
235 |
10.9 |
|
Elapsed: |
|
240.31 (mins) |
|
|
|
DB Time: |
|
6,207.36 (mins) |
|
|
|
Buffer Cache: |
29,184M |
29,184M |
Std Block Size: |
8K |
|
Shared Pool Size: |
10,240M |
10,240M |
Log Buffer: |
72,708K |
|
Event |
Waits |
Time(s) |
Avg wait (ms) |
% DB time |
Wait Class |
|
buffer busy waits |
78,775 |
207,914 |
2639 |
55.82 |
Concurrency |
Segments by Buffer Busy Waits
- % of Capture shows % of Buffer Busy Waits for each top segment compared
- with total Buffer Busy Waits for all segments captured by the Snapshot
|
Owner |
Tablespace Name |
Object Name |
Subobject Name |
Obj. Type |
Buffer Busy Waits |
% of Capture |
|
DBLSYS |
DBS_TS_TX_DATA |
FND_CONCURRENT_QUEUES |
|
TABLE |
90,802 |
72.49 |
|
2mwvn9xwq1tz3 |
select (RUNNING_PROCESSES-MAX_PROCESSES) , MAX_PROCESSES , NVL(SLEEP_SECONDS, 0) , DIAGNOSTIC_LEVEL into :b0, :b1, :b2, :b3:b4 from FND_CONCURRENT_QUEUES where ((DBLICATION_ID=:b5 and CONCURRENT_QUEUE_ID=:b6) and (TARGET_NODE=:b7 or (TARGET_NODE is null and :b7 is null ))) |
Load Profile
|
Per Second |
Per Transaction |
Per Exec |
Per Call |
|
|
DB Time(s): |
20.0 |
1.7 |
0.06 |
0.15 |
|
DB CPU(s): |
0.2 |
0.0 |
0.00 |
0.00 |
|
Redo size: |
1,836,990.9 |
154,577.6 |
|
|
|
Logical reads: |
35,973.5 |
3,027.1 |
|
|
|
Block changes: |
5,531.3 |
465.4 |
|
|
|
Physical reads: |
343.4 |
28.9 |
|
|
|
Physical writes: |
550.6 |
46.3 |
|
|
|
User calls: |
132.6 |
11.2 |
|
|
|
Parses: |
3.0 |
0.3 |
|
|
|
Hard parses: |
0.8 |
0.1 |
|
|
|
W/A MB processed: |
2.6 |
0.2 |
|
|
|
Logons: |
0.1 |
0.0 |
|
|
|
Executes: |
332.7 |
28.0 |
|
|
|
Rollbacks: |
0.5 |
0.0 |
|
|
|
Transactions: |
11.9 |
|
|
è 3. 检查这个sql 执行计划2mwvn9xwq1tz3。
6.11没问题：
6.14有问题：
DB buffer bussy wait 分析一例