日志缓冲区失败率

1. 查询LOG_BUFFER参数

 SQL> show parameter log_buffer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
log_buffer                           integer     2899456

2. 查询用户进程等待日志缓冲区的次数,通过数据字典v$sysstat :

SQL> select name,value
  2  from v$sysstat
  3  where name='redo buffer allocation retries';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo buffer allocation retries                                            0

3. 日志缓冲区过小,会增加日志写盘的次数,增加了系统的I/O负担.对于日志缓冲区而言,可以计算失败率.

使用数据字典v$LATCH计算日志缓冲区的失败率:

SQL> select name,gets,misses,immediate_gets,immediate_misses,
  2  Decode(gets,0,0,misses/gets*100) ratio1,
  3  Decode(immediate_gets+immediate_misses,0,0,
  4  immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
  5  from v$latch
  6  where name in('redo allocation','redo copy');

NAME                                                     GETS     MISSES
-------------------------------------------------- ---------- ----------
IMMEDIATE_GETS IMMEDIATE_MISSES     RATIO1     RATIO2
-------------- ---------------- ---------- ----------
redo copy                                                  18          0
         89274               36          0 .040309036

redo allocation                                          4644          1
         89276                0 .021533161          0

其中,GETS表示成功等待日志缓冲区的次数.IMMEDIATE_GETS表示成功立即得到日志缓冲区的次数.IMMEDIATE_MISSES表示未成功立即得到日志缓冲区的次数.

等待表示日志在进入日志缓冲区时因为日志缓冲区过小而没有空闲空间,所以日志缓冲区的失败率表示了日志缓冲区是否足够大.不够大时,用户的日志写将产生等待过程.对于日志缓存区的失败率,应该小于1%.