计算oracle redo block size的大小

2011年01月29日 星期六 下午 11:09

计算redo  block size的大小
   LGWR以block为单位把redo写入磁盘，redo block size是Oracle源代码中固定的，与操作系统相关。

通常的操作系统都是以512 bytes为单位，如:Solaris, AIX, Windows NT/2000, Linux 等

这个Log size可以从Oracle的内部视图中获得:

SQL> select max(lebsz) from x$kccle;
            MAX(LEBSZ)
            ----------
            512
            

也可以从v$sysstat中的统计信息中通过计算粗略得到.
以下几个统计信息如:
redo size------------redo信息的大小
redo wastage---------浪费的redo的大小
redo blocks written--LGWR写出的redo block的数量

额外的信息，每个redo block header需要占用16 bytes.
由此可以粗略的计算redo block size如下

SQL> select name,value from v$sysstat
            2  where name in ('redo size','redo wastage','redo blocks written');
            NAME                                                                  VALUE
            ---------------------------------------------------------------- ----------
            redo size                                                           2242628
            redo wastage                                                          63904
            redo blocks written                                                    4657
            SQL> select ceil(16 + (2242628 + 63904)/4657) rbsize from dual;
            RBSIZE
            ----------
            512
 

 

Although the size of redo entries is measured in bytes, LGWR writes the redo to the log files on disk in blocks. The size of redo log blocks is fixed in the Oracle source code and is operating system specific. Oracle's documentation uses the term "operating system block size" to refer to the log block size. Normally it is the smallest unit of I/O supported by the operating system for raw I/O, but on some operating systems it is the smallest possible unit of file system based I/O. The following table shows the most common log block sizes and some of the operating systems that use them.<?XML:NAMESPACE PREFIX = O />

虽然redo entries是以字节为单位的，但是LGWR将redo写入log file还是以块为单位的。redo log block的大小在Oracle代码中是固定的，是依据操作系统的，是操作系统支持的I/O的最小单位

 

 

Log Block Size	Operating Systems
512 bytes	Solaris, AIX, Windows NT/2000, Linux, Irix, DG/UX, OpenVMS, NetWare, UnixWare, DYNIX/ptx
1024 bytes	HP-UX, Tru64 Unix
2048 bytes	SCO Unix, Reliant Unix
4096 bytes	MVS, MPE/ix

从上表可以看出，最常用的操作系统的log block的大小都是512字节。查看这个块大小有很多方法：

从x$kccle中查：

select max(l.lebsz) log_block_size_kccle

from sys.x$kccle l

where l.inst_id = userenv('Instance')

The log block size can also be inferred from the system statistics in StatsPack reports. There is a 16 byte header for each log block, and the size of the data area is approximately the number of bytes of redo generated (redo size) plus the number of bytes of redo space left unused (redo wastage) divided by the number of log blocks written (redo blocks written). Thus the approximate formula is

log block size可以大致由统计信息中推断。每个log block有16个字节的头，数据区域大约是

(redo size（redo信息的大小）+redo wastage(浪费的redo大小))/redo blocks written(LGWR写Redo的块的数量)

16 + (redo size + redo wastage) / redo blocks written

从v$sysstat中查：

 

select ceil(16+(redo_size+redo_wastage)/redo_block_written) log_block_size_sysstat

from(select max(decode(name,'redo size',value)) redo_size,

          max(decode(name,'redo wastage',value)) redo_wastage,

          max(decode(name,'redo blocks written',value)) redo_block_written

from (select name,value from v$sysstat

where name in('redo size','redo wastage','redo blocks written')))

This formula will commonly understate the log block size by a few bytes, because it does not allow for redo that has been generated but not yet written, and the redo size statistic is commonly a little inaccurate.

按道理redo block written *(512-16)应该等于redo size+redo wastage的，现在还不是很理解，准备发一个帖子请教论坛高手

另外，既然redo写入log file都是以block为单位，那在LGWR频繁给触发时，必然存在block还没有写满就被写入了log file，针对这样的场景如何理解----看了redo wastage就明白了