Someone recently asked me what’s the ratio of redo generation to datablock writes, and of course I answered it depends, but like so many things it’s nice to have rules of thumb like random 8k block reads off spindle are around 6ms and sequential redo writes around 3ms  – just points of references or yard sticks, so I decided to browse through AWR stats. I looked at 1000 AWR snapshots. Each snapshot  data gave the averages for a 1 hour period. The snapshots came from 7 databases. I then plotted the distribution:

The ratio is on the left y-axis and % on the bottom x-axis. The majority of times block write bytes is 2-4x more than redo generated bytes.

Less than 2% of ratios are below 1 or above 5.

Initially I plotted each database’s ratio as a lines. It was quite a mess:

 

Here is the query I used

 

SET markup HTML on
spool hist.html
select   write/redo from
( select
dbid,  to_char( begin_time +1/48,'YYYY/MM/DD HH24'),
sum(decode(metric_name,'Redo Generated Per Sec',average,0))  redo,
sum(decode(metric_name, 'Physical Write Bytes Per Sec' ,average,0))  write
from     dba_hist_sysmetric_summary
where    metric_name in
( 'Redo Generated Per Sec',
'Physical Write Bytes Per Sec'
)
group by  dbid,  to_char( begin_time +1/48,'YYYY/MM/DD HH24')
)
order by  1
;
spool off
SET markup HTML off

I don’t see any clear documentation on “Physical Write Bytes Per Sec” so I wonder exactly what I/O is included in this statistics. Clearly this statistics doesn’t include redo because it’s value can be less than redo writes.  The statistic “physical write IO requests” is documented as buffer cache block writes and direct block writes thus I’m betting these are the writes covered by “Physical Write Bytes Per Sec” .

It would be interesting to break down this data into different data operations such as many changes on one block, single change across many blocks, hot back up mode, etc

 

Kyle Hailey
Uncategorized