本文共 5562 字，大约阅读时间需要 18 分钟。
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的争用。