通过案例学调优之--和 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.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的争用。