作者:dbtan |【转载时请以超链接形式标明文章出处作者信息】


Oracle 10g Redo的增强:

在Oracle 10g中,log_parallelism参数变为隐含参数,并且Oracle引入了另外两个参数,允许log_parallelism进行动态调整。缺省的_log_parallelism_dynamic参数被设置为True,如果_log_parallelism_max被设置为不同于_log_parallelism的参数值,那么Oracle会动态的选择并行度,当然不超过最大允许值,这是Oracle 10g中动态SGA的另外一个提高。

sys@TQGZS> @GetHidPar.sql
Enter value for par: log_parallelism
old   4: AND x.ksppinm LIKE '%&par%'
new   4: AND x.ksppinm LIKE '%log_parallelism%'
NAME                           VALUE       DESCRIB
------------------------------ ----------- -------------------------------------
_log_parallelism               1           Number of log buffer strands
_log_parallelism_max           1           Maximum number of log buffer strands
_log_parallelism_dynamic       TRUE        Enable dynamic strands

但是需要注意的是当日志并行度被设置大于1之后,LogMiner将不能解析日志文件,ORA-01374号错误提示说明了这个问题:

[oracle@tqgzs: ~]$oerr ora 01374
01374, 00000, "_log_parallelism_max greater than 1 not supported in this release"
// *Cause:  LogMiner does not mine redo records generated with
//          _log_parallelism_max set to a value greater than 1.
// *Action:
//          

相较Oracle 9i中的Public Redolog Strands(缩写为PBRS)Oracle 10gR2中更引入了Private Redolog Strands的概念(缩写为PVRS)在PVRS机制下,数据库可以在共享池中分配大量的小的私有内存,通常每个大小在64~128KB左右,被独立的Redo Allocation Latch所保护,当数据库中特定类型的小事务开始时会被绑定到独立且空闲PVRS,每个Buffer绑定一个活动事务在新的机制下,Redo产生后可以直接存入PVRS,而不再保存在PGA中,这样就不再需要额外的内存拷贝过程,Redo Copy Latch也就不再需要(PVRS也因此被称为ZERO-COPY Redo),而Redo Copy正是引发Redo Allocation Latch竞争的根源

在新的机制下,在进行Redo写出工作时,LGWR需要做的工作就是将PBRSPVRS中的内容写出,当Redo Flush发生时,所有的Public Redo Allocation Latch需要被获取,所有Public Strands的Redo Copy Latch需要被检查,所有包含活动事务的Private Strands需要被持有。

以下是PVRS在共享池中的内存分配信息(注意在RAC环境中不适用PVRS):

sys@TQGZS> select banner from x$version where indx in (0,3);
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
TNS for Linux: Version 10.2.0.1.0 - Production
sys@TQGZS> select * from v$sgastat where name='private strands';
POOL         NAME                                BYTES
------------ ------------------------------ ----------
shared pool  private strands                   1198080

在Oracle 10gR2中,Oracle通过使用多个Redo Allocation Latch来提高并发性能:

sys@TQGZS> select addr,latch#,child#,name,gets,immediate_gets,immediate_misses
  2  from v$latch_children where name='redo allocation';
ADDR         LATCH#     CHILD# NAME                       GETS IMMEDIATE_GETS IMMEDIATE_MISSES
-------- ---------- ---------- -------------------- ---------- -------------- ----------------
2FAD48A8        148         19 redo allocation               5              0                0
2FAD4844        148         18 redo allocation               5              0                0
2FAD47E0        148         17 redo allocation               5              0                0
2FAD477C        148         16 redo allocation               5              0                0
2FAD4718        148         15 redo allocation               5              0                0
2FAD46B4        148         14 redo allocation               5              0                0
2FAD4650        148         13 redo allocation               5              0                0
2FAD45EC        148         12 redo allocation               5              0                0
2FAD4588        148         11 redo allocation               5              0                0
2FAD4524        148         10 redo allocation               5              0                0
2FAD44C0        148          9 redo allocation               5              0                0
2FAD445C        148          8 redo allocation               5              0                0
2FAD43F8        148          7 redo allocation               5              0                0
2FAD4394        148          6 redo allocation               5              0                0
2FAD4330        148          5 redo allocation               5              0                0
2FAD42CC        148          4 redo allocation             615              0                0
2FAD4268        148          3 redo allocation             285              0                0
2FAD4204        148          2 redo allocation             391              0                0
2FAD41A0        148          1 redo allocation            1552          12281                2
19 rows selected.

以下是与PVRS相关的几个隐含参数,缺省的_log_private_parallelism被设置为FALSE

sys@TQGZS> @GetHidPar.sql
Enter value for par: log_private
old   4: AND x.ksppinm LIKE '%&par%'
new   4: AND x.ksppinm LIKE '%log_private%'
NAME                         VALUE     DESCRIB
---------------------------- --------- --------------------------------------------------------------
_log_private_parallelism     FALSE     Number of private log buffer strands for zero-copy redo
_log_private_parallelism_mul 10        Active sessions multiplier to deduce number of private strands
_log_private_mul             5         Private strand multiplier for log space preallocation

PVRS的统计数据可以从v$sysstat视图查询得到,由于IMU(In Memory Undo)与PVRS紧密相关,所以两者的信息具有相关性:

sys@TQGZS> select name,value from v$sysstat where upper(name) like '%IMU%';
NAME                                           VALUE
---------------------------------------- -----------
doubling up with imu segment                       0
IMU commits                                      167
IMU Flushes                                       94
IMU contention                                     0
IMU recursive-transaction flush                    0
IMU undo retention flush                           0
IMU ktichg flush                                   0
IMU bind flushes                                   0
IMU mbu flush                                      0
IMU pool not allocated                             0
IMU CR rollbacks                                   6
IMU undo allocation size                     2453800
IMU Redo allocation size                      758612
IMU- failed to get a private strand                0

OS Maximum resident set size                       0
15 rows selected.

在实施了PVRS之后,在警告日志信息中可能看到如下信息,这里的Private_strands就是新特性引入的新提示:

Tue Mar 11 06:07:02 2008
Private_strands 7 at log switch
Thread 1 advanced to log sequence 1228431
  Current log# 2 seq# 1228431 mem# 0: /databases/app/oracle/oradata/r2gdb/redo02.log

更进一步地,如果收到提示Private strand flush not complet,这是指从内存到Redo Log File的写出尚未完成:

Mon Nov 30 16:25:06 2009
Thread 1 advanced to log sequence 2589
  Current log# 3 seq# 2589 mem# 0: /home/oracle/app/oracle/oradata/ccdb/redo03.log
Thread 1 cannot allocate new log, sequence 2590
Private strand flush not complete
  Current log# 3 seq# 2589 mem# 0: /home/oracle/app/oracle/oradata/ccdb/redo03.log
Thread 1 advanced to log sequence 2590
  Current log# 1 seq# 2590 mem# 0: /home/oracle/app/oracle/oradata/ccdb/redo01.log

通过v$event_name可以找到关于这个新等待的说明:

sys@TQGZS> select name from v$event_name where upper(name) like '%STRAND%';
NAME
--------------------------------------------------
log file switch (private strand flush incomplete)

- The End -