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/bashexport ORACLE_SID=prodcount=0
while [ $count -lt 1000 ]
dosqlplus 'scott/tiger'<<EOF
update tb1 set object_id=1000 ;
rollback;EOFcount=`expr $count + 1`
done[oracle@RH6 ~]$ cat 33.sh
#!/bin/bashexport ORACLE_SID=prodcount=0
while [ $count -lt 1000 ]
dosqlplus 'scott/tiger'<<EOF
update tb2 set object_id=1000 ;
rollback;EOFcount=`expr $count + 1`
done[oracle@RH6 ~]$ cat 44.sh
#!/bin/bashexport ORACLE_SID=prodcount=0
while [ $count -lt 1000 ]
dosqlplus 'scott/tiger'<<EOF
update tb3 set object_id=1000 ;
rollback;EOFcount=`expr $count + 1`
done5、通过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，如需转载请自行联系原作者