Free buffer waits
当一个会话将数据块从磁盘读到内存中时，它需要到内存中找到空闲的内存空间来存放这些数据块，当内存中没有空闲的空间时，就会产生这个等待；除此之外，还有一种情况就是会话在做一致性读时，需要构造数据块在某个时刻的前映像（image），此时需要申请内存来存放这些新构造的数据块，如果内存中无法找到这样的内存块，也会发生这个等待事件。
当数据库中出现比较严重的free buffer waits等待事件时，可能的原因是：
（1）data buffer 太小，导致空闲空间不够
（2）内存中的脏数据太多，DBWR无法及时将这些脏数据写到磁盘中以释放空间
这个等待事件包含2个参数：
File#： 需要读取的数据块所在的数据文件的文件号。
Block#： 需要读取的数据块块号。
案例分析：
|
1
2
3
4
5
6
7
8
|
11
:
14
:
33
SYS@ prod>show parameter cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 24M
db_cache_advice string ON
db_cache_size big integer 16M
db_keep_cache_size big integer
0
db_recycle_cache_size big integer 12M
|
|
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
|
11
:
21
:
17
SYS@ prod>conn scott/tiger
Connected.
11
:
23
:
16
SCOTT@ prod>begin
11
:
25
:
04
2
for
i
in
1.
.100000
loop
11
:
25
:
04
3
insert
into
t1 values (i);
11
:
25
:
04
4
end loop;
11
:
25
:
04
5
end;
11
:
25
:
04
6
/
PL/SQL procedure successfully completed.
11
:
23
:
29
SYS@ prod>conn tom/tom
Connected.
11
:
23
:
38
TOM@ prod>create table t1
as
select *
from
scott.t1;
Table created.
Elapsed:
00
:
00
:
02.19
11
:
23
:
52
TOM@ prod>begin
11
:
24
:
59
2
for
i
in
1.
.100000
loop
11
:
24
:
59
3
insert
into
t1 values (i);
11
:
24
:
59
4
end loop;
11
:
24
:
59
5
end;
11
:
24
:
59
6
/
PL/SQL procedure successfully completed.
11
:
25
:
12
SYS@ prod>select event,TOTAL_WAITS,AVERAGE_WAIT,EVENT_ID
from
v$system_event
2
*
where
event like
'%buffer%'
EVENT TOTAL_WAITS AVERAGE_WAIT EVENT_ID
---------------------------------------------------------------- ----------- ------------ ----------
latch: cache buffers chains
3
.2
2779959231
free buffer waits
14
.86
2701153470
buffer busy waits
2
.23
2161531084
log buffer space
7
40.42
3357856061
latch: cache buffers lru chain
17
.32
3401628503
buffer deadlock
11
.03
218992928
6
rows selected.
|
Latch free
在10g之前的版本里，latch free 等待事件代表了所有的latch等待，在10g以后，一些常用的latch事件已经被独立了出来：
|
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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
11
:
25
:2name1 SYS@ prod>select name
from
v$event_name
where
name like
'latch%'
order by
1
;
NAME
----------------------------------------------------------------
latch activity
latch free
latch: Change Notification Hash table latch
latch: In memory undo latch
latch: MQL Tracking Latch
latch: PX hash array latch
latch: Undo Hint Latch
latch: WCR: processes HT
latch: WCR: sync
latch: cache buffer handles
latch: cache buffers chains
latch: cache buffers lru chain
latch: call allocation
latch: change notification client cache latch
latch: checkpoint queue latch
latch: enqueue hash chains
latch: gc element
NAME
----------------------------------------------------------------
latch: gcs resource hash
latch: ges resource hash list
latch: lob segment dispenser latch
latch: lob segment hash table latch
latch: lob segment query latch
latch: messages
latch: object queue header operation
latch: parallel query alloc buffer
latch: redo allocation
latch: redo copy
latch: redo writing
latch: row cache objects
latch: session allocation
latch: shared pool
latch: undo global data
latch: virtual circuit queues
33
rows selected.
11
:
39
:
21
SYS@ prod>select EVENT#,EVENT_ID,NAME,PARAMETER1,PARAMETER2,PARAMETER3
from
v$event_name
2
*
where
name like
'%latch free%'
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3
---------- ---------- ------------------------------ ---------- -------------------- ------------------------------
402
3474287957
latch free address number tries
409
2530878290
wait list latch free address number tries
11
:
32
:
33
SYS@ prod>desc v$latchname
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
LATCH# NUMBER
NAME VARCHAR2(
64
)
HASH NUMBER
所以latch free 等待事件在10g以后的版本中并不常见，而是以具体的Latch 等待事件出现。这个等待事件有三个参数：Address: 会话等待的latch 地址。
Number
： latch号，通过这个号，可以从v$latchname 视图中找到这个latch 的相关的信息，Tries: 会话尝试获取Latch 的次数。
11
:
34
:
25
SYS@ prod>select *
from
v$latchname
11
:
34
:
36
2
where
name like
'%buffer%'
;
LATCH# NAME HASH
---------- ---------------------------------------------------------------- ----------
33
SGA IO buffer pool latch
2719726273
63
IPC stats buffer allocation latch
1449990452
106
KJC global post event buffer
3098969798
145
cache buffers lru chain
3559635447
146
buffer pool
510014793
150
cache buffers chains
3563305585
151
cache buffer handles
892398878
196
media recovery process out of buffers
2731251867
197
mapped buffers lru chain
93631960
208
lock DBA buffer during media recovery
3620457631
350
virtual circuit buffers
1577520421
378
parallel query alloc buffer
291345605
416
image handles of buffered messages latch
3223585260
476
buffer pin latch
3925519355
14
rows selected.
|
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1537334，如需转载请自行联系原作者
