4.1、和 LOG BUFFER 相关的主要 Latch
有: Latch:Redo Copy
Latch:Redo Allocation Latch
4.2 当一个进程在修改数据时候将会产生 Redo,这个 Redo 首先在 PGA 中保存。
然后进程需要 获取Redo Copy Latch(这个Latch的个数由隐含参数_log_simultaneous_copies决定),当获 得 Redo Copy Latch 后,进程接着获取 Redo Allocation Latch 来分配 Redo Log Buffer 中的空间, 空间分配完成后,释放 Redo Allocation Latch。然后进程把 PGA 里临时存放的 Redo 信息复制 到 Redo Log Buffer,复制完成后,释放 Redo Copy Latch。
4.3 逻辑架构如下:
案例分析：
测试redo中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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
1
、建立测试环境
15
:
08
:
51
SYS@ prod >select name ,bytes/
1024
/
1024
from
v$sgastat
where
rownum <
6
;
NAME BYTES/
1024
/
1024
-------------------------- ---------------
fixed_sga
1.27443695
buffer_cache
60
log_buffer
6.0078125
kkj jobq wor
.003913879
dpslut_kfdsg
.000244141
建立一个最小的日志组
15
:
09
:
33
SYS@ prod >select group#,sequence#,status,bytes/
1024
/
1024
from
v$log;
GROUP# SEQUENCE# STATUS BYTES/
1024
/
1024
---------- ---------- ---------------- ---------------
4
108
CURRENT
4
5
106
INACTIVE
4
2
、建立三张测试表
15
:
11
:
59
SCOTT@ prod >create table tb1
as
select *
from
user_objects;
Table created.
15
:
13
:
48
SCOTT@ prod >select count(*)
from
tb1;
COUNT(*)
----------
376832
15
:
19
:
16
SCOTT@ prod >create table tb2
as
select *
from
tb1
where
rownum <
100000
;
Table created.
15
:
20
:
30
SCOTT@ prod >create table tb3
as
select *
from
tb1
where
rownum <
100000
;
Table created.
4
、建立测试脚本
[oracle@RH6 ~]$ cat
22.
sh
#!/bin/bash
export ORACLE_SID=prod
count=
0
while
[ $count -lt
1000
]
do
sqlplus
'scott/tiger'
<<EOF
update tb1 set object_id=
1000
;
rollback;
EOF
count=`expr $count +
1
`
done
[oracle@RH6 ~]$ cat
33.
sh
#!/bin/bash
export ORACLE_SID=prod
count=
0
while
[ $count -lt
1000
]
do
sqlplus
'scott/tiger'
<<EOF
update tb2 set object_id=
1000
;
rollback;
EOF
count=`expr $count +
1
`
done
[oracle@RH6 ~]$ cat
44.
sh
#!/bin/bash
export ORACLE_SID=prod
count=
0
while
[ $count -lt
1000
]
do
sqlplus
'scott/tiger'
<<EOF
update tb3 set object_id=
1000
;
rollback;
EOF
count=`expr $count +
1
`
done
5
、通过
3
个session，运行脚本
6
、查看session event
15
:
22
:
08
SYS@ prod >select sid,username ,event
from
v$session
where
username=
'SCOTT'
;
SID USERNAME EVENT
---------- ------------------------------ ----------------------------------------------------------------
31
SCOTT log file switch (checkpoint incomplete)
45
SCOTT enq: TX - row lock contention
Elapsed:
00
:
00
:
00.00
15
:
22
:
14
SYS@ prod >/
SID USERNAME EVENT
---------- ------------------------------ ----------------------------------------------------------------
31
SCOTT log file switch completion
41
SCOTT enq: TX - row lock contention
44
SCOTT log file switch completion
45
SCOTT enq: TX - row lock contention
47
SCOTT log file switch completion
15
:
23
:
42
SYS@ prod >/
SID USERNAME EVENT
---------- ------------------------------ ----------------------------------------------------------------
31
SCOTT db file sequential read
41
SCOTT enq: TX - row lock contention
44
SCOTT latch: redo copy
45
SCOTT enq: TX - row lock contention
47
SCOTT latch: redo allocation
15
:
26
:
54
SYS@ prod >r
1
* select sid,username ,event
from
v$session
where
username=
'SCOTT'
SID USERNAME EVENT
---------- ------------------------------ ----------------------------------------------------------------
31
SCOTT log file switch completion
41
SCOTT enq: TX - row lock contention
44
SCOTT log file switch completion
45
SCOTT enq: TX - row lock contention
47
SCOTT log file switch completion
7
、查看redo latch竞争
15
:
25
:
11
SYS@ prod >select name,misses,sleeps,spin_gets,wait_time
from
v$latch
15
:
25
:
34
2
where
name
in
(
'redo copy'
,
'redo allocation'
);
NAME MISSES SLEEPS SPIN_GETS WAIT_TIME
---------------------------------------------------------------- ---------- ---------- ---------- ----------
redo copy
101
116
0
279828
redo allocation
48
50
0
54560
Elapsed:
00
:
00
:
00.02
15
:
25
:
53
SYS@ prod >/
NAME MISSES SLEEPS SPIN_GETS WAIT_TIME
---------------------------------------------------------------- ---------- ---------- ---------- ----------
redo copy
111
126
0
300388
redo allocation
50
52
0
56124
Elapsed:
00
:
00
:
00.01
15
:
26
:
08
SYS@ prod >/
NAME MISSES SLEEPS SPIN_GETS WAIT_TIME
---------------------------------------------------------------- ---------- ---------- ---------- ----------
redo copy
111
126
0
300388
redo allocation
50
52
0
56124
Elapsed:
00
:
00
:
00.00
15
:
26
:
12
SYS@ prod >/
NAME MISSES SLEEPS SPIN_GETS WAIT_TIME
---------------------------------------------------------------- ---------- ---------- ---------- ----------
redo copy
202
234
0
594703
redo allocation
75
79
0
83114
Elapsed:
00
:
00
:
00.00
15
:
27
:
58
SYS@ prod >/
NAME MISSES SLEEPS SPIN_GETS WAIT_TIME
---------------------------------------------------------------- ---------- ---------- ---------- ----------
redo copy
220
258
0
661577
redo allocation
81
85
0
103697
15
:
28
:
29
SYS@ prod >/
NAME MISSES SLEEPS SPIN_GETS WAIT_TIME
---------------------------------------------------------------- ---------- ---------- ---------- ----------
redo copy
346
400
1
1174583
redo allocation
146
150
0
189359
|
可以看到，在系统中产生了大量的redo latch的争用。
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1576149，如需转载请自行联系原作者
