Oracle Redo log并行机制详解(1)(2)
注意：对于未能获取到Private strand的redo allocation latch的事务，在事务结束前，即使已经有其它事务释放了Private strand，也不会再申请Private strand了。
每个Private strand的大小为65K。10g中，shared pool中的Private strands的大小就是活跃会话数乘以65K，而11g中，在shared pool中需要为每个Private strand额外分配4k的管理空间，即：数量*69k。
- --10g:
- SQL> select * from V$sgastat where name like '%strand%';
- POOL NAME BYTES
- ------------ -------------------------- ----------
- shared pool private strands 1198080
- HELLODBA.COM>select trunc(value * KSPPSTVL / 100) * 65 * 1024
- 2 from (select value from v$parameter where name = 'transactions') a,
- 3 (select val.KSPPSTVL
- 4 from sys.x$ksppi nam, sys.x$ksppsv val
- 5 where nam.indx = val.indx
- 6 AND nam.ksppinm = '_log_private_parallelism_mul') b;
- TRUNC(VALUE*KSPPSTVL/100)*65*1024
- -------------------------------------
- 1198080
- --11g:
- HELLODBA.COM>select * from V$sgastat where name like '%strand%';
- POOL NAME BYTES
- ------------ -------------------------- ----------
- shared pool private strands 706560
- HELLODBA.COM>select trunc(value * KSPPSTVL / 100) * (65 + 4) * 1024
- 2 from (select value from v$parameter where name = 'transactions') a,
- 3 (select val.KSPPSTVL
- 4 from sys.x$ksppi nam, sys.x$ksppsv val
- 5 where nam.indx = val.indx
- 6 AND nam.ksppinm = '_log_private_parallelism_mul') b;
- TRUNC(VALUE*KSPPSTVL/100)*(65+4)*1024
- -------------------------------------
- 706560
Private strand的数量受到2个方面的影响：logfile的大小和活跃事务数量。
参数_log_private_mul指定了使用多少logfile空间预分配给Private strand，默认为5。我们可以根据当前logfile的大小(要除去预分配给log buffer的空间)计算出这一约束条件下能够预分配多少个Private strand：
- HELLODBA.COM>select bytes from v$log where status = 'CURRENT';
- BYTES
- ----------
- 52428800
- HELLODBA.COM>select trunc(((select bytes from v$log where status = 'CURRENT') - (select to_number(value) from v$parameter where name = 'log_buffer'))*
- 2 (select to_number(val.KSPPSTVL)
- 3 from sys.x$ksppi nam, sys.x$ksppsv val
- 4 where nam.indx = val.indx
- 5 AND nam.ksppinm = '_log_private_mul') / 100 / 66560)
- 6 as "calculated private strands"
- 7 from dual;
- calculated private strands
- --------------------------
- 5
- HELLODBA.COM>select count(1) "actual private strands" from x$kcrfstrand where last_buf_kcrfa = '00';
- actual private strands
- ----------------------
- 5
当logfile切换后(和checkpoint一样，切换之前必须要将所有Private strand的内容flush到logfile中，因此我们在alert log中可能会发现日志切换信息之前会有这样的信息："Private strand flush not complete"，这是可以被忽略的)，会重新根据切换后的logfile的大小计算对Private strand的限制：
- HELLODBA.COM>alter system switch logfile;
- System altered.
- HELLODBA.COM>select bytes from v$log where status = 'CURRENT';
- BYTES
- ----------
- 104857600
- HELLODBA.COM>select trunc(((select bytes from v$log where status = 'CURRENT') - (select to_number(value) from v$parameter where name = 'log_buffer'))*
- 2 (select to_number(val.KSPPSTVL)
- 3 from sys.x$ksppi nam, sys.x$ksppsv val
- 4 where nam.indx = val.indx
- 5 AND nam.ksppinm = '_log_private_mul') / 100 / 66560)
- 6 as "calculated private strands"
- 7 from dual;
- calculated private strands
- --------------------------
- 13
- HELLODBA.COM>select count(1) "actual private strands" from x$kcrfstrand where last_buf_kcrfa = '00';
- actual private strands
- ----------------------
- 13
参数_log_private_parallelism_mul用于推算活跃事务数量在最大事务数量中的百分比，默认为10。Private strand的数量不能大于活跃事务的数量。
- HELLODBA.COM>show parameter transactions
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- transactions integer 222
- transactions_per_rollback_segment integer 5
- HELLODBA.COM>select trunc((select to_number(value) from v$parameter where name = 'transactions') *
- 2 (select to_number(val.KSPPSTVL)
- 3 from sys.x$ksppi nam, sys.x$ksppsv val
- 4 where nam.indx = val.indx
- 5 AND nam.ksppinm = '_log_private_parallelism_mul') / 100 )
- 6 as "calculated private strands"
- 7 from dual;
- calculated private strands
- --------------------------
- 22
- HELLODBA.COM>select count(1) "actual private strands" from x$kcrfstrand where last_buf_kcrfa = '00';
- actual private strands
- ----------------------
- 22
注：在预分配Private strand时，会选择上述2个条件限制下最小一个数量。但相应的shared pool的内存分配和redo allocation latch的数量是按照活跃事务数预分配的。
因此，如果logfile足够大，_log_private_parallelism_mul与实际活跃进程百分比基本相符的话，Private strand的引入基本可以消除redo allocation latch的争用问题。
