通过案例学调优之--和 LOG BUFFER 相关的主要 Latch

通过案例学调优之--和 LOG BUFFER 相关的主要 Latch

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、建立测试环境15:08:51 SYS@ prod >select name ,bytes/1024/1024 from v$sgastat where rownum <6;NAME                       BYTES/1024/1024-------------------------- ---------------fixed_sga                       1.27443695buffer_cache                            60log_buffer                       6.0078125kkj jobq  wor                   .003913879dpslut_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=0while [ $count -lt 1000 ]dosqlplus 'scott/tiger'<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 contentionElapsed: 00:00:00.0015: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 completion15: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 allocation15: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$latch15:25:34   2   where name in ('redo copy','redo allocation');NAME                                                                 MISSES     SLEEPS  SPIN_GETS  WAIT_TIME---------------------------------------------------------------- ---------- ---------- ---------- ----------redo copy                                                               101        116          0     279828redo allocation                                                          48         50          0      54560Elapsed: 00:00:00.0215:25:53 SYS@ prod >/NAME                                                                 MISSES     SLEEPS  SPIN_GETS  WAIT_TIME---------------------------------------------------------------- ---------- ---------- ---------- ----------redo copy                                                               111        126          0     300388redo allocation                                                          50         52          0      56124Elapsed: 00:00:00.0115:26:08 SYS@ prod >/NAME                                                                 MISSES     SLEEPS  SPIN_GETS  WAIT_TIME---------------------------------------------------------------- ---------- ---------- ---------- ----------redo copy                                                               111        126          0     300388redo allocation                                                          50         52          0      56124Elapsed: 00:00:00.0015:26:12 SYS@ prod >/NAME                                                                 MISSES     SLEEPS  SPIN_GETS  WAIT_TIME---------------------------------------------------------------- ---------- ---------- ---------- ----------redo copy                                                               202        234          0     594703redo allocation                                                          75         79          0      83114Elapsed: 00:00:00.0015:27:58 SYS@ prod >/NAME                                                                 MISSES     SLEEPS  SPIN_GETS  WAIT_TIME---------------------------------------------------------------- ---------- ---------- ---------- ----------redo copy                                                               220        258          0     661577redo allocation                                                          81         85          0     10369715:28:29 SYS@ prod >/NAME                                                                 MISSES     SLEEPS  SPIN_GETS  WAIT_TIME---------------------------------------------------------------- ---------- ---------- ---------- ----------redo copy                                                               346        400          1    1174583redo allocation                                                         146        150          0     189359

可以看到，在系统中产生了大量的redo latch的争用。