温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

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

发布时间:2020-07-25 13:21:03 来源:网络 阅读:853 作者:客居天涯 栏目:关系型数据库

通过案例学调优之--和 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 逻辑架构如下: 

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

案例分析:

测试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的争用。

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI