HelloDBA [中文]
Search Internet Search HelloDBA
Home
Tech Documents
Products & Download
Subscribe
  • To Google
  • To Yahoo
  • To zhuxia
  • To xianguo
  • To QQ email
  • To YouDao
About
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Oracle Redo Strand

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2009-12-18 06:26:13

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

  Redo log is a important data to be used to recovery and some other advanced features. A redo entry involved the information about database changing. All of the redo entries should be writen into logfile finally. To avoid disk io waits, oracle allocated a log buffer from sga to cache the redo data. A redo entry is generated in PGA, then it be copied to log buffer by server process. When reach some conditions, LGWR will write the log buffer contents to the logfiles. Log buffer is shared by all of server processes, to protect it, the server process should first request the "redo allocation latch" to allocate buffer from it. Hence, in high concurrent OLTP system, we may observer the redo allocation latch contentions. Below is the flow of the redo be writen into log buffer:
 

  Generate Redo Entry in PGA -> Server process request Redo Copy latch (which is multiple, 2*CPU_COUNT default) -> Server process request the single redo allocation latch -> allocate log buffer -> release redo allocation latch -> Copy Redo Entrys into Log Buffer -> release Redo Copy latch
 

shared strand

  To reduce redo allocation latch waits, oracle introduced log buffer parallelism in 9.2. It split the log buffer into multiple small buffers, which are named strands (to distinguish from the private strands that will be instroduced later, we also call it "shared strands"). Each shared strand is protected by a redo allocation latch separately. With multiple shared strands, the log buffer allocation become parallelism from sequence, and redo allocation latch contentions reduced correspondly.
 

  Shared strands number is controlled by the parameter log_parallelism, which is undocumented parameter in 10g, and obsoloted in 11g. After 10g, new parameter _log_parallelism_max define the max shared strands number, and _log_parallelism_dynamic identify if the shared strands number could be dynamiy.
 

SQL代码
  1. HELLODBA.COM>select  nam.ksppinm, val.KSPPSTVL, nam.ksppdesc   
  2.   2  from    sys.x$ksppi nam,   
  3.   3          sys.x$ksppsv val   
  4.   4  where nam.indx = val.indx   
  5.   5  --AND   nam.ksppinm LIKE '_%'   
  6.   6  AND   upper(nam.ksppinm) LIKE '%LOG_PARALLE%';   
  7.   
  8. KSPPINM                    KSPPSTVL   KSPPDESC   
  9. -------------------------- ---------- ------------------------------------------   
  10. _log_parallelism           1          Number of log buffer strands   
  11. _log_parallelism_max       2          Maximum number of log buffer strands   
  12. _log_parallelism_dynamic   TRUE       Enable dynamic strands  

  Shared strand size = log_buffer/(shared strands number). We could get the strands information from x$kcrfstrand, which include shared strands and private strands in 10g later.
 

SQL代码
  1. HELLODBA.COM>select indx,strand_size_kcrfa from x$kcrfstrand where last_buf_kcrfa != '00';   
  2.   
  3.       INDX STRAND_SIZE_KCRFA   
  4. ---------- -----------------   
  5.          0           3514368   
  6.          1           3514368   
  7.   
  8. HELLODBA.COM>show parameter log_buffer   
  9.   
  10. NAME                                 TYPE        VALUE   
  11. ------------------------------------ ----------- ------------------------------   
  12. log_buffer                           integer     7028736   

  For multiple CPU host, the default shared strands number is 2 if less than/equal to 16 CPU. If observed redo allocation latch contentions, should consider increase 1 strand for every 16 CPU, but should not more than 8. Besides, _log_parallelism_max could not larger than cpu_count.
 

Private strand

  To reduce redo allocation lacth contention further, a new mechanism is introduced in 10g, which is Private Strand. Private Strand is the buffer allicated from shared pool instead of log buffer.
 

SQL代码
  1. HELLODBA.COM>select * from V$sgastat where name like '%strand%';   
  2.   
  3. POOL         NAME                            BYTES   
  4. ------------ -------------------------- ----------   
  5. shared pool  private strands               2684928   
  6.   
  7. HELLODBA.COM>select indx,strand_size_kcrfa from x$kcrfstrand where last_buf_kcrfa = '00';   
  8.   
  9.       INDX STRAND_SIZE_KCRFA   
  10. ---------- -----------------   
  11.          2             66560   
  12.          3             66560   
  13.          4             66560   
  14.          5             66560   
  15.          6             66560   
  16.          7             66560   
  17.          8             66560   
  18. ...   

  Redo/Undo mechanism is changed much by private strands. Every private strand is protected by a redo allocation latch. As "private", each strand only servers for 1 active transaction. The transaction that using private strand generate redo entries in private strand instead of PGA. The redo entried in private strand will be batch writen into logfile when flush or commit. If a transaction failed to request a private strand, it will request the shared privated as old mechanism. We can identify the transaction used private strands or not by the 13th bit of ktcxbflg in x$ktcxb.
 

SQL代码
  1. HELLODBA.COM>select decode(bitand(ktcxbflg, 4096),0,1,0) used_private_strand, count(*)   
  2.   2    from x$ktcxb   
  3.   3   where bitand(ksspaflg, 1) != 0   
  4.   4     and bitand(ktcxbflg, 2) != 0   
  5.   5   group by bitand(ktcxbflg, 4096);   
  6.   
  7. USED_PRIVATE_STRAND   COUNT(*)   
  8. ------------------- ----------   
  9.                   1         10   
  10.                   0          1   

  The transactions using private strand will not request Redo Copy Latch and redo allocation latch of shared strand when generate Redo Entry, just request Redo Copy Latch before flush strand, thus reduced the contentions of those latches, also reduced the CPU workload. 
 

  Note: For those transactions failed get private strand, even though there is private strand available before transaction end, it will not request it.
 

  Size of private strand is 65K (129k on 64 bit system). Private strands buffer size in shared is active transactions plus 65k in 10g, and 69k (additional 4k for management) in 11g.
 

SQL代码
  1. --10g:   
  2. SQL> select * from V$sgastat where name like '%strand%';   
  3.   
  4. POOL         NAME                            BYTES   
  5. ------------ -------------------------- ----------   
  6. shared pool  private strands               1198080   
  7.   
  8. HELLODBA.COM>select trunc(value * KSPPSTVL / 100) * 65 * 1024   
  9.   2    from (select value from v$parameter where name = 'transactions') a,   
  10.   3         (select val.KSPPSTVL   
  11.   4            from sys.x$ksppi nam, sys.x$ksppsv val   
  12.   5           where nam.indx = val.indx   
  13.   6             AND nam.ksppinm = '_log_private_parallelism_mul') b;   
  14.   
  15. TRUNC(VALUE*KSPPSTVL/100)*65*1024   
  16. -------------------------------------   
  17.                               1198080   
  18.   
  19. --11g:   
  20. HELLODBA.COM>select * from V$sgastat where name like '%strand%';   
  21.   
  22. POOL         NAME                            BYTES   
  23. ------------ -------------------------- ----------   
  24. shared pool  private strands                706560   
  25.   
  26. HELLODBA.COM>select trunc(value * KSPPSTVL / 100) * (65 + 4) * 1024   
  27.   2    from (select value from v$parameter where name = 'transactions') a,   
  28.   3         (select val.KSPPSTVL   
  29.   4            from sys.x$ksppi nam, sys.x$ksppsv val   
  30.   5           where nam.indx = val.indx   
  31.   6             AND nam.ksppinm = '_log_private_parallelism_mul') b;   
  32.   
  33. TRUNC(VALUE*KSPPSTVL/100)*(65+4)*1024   
  34. -------------------------------------   
  35.                                706560   

  Private strands number affected by 2 factors, size of logfile and active transaction number.
 

  Undocumented parameter _log_private_mul define the percent of logfile space (without the space reserved for log buffer) should be pre-allocate for private strands. We could calculate the limited private strands number under current logfile size.
 

SQL代码
  1. HELLODBA.COM>select bytes from v$log where status = 'CURRENT';   
  2.   
  3.      BYTES   
  4. ----------   
  5.   52428800   
  6.   
  7. HELLODBA.COM>select trunc(((select bytes from v$log where status = 'CURRENT') - (select to_number(value) from v$parameter where name = 'log_buffer'))*   
  8.   2         (select to_number(val.KSPPSTVL)   
  9.   3            from sys.x$ksppi nam, sys.x$ksppsv val   
  10.   4           where nam.indx = val.indx   
  11.   5             AND nam.ksppinm = '_log_private_mul') / 100 / 66560)   
  12.   6         as "calculated private strands"  
  13.   7    from dual;   
  14.   
  15. calculated private strands   
  16. --------------------------   
  17.                          5   
  18.   
  19. HELLODBA.COM>select count(1) "actual private strands" from x$kcrfstrand where last_buf_kcrfa = '00';   
  20.   
  21. actual private strands   
  22. ----------------------   
  23.                      5   

  After logfile switch (like checkpoint, all of private strand should be flushed into logfile before switch. We may found such information in alert log before the logfile switch entry, as "Private strand flush not complete", which could be ignored), oracle will re-allocate private strands base on current logfile size.
 

SQL代码
  1. HELLODBA.COM>alter system switch logfile;   
  2.   
  3. System altered.   
  4.   
  5. HELLODBA.COM>select bytes from v$log where status = 'CURRENT';   
  6.   
  7.      BYTES   
  8. ----------   
  9.  104857600   
  10.   
  11. HELLODBA.COM>select trunc(((select bytes from v$log where status = 'CURRENT') - (select to_number(value) from v$parameter where name = 'log_buffer'))*   
  12.   2         (select to_number(val.KSPPSTVL)   
  13.   3            from sys.x$ksppi nam, sys.x$ksppsv val   
  14.   4           where nam.indx = val.indx   
  15.   5             AND nam.ksppinm = '_log_private_mul') / 100 / 66560)   
  16.   6         as "calculated private strands"  
  17.   7    from dual;   
  18.   
  19. calculated private strands   
  20. --------------------------   
  21.                         13   
  22.   
  23. HELLODBA.COM>select count(1) "actual private strands" from x$kcrfstrand where last_buf_kcrfa = '00';   
  24.   
  25. actual private strands   
  26. ----------------------   
  27.                     13  

  Parameter _log_private_parallelism_mul is used to deduce the active transactions number, which is percent of max transactions. Private strands number is limited in active transactions number.
 

SQL代码
  1. HELLODBA.COM>show parameter transactions   
  2.   
  3. NAME                                 TYPE        VALUE   
  4. ------------------------------------ ----------- ------------------------------   
  5. transactions                         integer     222   
  6. transactions_per_rollback_segment    integer     5   
  7. HELLODBA.COM>select trunc((select to_number(value) from v$parameter where name = 'transactions') *   
  8.   2         (select to_number(val.KSPPSTVL)   
  9.   3            from sys.x$ksppi nam, sys.x$ksppsv val   
  10.   4           where nam.indx = val.indx   
  11.   5             AND nam.ksppinm = '_log_private_parallelism_mul') / 100 )   
  12.   6         as "calculated private strands"  
  13.   7    from dual;   
  14.   
  15. calculated private strands   
  16. --------------------------   
  17.                         22   
  18.   
  19. HELLODBA.COM>select count(1) "actual private strands" from x$kcrfstrand where last_buf_kcrfa = '00';   
  20.   
  21. actual private strands   
  22. ----------------------   
  23.                     22   

  When pre-allocate private strands, oracle will choose the less number limited by the 2 factors just mentioned. However, as the logfile size could be changed online, the total size of private strands buffer in shared pool and the redo allocation latch number is allocated as active transaction number.
 

  Hence, if logfile is large enough, and active transactios number setting is suitable, private strands will almost eliminate redo allocation latch contentions.
 

    --- Fuyuncat Mark ---
Previous： Next：
Return To this Category

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat