关于redo wastage的测试
通过查看v$sysstat视图可以找到与redo相关的状态记录：
select name, value from v$sysstat where name like '%redo%';
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production（32个参数）
| redo blocks read for recovery | 0 |
| redo k-bytes read for recovery | 0 |
| redo k-bytes read for terminal recovery | 0 |
| redo entries | 2415619 |
| redo size | 2894156244 |
| redo entries for lost write detection | 0 |
| redo size for lost write detection | 0 |
| redo size for direct writes | 2298332080 |
| redo buffer allocation retries | 304 |
| redo wastage | 146017332 |
| redo writes | 379231 |
| redo blocks written | 6129247 |
| redo write time | 25647 |
| redo blocks checksummed by FG (exclusive) | 4593510 |
| redo blocks checksummed by LGWR | 0 |
| redo log space requests | 14 |
| redo log space wait time | 12 |
| redo ordering marks | 396 |
| redo subscn max counts | 26943 |
| redo write broadcast ack time | 0 |
| redo write broadcast ack count | 0 |
| redo write broadcast lgwr post count | 0 |
| redo synch time | 436 |
| redo synch time (usec) | 4634610 |
| redo synch writes | 1228 |
| redo synch long waits | 18 |
| redo synch poll writes | 0 |
| redo synch polls | 0 |
| redo KB read | 3064772 |
| redo KB read (memory) | 0 |
| redo KB read for transport | 0 |
| redo KB read (memory) for transport | 0 |
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi （16个）
| redo synch writes | 226341116 |
| redo synch time | 20660928 |
| redo blocks read for recovery | 0 |
| redo entries | 6683152057 |
| redo size | 2.36827E+12 |
| redo buffer allocation retries | 159174 |
| redo wastage | 79127052484 |
| redo writer latching time | 1754 |
| redo writes | 274230209 |
| redo blocks written | 4941554269 |
| redo write time | 15881292 |
| redo log space requests | 234573 |
| redo log space wait time | 4492908 |
| redo log switch interrupts | 0 |
| redo ordering marks | 167582252 |
| redo subscn max counts | 228984354 |
观察数值是在不断变化的
| redo blocks read for recovery | 0 |
| redo k-bytes read for recovery | 0 |
| redo k-bytes read for terminal recovery | 0 |
| redo entries | 2416776 |
| redo size | 2894279508 |
| redo entries for lost write detection | 0 |
| redo size for lost write detection | 0 |
| redo size for direct writes | 2298332080 |
| redo buffer allocation retries | 304 |
| redo wastage | 146442604 |
| redo writes | 380301 |
| redo blocks written | 6130353 |
| redo write time | 25685 |
| redo blocks checksummed by FG (exclusive) | 4593521 |
| redo blocks checksummed by LGWR | 0 |
| redo log space requests | 14 |
| redo log space wait time | 12 |
| redo ordering marks | 396 |
| redo subscn max counts | 26952 |
| redo write broadcast ack time | 0 |
| redo write broadcast ack count | 0 |
| redo write broadcast lgwr post count | 0 |
| redo synch time | 436 |
| redo synch time (usec) | 4635808 |
| redo synch writes | 1234 |
| redo synch long waits | 18 |
| redo synch poll writes | 0 |
| redo synch polls | 0 |
| redo KB read | 3065328 |
| redo KB read (memory) | 0 |
| redo KB read for transport | 0 |
| redo KB read (memory) for transport | 0 |
比如redo entries 该参数测试时发现系统无业务状态下不断递增，应该是oracle内部的进程产生的事务在不断的刷入redo空间中导致redo条目的不断增加，我这里取了几组数据进行计算：
2416441-2416371=70
2894247796-2894241040=6756
6756/70=96.514
2416580-2416441=139
2894260852-2894247796=13056
13056/139=93.928
2416776-2416371=405
2894279508-2894241040=38468
38468/405=94.9827
上面的计算值说明不了什么，只是想确认一下在无业务状态下redo条目每增加一条时产生的日志redo大小为多少，在11G下算出来的平均值为95。
redo entries:
2416776-2415619=1157
2416776-2415619=1157
redo size:
2894279508-2894156244=123264
2894279508-2894156244=123264
redo blocks written:
6130353-6129247=1106
6130353-6129247=1106
redo wastage:
146442604-146017332=425272
146442604-146017332=425272
redo blocks written (redo block 16bytes block header)
1106*512-1106*16=548576
redo writes
380301-379231=1070
redo size (increment)+redo wastage
123264+425272=548536
从以上的计算大致得出一个公式为：
redo size (increment)+redo wastage ≈ redo blocks written* （redo block size - redo block header size）
这里我尝试对数据库进行一些操作，观察redo状态的变化。先查看当前的数据库redo的状态值：
select name, value from v$sysstat where name like '%redo%';
select name,value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and name like '%redo%';
系统级别状态值
| redo blocks read for recovery | 0 |
| redo k-bytes read for recovery | 0 |
| redo k-bytes read for terminal recovery | 0 |
| redo entries | 2428219 |
| redo size | 2914140772 |
| redo entries for lost write detection | 0 |
| redo size for lost write detection | 0 |
| redo size for direct writes | 2315553328 |
| redo buffer allocation retries | 304 |
| redo wastage | 148178848 |
| redo writes | 384731 |
| redo blocks written | 6173905 |
| redo write time | 25852 |
| redo blocks checksummed by FG (exclusive) | 4628221 |
| redo blocks checksummed by LGWR | 0 |
| redo log space requests | 14 |
| redo log space wait time | 12 |
| redo ordering marks | 396 |
| redo subscn max counts | 27169 |
| redo write broadcast ack time | 0 |
| redo write broadcast ack count | 0 |
| redo write broadcast lgwr post count | 0 |
| redo synch time | 436 |
| redo synch time (usec) | 4640960 |
| redo synch writes | 1252 |
| redo synch long waits | 18 |
| redo synch poll writes | 0 |
| redo synch polls | 0 |
| redo KB read | 3078453 |
| redo KB read (memory) | 0 |
| redo KB read for transport | 0 |
| redo KB read (memory) for transport | 0 |
会话级别状态值
| redo blocks read for recovery | 0 |
| redo k-bytes read for recovery | 0 |
| redo k-bytes read for terminal recovery | 0 |
| redo entries | 3733 |
| redo size | 17371104 |
| redo entries for lost write detection | 0 |
| redo size for lost write detection | 0 |
| redo size for direct writes | 17081372 |
| redo buffer allocation retries | 0 |
| redo wastage | 0 |
| redo writes | 0 |
| redo blocks written | 0 |
| redo write time | 0 |
| redo blocks checksummed by FG (exclusive) | 0 |
| redo blocks checksummed by LGWR | 0 |
| redo log space requests | 0 |
| redo log space wait time | 0 |
| redo ordering marks | 0 |
| redo subscn max counts | 62 |
| redo write broadcast ack time | 0 |
| redo write broadcast ack count | 0 |
| redo write broadcast lgwr post count | 0 |
| redo synch time | 0 |
| redo synch time (usec) | 883 |
| redo synch writes | 6 |
| redo synch long waits | 0 |
| redo synch poll writes | 0 |
| redo synch polls | 0 |
| redo KB read | 0 |
| redo KB read (memory) | 0 |
| redo KB read for transport | 0 |
| redo KB read (memory) for transport | 0 |
这里我尝试创建一个表，看看redo统计信息的变化。
select count(*) from all_objects; （result：72815）
create table tredo as select * from all_objects; 执行4.431s
系统级别状态值
| redo blocks read for recovery | 0 |
| redo k-bytes read for recovery | 0 |
| redo k-bytes read for terminal recovery | 0 |
| redo entries | 2430120 |
| redo size | 2922845056 |
| redo entries for lost write detection | 0 |
| redo size for lost write detection | 0 |
| redo size for direct writes | 2324093992 |
| redo buffer allocation retries | 304 |
| redo wastage | 148233420 |
| redo writes | 384899 |
| redo blocks written | 6191566 |
| redo write time | 25869 |
| redo blocks checksummed by FG (exclusive) | 4644404 |
| redo blocks checksummed by LGWR | 0 |
| redo log space requests | 14 |
| redo log space wait time | 12 |
| redo ordering marks | 396 |
| redo subscn max counts | 27176 |
| redo write broadcast ack time | 0 |
| redo write broadcast ack count | 0 |
| redo write broadcast lgwr post count | 0 |
| redo synch time | 438 |
| redo synch time (usec) | 4661320 |
| redo synch writes | 1253 |
| redo synch long waits | 18 |
| redo synch poll writes | 0 |
| redo synch polls | 0 |
| redo KB read | 3078453 |
| redo KB read (memory) | 0 |
| redo KB read for transport | 0 |
| redo KB read (memory) for transport | 0 |
会话级别状态值
| redo blocks read for recovery | 0 |
| redo k-bytes read for recovery | 0 |
| redo k-bytes read for terminal recovery | 0 |
| redo entries | 5356 |
| redo size | 26010532 |
| redo entries for lost write detection | 0 |
| redo size for lost write detection | 0 |
| redo size for direct writes | 25622036 |
| redo buffer allocation retries | 0 |
| redo wastage | 0 |
| redo writes | 0 |
| redo blocks written | 0 |
| redo write time | 0 |
| redo blocks checksummed by FG (exclusive) | 0 |
| redo blocks checksummed by LGWR | 0 |
| redo log space requests | 0 |
| redo log space wait time | 0 |
| redo ordering marks | 0 |
| redo subscn max counts | 64 |
| redo write broadcast ack time | 0 |
| redo write broadcast ack count | 0 |
| redo write broadcast lgwr post count | 0 |
| redo synch time | 2 |
| redo synch time (usec) | 21243 |
| redo synch writes | 7 |
| redo synch long waits | 0 |
| redo synch poll writes | 0 |
| redo synch polls | 0 |
| redo KB read | 0 |
| redo KB read (memory) | 0 |
| redo KB read for transport | 0 |
| redo KB read (memory) for transport | 0 |
获取对应的数据进行简单计算
redo wastage：
148233420-148178848=54572
redo size：
sys：2922845056-2914140772=8704284
session：26010532-17371104=8639428
redo block written：
6191566-6173905=17661
54572+8704284 = 8758856
17661*512-17661*16=8759856
redo entries：
5356-3733=1623
与前面总结公式大致相符，同时发现DDL语句操作因为伴随则数据插入的操作，操作记录应该也被记录到了redo buffer中，但是否插入的数据与redo buffer增长的大小有一定的关系，我简单的查看了下：
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('SSS','TREDO');
END;
select segment_name, blocks * 8192 tbsize, segment_type
from dba_segments
where segment_name = 'TREDO';
result：9437184
这样对比应该得不到想要的结果，再进行一次对表数据DML的操作：
操作前系统级别的状态
| redo blocks read for recovery | 0 |
| redo k-bytes read for recovery | 0 |
| redo k-bytes read for terminal recovery | 0 |
| redo entries | 2501334 |
| redo size | 2946962456 |
| redo entries for lost write detection | 0 |
| redo size for lost write detection | 0 |
| redo size for direct writes | 2324406656 |
| redo buffer allocation retries | 304 |
| redo wastage | 149663340 |
| redo writes | 388570 |
| redo blocks written | 6243140 |
| redo write time | 26024 |
| redo blocks checksummed by FG (exclusive) | 4647057 |
| redo blocks checksummed by LGWR | 0 |
| redo log space requests | 14 |
| redo log space wait time | 12 |
| redo ordering marks | 396 |
| redo subscn max counts | 27319 |
| redo write broadcast ack time | 0 |
| redo write broadcast ack count | 0 |
| redo write broadcast lgwr post count | 0 |
| redo synch time | 440 |
| redo synch time (usec) | 4685487 |
| redo synch writes | 1283 |
| redo synch long waits | 18 |
| redo synch poll writes | 0 |
| redo synch polls | 0 |
| redo KB read | 3100690 |
| redo KB read (memory) | 0 |
| redo KB read for transport | 0 |
| redo KB read (memory) for transport | 0 |
会话级别的状态
| redo blocks read for recovery | 0 |
| redo k-bytes read for recovery | 0 |
| redo k-bytes read for terminal recovery | 0 |
| redo entries | 68472 |
| redo size | 47199448 |
| redo entries for lost write detection | 0 |
| redo size for lost write detection | 0 |
| redo size for direct writes | 25622036 |
| redo buffer allocation retries | 0 |
| redo wastage | 0 |
| redo writes | 0 |
| redo blocks written | 0 |
| redo write time | 0 |
| redo blocks checksummed by FG (exclusive) | 0 |
| redo blocks checksummed by LGWR | 0 |
| redo log space requests | 0 |
| redo log space wait time | 0 |
| redo ordering marks | 0 |
| redo subscn max counts | 72 |
| redo write broadcast ack time | 0 |
| redo write broadcast ack count | 0 |
| redo write broadcast lgwr post count | 0 |
| redo synch time | 3 |
| redo synch time (usec) | 31387 |
| redo synch writes | 10 |
| redo synch long waits | 0 |
| redo synch poll writes | 0 |
| redo synch polls | 0 |
| redo KB read | 0 |
| redo KB read (memory) | 0 |
| redo KB read for transport | 0 |
| redo KB read (memory) for transport | 0 |
进行删除操作
DELETE FROM TREDO WHERE rownum<50000;
系统级别的状态值发生改变：
| redo blocks read for recovery | 0 |
| redo k-bytes read for recovery | 0 |
| redo k-bytes read for terminal recovery | 0 |
| redo entries | 2553644 |
| redo size | 2965874752 |
| redo entries for lost write detection | 0 |
| redo size for lost write detection | 0 |
| redo size for direct writes | 2324406656 |
| redo buffer allocation retries | 304 |
| redo wastage | 149695144 |
| redo writes | 388660 |
| redo blocks written | 6281384 |
| redo write time | 26060 |
| redo blocks checksummed by FG (exclusive) | 4647066 |
| redo blocks checksummed by LGWR | 0 |
| redo log space requests | 14 |
| redo log space wait time | 12 |
| redo ordering marks | 397 |
| redo subscn max counts | 27323 |
| redo write broadcast ack time | 0 |
| redo write broadcast ack count | 0 |
| redo write broadcast lgwr post count | 0 |
| redo synch time | 440 |
| redo synch time (usec) | 4685487 |
| redo synch writes | 1283 |
| redo synch long waits | 18 |
| redo synch poll writes | 0 |
| redo synch polls | 0 |
| redo KB read | 3100690 |
| redo KB read (memory) | 0 |
| redo KB read for transport | 0 |
| redo KB read (memory) for transport | 0 |
会话级别的状态值发生改变：
| redo blocks read for recovery | 0 |
| redo k-bytes read for recovery | 0 |
| redo k-bytes read for terminal recovery | 0 |
| redo entries | 120623 |
| redo size | 66079516 |
| redo entries for lost write detection | 0 |
| redo size for lost write detection | 0 |
| redo size for direct writes | 25622036 |
| redo buffer allocation retries | 0 |
| redo wastage | 0 |
| redo writes | 0 |
| redo blocks written | 0 |
| redo write time | 0 |
| redo blocks checksummed by FG (exclusive) | 0 |
| redo blocks checksummed by LGWR | 0 |
| redo log space requests | 0 |
| redo log space wait time | 0 |
| redo ordering marks | 1 |
| redo subscn max counts | 76 |
| redo write broadcast ack time | 0 |
| redo write broadcast ack count | 0 |
| redo write broadcast lgwr post count | 0 |
| redo synch time | 3 |
| redo synch time (usec) | 31387 |
| redo synch writes | 10 |
| redo synch long waits | 0 |
| redo synch poll writes | 0 |
| redo synch polls | 0 |
| redo KB read | 0 |
| redo KB read (memory) | 0 |
| redo KB read for transport | 0 |
| redo KB read (memory) for transport | 0 |
redo size：
sys:
2965874752-2946962456=18912296
session：
66079516-47199448=18880068
66079516-47199448=18880068
redo wastage:
149695144-149663340=31804
redo block written：
6281384-6243140=38244
38244*(512-16)=18969024
对表进行迁移操作，并重新计算表大小，发生改变为：
result：2818048
9437184-2818048=6619136
应该说，通过这种简单的数字计算是无法判断出redo变化与数据变化的关系的。
这次主要的目的是为了测试redo wastage的变化的，这次测试一方面得出redo wastage，redo size，redo blocks written三者的关系，同时，也得出另一个公式：
redo wastage(increment)/(redo wastage(increment)+redo size(increment))
通过该公式的计算能判断出redo浪费率是多少
(31804/(31804+18912296))*100=0.168%
通过该计算能判断redo block的设置是否合理。
关于redo block的设置在初始化参数中有写，这里就不详细介绍
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26664667/viewspace-1228588/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
没有了~
下一篇：
ORACLE初始化参数文件介绍
请登录后发表评论
登录
全部评论
