理解redo(7)oracle redo并行机制的原理介绍

长平狐 发布于 2012/09/19 13:56
阅读 150
收藏 0

    在前面的blog中,我们知道,redo entries写入log buffer大致的过程如下:

        在PGA中生产Redo Entry -> 服务进程获取Redo Copy latch(存在多个---CPU_COUNT*2) -> 服务进程获取redo allocation latch(仅1个) -> 分配log buffer ->

        释放redo  allocation latch -> 将Redo Entry写入Log Buffer -> 释放Redo Copy latch

 

    由于log buffer是一块“共享”内存,为了避免冲突,它是受到redo allocation latch保护的,每个server process需要先获取到该latch才能分配redo buffer。因此,在OLTP系统中,我们通常可以观察到redo allocation latch的等待事件。

 

    oracle引入shared strand和private strand来实现并行redo buffer分配机制,借此避免高并发下的redo allocation latch等待事件。

 

   1   shared strand

 

    为了减少redo allocation latch等待事件,oracle引入了log buffer的并行机制。其基本原理是,将log buffer划分为多个小的buffer,这些小的buffer被称作shared strand。每一个shared strand受到一个单独的redo allocation latch的保护。多个shared strand的出现,使得原来序列化的redo buffer分配变成了并行的过程,从而减少了redo allocation latch的等待。

 

    shared strand由一些隐藏参数控制:

09:39:59 sys@ORCL (^ω^) col name for a25
09:42:11 sys@ORCL (^ω^) col value for a10
09:42:11 sys@ORCL (^ω^) col description for a55
09:42:11 sys@ORCL (^ω^) select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
09:42:11   2    from x$ksppi a,x$ksppcv b
09:42:11   3   where a.indx = b.indx
09:42:11   4     and a.ksppinm like '%_log_parallelism%'
09:42:13   5  /

NAME                      VALUE      DESCRIPTION
------------------------- ---------- -------------------------------------------------------
_log_parallelism          1          Number of log buffer strands
_log_parallelism_max      2          Maximum number of log buffer strands
_log_parallelism_dynamic  TRUE       Enable dynamic strands  --控制是否允许shared strand数量在_log_parallelism和_log_parallelism_max之间动态变化


    每一个shared_strand的大小=log_buffer/(shared_strand的数量):

12:55:40 sys@ORCL (^ω^) select indx,strand_size_kcrfa from x$kcrfstrand where last_buf_kcrfa != '00';

      INDX STRAND_SIZE_KCRFA
---------- -----------------
         0           3512320
         1           3512320

12:58:46 sys@ORCL (^ω^) show parameter log_buffer

NAME_COL_PLUS_S TYPE            VALUE_COL_PLUS_
--------------- --------------- ---------------
log_buffer      integer         7024640
12:58:57 sys@ORCL (^ω^) select 7024640/2 from dual;

 7024640/2
----------
   3512320


    关于shared strand的数量设置,16个cpu之内最大默认为2,当系统中存在redo allocation latch等待时,每增加16个cpu可以考虑增加1个strand,最大不应该超过8。
并且_log_parallelism_max不允许大于cpu_count。

 

    2  private strand

 

    为了进一步降低redo buffer的冲突,10g引入了private strand机制,这是从shared pool中分配出来的一块内存空间。每一个Private strand受到一个单独的redo allocation latch保护,每个Private strand作为“私有的”strand只会服务于一个活动事务。获取到了Private strand的用户事务不是在PGA中而是在Private strand生成Redo。当flush private strand或者commit时,Private strand被批量写入log文件中。如果新事务申请不到Private strand的redo allocation latch,则会继续遵循旧的redo buffer机制,申请写入shared strand中。对于未能获取到Private strand的redo allocation latch的事务,在事务结束前,即使已经有其它事务释放了Private strand,也不会再申请Private strand了。

   

    整个事务过程大致如下:

    事务开始 -> 申请Private strand的redo allocation latch (申请失败则申请Shared Strand的redo allocation latch) -> 在Private strand中生产Redo Enrey -> 

    Flush/Commit ->申请Redo Copy Latch -> 服务进程将Redo Entry批量写入Log File -> 释放Redo Copy Latch -> 释放Private strand的redo allocation latch

12:59:30 sys@ORCL (^ω^) select * from v$sgastat where name like '%strand%';

POOL                     NAME                 BYTES
------------------------ --------------- ----------
shared pool              private strands    1198080

13:12:31 sys@ORCL (^ω^) select indx,strand_size_kcrfa from x$kcrfstrand where last_buf_kcrfa = '00';

      INDX STRAND_SIZE_KCRFA
---------- -----------------
         2             66560
         3             66560
         4             66560
         5             66560
         6             66560
         7             66560
         8             66560
         9             66560
        10             66560
        11             66560
        12             66560
        13             66560
        14             66560
        15             66560
        16             66560
        17             66560
        18             66560
        19             66560

已选择18行。
14:53:42 sys@ORCL (^ω^) select 66560*18 from dual;

  66560*18
----------
   1198080


    每个Private strand的大小为65K。Private strand的数量受到2个方面的影响:min(logfile的大小,活跃事务数量)

                 2.1  logfile的大小

                        参数_log_private_mul指定了使用多少logfile空间预分配给Private strand,默认为5。我们可以根据当前logfile的大小(要除去预分配给log buffer的空间)计算出这一约束条件下能够预分配多少个Private strand。

15:27:25 sys@ORCL (^ω^) select bytes from v$log where status='CURRENT';

     BYTES
----------
  52428800

15:32:30 sys@ORCL (^ω^)  select trunc(((select bytes from v$log where status = 'CURRENT') - (select to_number(value) from v$parameter where name = 'log_buffer'))*
15:35:16   2         (select to_number(val.KSPPSTVL) from sys.x$ksppi nam, sys.x$ksppsv val  where nam.indx = val.indx AND nam.ksppinm = '_log_private_mul') / 100 / 66560)
15:35:16   3          as "calculated private strands"
15:35:16   4     from dual;

calculated private strands
--------------------------
                        34

15:35:22 sys@ORCL (^ω^) select count(1) "actual private strands" from x$kcrfstrand where last_buf_kcrfa = '00';

actual private strands
----------------------
                    18

15:36:03 sys@ORCL (^ω^) alter system switch logfile;

系统已更改。

15:36:22 sys@ORCL (^ω^) select bytes from v$log where status='CURRENT';

     BYTES
----------
  52428800

15:36:46 sys@ORCL (^ω^)  select trunc(((select bytes from v$log where status = 'CURRENT') - (select to_number(value) from v$parameter where name = 'log_buffer'))*
15:36:59   2         (select to_number(val.KSPPSTVL) from sys.x$ksppi nam, sys.x$ksppsv val  where nam.indx = val.indx AND nam.ksppinm = '_log_private_mul') / 100 / 66560)
15:37:00   3          as "calculated private strands"
15:37:00   4     from dual;

calculated private strands
--------------------------
                        34

15:37:01 sys@ORCL (^ω^) select count(1) "actual private strands" from x$kcrfstrand where last_buf_kcrfa = '00';

actual private strands
----------------------
                    18

15:37:19 sys@ORCL (^ω^)


                2.2  活跃事务数量

                       参数_log_private_parallelism_mul用于推算活跃事务数量在最大事务数量中的百分比,默认为10。Private strand的数量不能大于活跃事务的数量。

15:37:19 sys@ORCL (^ω^) show parameter transactions

NAME_COL_PLUS_S TYPE            VALUE_COL_PLUS_
--------------- --------------- ---------------
transactions    integer         187
transactions_pe integer         5
r_rollback_segm
ent
15:42:47 sys@ORCL (^ω^) select trunc((select to_number(value) from v$parameter where name = 'transactions') *
15:44:33   2         (select to_number(val.KSPPSTVL)
15:44:33   3             from sys.x$ksppi nam, sys.x$ksppsv val
15:44:33   4            where nam.indx = val.indx
15:44:33   5               AND nam.ksppinm = '_log_private_parallelism_mul') / 100 )
15:44:33   6          as "calculated private strands"
15:44:33   7   from dual;

calculated private strands
--------------------------
                        18

15:44:35 sys@ORCL (^ω^) select count(1) "actual private strands" from x$kcrfstrand where last_buf_kcrfa = '00';

actual private strands
----------------------
                    18


 

 


原文链接:http://blog.csdn.net/linwaterbin/article/details/7938400
加载中
OSCHINA
登录后可查看更多优质内容
返回顶部
顶部