redo wastage
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: redo wastage
今天在看oracle core 第6章时，里面提到了redo wastage的概念，针对该概念，以前
也有所了解，不过并不是太清楚，今天正好简单记录一下。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 |
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. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
------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。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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，其实
可以用的空间大概如下：
|
1 2 3 4 5 6 7 8 9 10 11 |
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 比较严重的情况下，我们才会加以关注。
2 Responses to “redo wastage”
大家也可以参考下这个链接 http://www.ixora.com.au/notes/redo_wastage.htm
问一个问题，怎么从日志的块头信息看出块头的长度为16bytes的？
Leave a Reply
You must be logged in to post a comment.