buffer busy waits等待事件
|
1
|
Wait occurs when a session attempts to access a block
in
memory, is denied
and
must wait until the buffer becomes available. This event happens because a buffer is either being read
into
the buffer cache by another session (
and
the session is waiting
for
that read to complete)
or
the buffer is
in
the buffer cache, but
in
a incompatible mode (that is, some other session is changing the buffer).
|
Solutions：
|
1
2
3
4
5
|
Buffer busy waits often occur
in
the following cases:
Inefficient SQL statements read more blocks than necessary. If there are many sessions running these statements, they will attempt to read the same blocks
and
possibly wait
on
this
event.
If the FREELISTS parameter
for
a table is too low, multiple sessions that are attempting to
insert
rows
in
the same table
and
end up waiting
for
freelists. This problem shows up
as
contention
for
the segment header of the table.
Multiple sessions are attempting to change an index block (possibly do to an
insert
).
The INITRANS parameters is too low
for
a segment. Any DML operation that needs to go
into
the block needs to lock an Interested Transaction List (ITL). If INITRANS parameter is set too low,
then
there will be less number of ITLs allocated originally. Oracle can only allocate more ITLs
if
there is space
in
PCTFREE area of the block. If, however, there is no space to increase the ITL, the transactions that cannot lock ITLs will have to wait until the previous transactions have completed operations
on
the block. The waiter will register ‘buffer busy wait’
for
the block
|
案例分析：
1、建立测试表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
13
:
35
:
51
SCOTT@ prod >create table tb1 (id int ,name varchar2(
10
));
Table created.
13
:
36
:
16
SCOTT@ prod >
insert
into
tb1 values (
1
,
'scott'
);
1
row created.
13
:
36
:
35
SCOTT@ prod >
insert
into
tb1 values (
2
,
'tom'
);
1
row created.
13
:
36
:
47
SCOTT@ prod >commit;
Commit complete.
13
:
37
:
09
SCOTT@ prod >select sid
from
v$mystat
where
rownum=
1
;
SID
----------
37
13
:
37
:
25
SCOTT@ prod >grant all
on
tb1 to tom;
Grant succeeded.
|
2、session 1做事务处理
|
1
2
3
4
5
6
7
|
13
:
40
:
24
SCOTT@ prod >begin
13
:
41
:
18
2
for
i
in
1.
.100000
loop
13
:
41
:
29
3
update tb1 set name=
'rose'
where
id=
2
;
13
:
41
:
56
4
commit;
13
:
41
:
58
5
end loop;
13
:
42
:
02
6
end;
13
:
42
:
04
7
/
|
3、session 2做事务处理
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
13
:
40
:
48
SYS@ prod >conn tom/tom
Connected.
13
:
40
:
52
TOM@ prod >select sid
from
v$mystat
where
rownum=
1
;
SID
----------
43
13
:
41
:
08
TOM@ prod >begin
13
:
43
:
15
2
for
i
in
1.
.100000
loop
13
:
43
:
22
3
update scott.tb1 set name=
'john'
where
id=
1
;
13
:
43
:
43
4
commit;
13
:
43
:
45
5
end loop;
13
:
43
:
49
6
end;
13
:
43
:
50
7
/
|
4、查看会话等待事件
如果事务在运行过程中可以访v$session_wait,如果事务运行结束可以访问v$session_wait_history
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
13
:
44
:
55
SYS@ prod > select event,sid,p1,p2,p3
from
v$session_wait_history
where
sid
in
(
37
,
43
)
and
event like
'%buffer%'
EVENT SID P1 P2 P3
---------------------------------------------------------------- ---------- ---------- ---------- ----------
log buffer space
37
0
0
0
log buffer space
37
0
0
0
log buffer space
43
0
0
0
log buffer space
43
0
0
0
log buffer space
43
0
0
0
13
:
44
:
56
SYS@ prod >select event,sid,p1,p2,p3
from
v$session_wait_history
where
sid
in
(
37
,
43
)
and
event like
'%buffer%'
EVENT SID P1 P2 P3
---------------------------------------------------------------- ---------- ---------- ---------- ----------
buffer busy waits
37
6
203
1
buffer busy waits
37
6
203
1
buffer busy waits
37
6
203
1
buffer busy waits
37
6
203
1
latch: cache buffers chains
37
372066908
150
0
buffer busy waits
37
6
203
1
buffer busy waits
43
6
203
1
latch: cache buffers chains
43
372066908
150
0
buffer busy waits
43
6
203
1
buffer busy waits
43
6
203
1
latch: cache buffers chains
43
372066908
150
0
latch: cache buffers chains
43
372066908
150
0
12
rows selected.
可以看到引起‘buffer busy waits’的block: file#为
6
，block#为
203
|
5、判断等待事件访问的块类型
|
1
2
3
4
5
6
|
13
:
45
:
17
SYS@ prod >select *
from
v$waitstat
where
count >
0
;
CLASS COUNT TIME
------------------ ---------- ----------
data block
144
754
file header block
9
44
undo header
7
1
|
6、查看引起‘buffer busy waits’事件的sql语句
|
1
2
3
4
|
13
:
52
:
13
SYS@ prod >select sql_text
from
V$sqlarea
where
(address,hash_value)
in
(select sql_address,sql_hash_value
from
v$session
where
event like
'%buffer busy%'
)
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
UPDATE TB1 SET NAME=
'rose'
WHERE ID=
2
|
7、查看引起‘buffer busy waits’事件的块类型
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
14
:
11
:
00
SYS@ prod > SELECT
'segment Header'
CLASS,
2
a.Segment_Type,
3
a.Segment_Name,
4
a.Partition_Name
5
FROM Dba_Segments a,
6
V$session_Wait b
7
WHERE a.Header_File = b.P1
8
AND a.Header_Block = b.P2
9
AND b.Event =
'buffer busy waits'
10
UNION
11
SELECT
'freelist Groups'
CLASS,
12
a.Segment_Type,
13
a.Segment_Name,
14
a.Partition_Name
15
FROM Dba_Segments a,
16
V$session_Wait b
17
WHERE b.P2 BETWEEN a.Header_Block +
1
AND (a.Header_Block + a.Freelist_Groups)
18
AND a.Header_File = b.P1
19
AND a.Freelist_Groups >
1
20
AND b.Event =
'buffer busy waits'
21
UNION
22
SELECT a.Segment_Type ||
' Block'
CLASS,
23
a.Segment_Type,
24
a.Segment_Name,
25
a.Partition_Name
26
FROM Dba_Extents a,
27
V$session_Wait b
28
WHERE b.P2 BETWEEN a.Block_Id AND a.Block_Id + a.Blocks -
1
29
AND a.File_Id = b.P1
30
AND b.Event =
'buffer busy waits'
31
AND NOT EXISTS (SELECT
1
32
FROM Dba_Segments
33
WHERE Header_File = b.P1
34
* AND Header_Block = b.P2)
CLASS SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME
------------------------ ------------------ -------------------- ------------------------------
TABLE Block TABLE TB1
|
8、查看引起‘buffer busy waits’事件的segment
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
13
:
57
:
25
SYS@ prod >col segment_name
for
a30
13
:
57
:
38
SYS@ prod >select owner,segment_name,segment_type,block_id
from
dba_extents
where
file_id=
6
OWNER SEGMENT_NAME SEGMENT_TYPE BLOCK_ID
------------------------------ ------------------------------ ------------------ ----------
SCOTT EMP1 TABLE
128
SCOTT EMP1 TABLE
144
SCOTT EMP1 TABLE
160
SCOTT EMP1 TABLE
168
SCOTT EMP1 TABLE
184
SCOTT MLOG$_EMP1 TABLE
136
SCOTT MLOG$_EMP1 TABLE
152
SCOTT MLOG$_EMP1 TABLE
176
SCOTT MLOG$_EMP1 TABLE
192
SCOTT TB1 TABLE
200
10
rows selected.
|
因为表TB1的block#，接近引起“buffer busy waits”事件的block#（203），从而判断热块是在表tb1上。
9、查询访问tb1的sql
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
14
:
01
:
31
SYS@ prod >select sql_text
from
v$sqlarea
where
sql_text like
'%tb1%'
;
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
begin
for
i
in
1.
.100000
loop update tb1 set name=
'rose'
where
id=
2
; commit; end loop; end;
select sql_text
from
v$sqlarea
where
sql_text like
'%tb1%'
select sql_text
from
v$sqlarea
where
sql_text like
'%tb1%'
begin
for
i
in
1.
.100000
loop update scott.tb1 set name=
'rose'
where
id=
2
; commit; end loop; end;
14
:
01
:
45
SYS@ prod >select sql_text
from
v$sqlarea
where
sql_text like
'%tb1%'
;
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
begin
for
i
in
1.
.100000
loop update tb1 set name=
'rose'
where
id=
2
; commit; end loop; end;
select sql_text
from
v$sqlarea
where
sql_text like
'%tb1%'
select sql_text
from
v$sqlarea
where
sql_text like
'%tb1%'
begin
for
i
in
1.
.100000
loop update scott.tb1 set name=
'rose'
where
id=
2
; commit; end loop; end;
|
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1546187，如需转载请自行联系原作者
