对redo wastage这个概念的说明

redo wastage

本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客

本文链接地址: redo wastage

今天在看oracle core 第6章时，里面提到了redo wastage的概念，针对该概念，以前
也有所了解，不过并不是太清楚，今天正好简单记录一下。

SQL> SELECT name,VALUE FROM v$sysstat WHERE name LIKE '%redo%';   NAME VALUE ---------------------------------------------------------------- ---------- redo synch writes 264 redo synch TIME 344 redo blocks READ FOR recovery 25479 redo entries 2158328 redo SIZE 605152164 redo buffer allocation retries 46 redo wastage 1357540 redo writer latching TIME 16 redo writes 5050 redo blocks written 1225607 redo WRITE TIME 5262   NAME VALUE ---------------------------------------------------------------- ---------- redo log SPACE requests 35 redo log SPACE wait TIME 177 redo log switch interrupts 0 redo ordering marks 4 redo subscn MAX counts 0 redo WRITE broadcast ack TIME 0   17 ROWS selected.   SQL> CREATE TABLE t AS SELECT * FROM dba_objects;   TABLE created.   SQL> SELECT name,VALUE FROM v$sysstat WHERE name LIKE '%redo%';   NAME VALUE ---------------------------------------------------------------- ---------- redo synch writes 265 redo synch TIME 344 redo blocks READ FOR recovery 25479 redo entries 2159471 redo SIZE 611007860 redo buffer allocation retries 46 redo wastage 1358616 redo writer latching TIME 16 redo writes 5059 redo blocks written 1237416 redo WRITE TIME 5266   NAME VALUE ---------------------------------------------------------------- ---------- redo log SPACE requests 35 redo log SPACE wait TIME 177 redo log switch interrupts 0 redo ordering marks 4 redo subscn MAX counts 0 redo WRITE broadcast ack TIME 0   17 ROWS selected.   SQL> SQL> SELECT a.name,b.VALUE 2 FROM v$statname a ,v$mystat b 3 WHERE a.statistic#=b.statistic# AND a.name LIKE '%redo%';   NAME VALUE ---------------------------------------------------------------- ---------- redo synch writes 5 redo synch TIME 0 redo blocks READ FOR recovery 0 redo entries 12561 redo SIZE 14319932 redo buffer allocation retries 0 redo wastage 0 redo writer latching TIME 0 redo writes 0 redo blocks written 0 redo WRITE TIME 0   NAME VALUE ---------------------------------------------------------------- ---------- redo log SPACE requests 0 redo log SPACE wait TIME 0 redo log switch interrupts 0 redo ordering marks 0 redo subscn MAX counts 0 redo WRITE broadcast ack TIME 0   17 ROWS selected.   SQL> SQL> BEGIN 2 FOR i IN 1 .. 100 loop 3 DELETE FROM t WHERE rownum SELECT name,VALUE FROM v$sysstat WHERE name LIKE '%redo%';   NAME VALUE ---------------------------------------------------------------- ---------- redo synch writes 266 redo synch TIME 344 redo blocks READ FOR recovery 25479 redo entries 2159888 redo SIZE 611345536 redo buffer allocation retries 46 redo wastage 1366480 redo writer latching TIME 16 redo writes 5097 redo blocks written 1238113 redo WRITE TIME 5270   NAME VALUE ---------------------------------------------------------------- ---------- redo log SPACE requests 35 redo log SPACE wait TIME 177 redo log switch interrupts 0 redo ordering marks 4 redo subscn MAX counts 0 redo WRITE broadcast ack TIME 0   17 ROWS selected.   SQL> SELECT a.name, b.VALUE 2 FROM v$statname a, v$mystat b 3 WHERE a.statistic# = b.statistic# 4 AND a.name LIKE '%redo%';   NAME VALUE ---------------------------------------------------------------- ---------- redo synch writes 6 redo synch TIME 0 redo blocks READ FOR recovery 0 redo entries 12925 redo SIZE 14652304 redo buffer allocation retries 0 redo wastage 0 redo writer latching TIME 0 redo writes 0 redo blocks written 0 redo WRITE TIME 0   NAME VALUE ---------------------------------------------------------------- ---------- redo log SPACE requests 0 redo log SPACE wait TIME 0 redo log switch interrupts 0 redo ordering marks 0 redo subscn MAX counts 0 redo WRITE broadcast ack TIME 0   17 ROWS selected.

------Redo wastage size (bytes)   SQL> select (1366480-1358616) from dual;   (1366480-1358616) ----------------- 7864   ---Redo log size (bytes)   SQL> select 14652304-14319932 from dual; ---session级别   14652304-14319932 ----------------- 332372   SQL> select 611345536-611007860 from dual; ---系统级别   611345536-611007860 ------------------- 337676   SQL>   对于多出的redo log bytes，其实就是递归操作产生的。   SQL> select 7864/337676 from dual;   7864/337676 ----------- .023288596   我们可以发现，此时redo log 浪费的比例约为2.3%。此时的redo log block_size为512 bytes。

SQL> conn /AS sysdba Connected. SQL> SELECT MAX(lebsz) FROM x$kccle;   MAX(LEBSZ) ---------- 512   SQL> SELECT * FROM v$log;   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 286 52428800 1 NO CURRENT 3411998 17-JUN-12 2 1 284 52428800 1 YES INACTIVE 3407009 17-JUN-12 3 1 285 52428800 1 YES INACTIVE 3410228 17-JUN-12   SQL> SELECT * FROM v$logfile;   GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ------------------------------------------------------------ --- 3 ONLINE /home/ora10g/oradata/roger/redo03.log NO 2 ONLINE /home/ora10g/oradata/roger/redo02.log NO 1 ONLINE /home/ora10g/oradata/roger/redo01.log NO   SQL> ALTER SYSTEM DUMP LOGFILE '/home/ora10g/oradata/roger/redo01.log';   System altered.   -----trace如下： DUMP OF REDO FROM FILE '/home/ora10g/oradata/roger/redo01.log' Opcodes *.* RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff Times: creation thru eternity FILE HEADER: Compatibility Vsn = 169870592=0xa200500 Db ID=2466925865=0x930a4d29, Db Name='ROGER' Activation ID=2470475068=0x9340753c Control Seq=4397=0x112d, File SIZE=102400=0x19000 File NUMBER=1, Blksiz=512, File TYPE=2 LOG

这里部分内容其实是redo log 的头部，一共16个bytes，所以我这里50m的redo log，其实
可以用的空间大概如下：

SQL> SELECT 50*1024*1024 - (50*1024*1024*0.023 + 102400*16) FROM dual;   50*1024*1024-(50*1024*1024*0.023+102400*16) ------------------------------------------- 49584537.6   SQL> SELECT 49584537.6/52428800 FROM dual;   49584537.6/52428800 ------------------- .94575

从上来看来，其实对于50m的redo log，实际上可用的空间的比例约为94.5左右，同理，对于archivelog 也是一样
的道理，对于产生的50m的archive log而言，里面有5.5m是空洞。

为什么会产生redo wastage的情况下，可用这样简单的理解：

oracle 写log buffer内容到disk的过程，仍然是以block为单位的，redo log block size为512 bytes,也就是说，
oracle每次写都是block size的整数倍，如果说某个时刻，lgwr进程在写某个block，仅仅写了400个bytes，此时
事务commit或者发现redo log 切换，该block将被写入到redo log file中，而此时该block中还有112 个bytes是空的。

针对这类的redo wastage 浪费比例计算，我这样计算其实并不准确，严格来讲，应该计算多天的平均值。

在oracle core第6章中，L大师提到，对于block size更大的情况，redo wastage可能也就更为严重，但是redo log
block大小不是我们能够决定的，是由操作系统本身决定的，针对不同的平台，redo log block size不同，如下表：

oracle 为什么要这样设计，L大师是这样简单总结了一句话：
So Oracle’s strategy is simple: move the pointer to the end of the block, write the block, never read,
and never look back. The aim is to keep the code as simple as possible and keep streaming the log to disk
without interruptions.

总的来说，只有redo wastage 比较严重的情况下，我们才会加以关注。