关于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

redo size:
2894279508-2894156244=123264

redo blocks written:
6130353-6129247=1106

redo wastage:
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

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博客 ” ，链接：http://blog.itpub.net/26664667/viewspace-1228588/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26664667/viewspace-1228588/