Ratio of Redo bytes to Datablocks writes

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

 


Uncategorized

  1. Trackbacks

  1. Comments

  2. Martin Widlake
    July 10th, 2011 at 18:19 | #1

    Hi Kyle,

    I think rules of thumb for insert, update and delete activity, and overhead per index would be nice. That way a rough estimate can be made depending on the type of activity your system will have. I tend to use just a blanket of 2.5X, which judging by your rather nice graph is a little optimistic on my part.

    Having made the suggestion I now feel I should go and do some tests to find out!

    MArtin

  3. July 11th, 2011 at 22:01 | #2

    Hi Martin,

    Thanks for stopping by. Glad this post was of some interest to someone. I was debating whether it was too frivolous to post or not.

    My big question, for my current work, is the size of redo generated verses level 1 RMAN backups. Unfortunately I don’t have the same trove of data to peruse.

    - Kyle

  4. Martin Widlake
    July 13th, 2011 at 09:07 | #3

    @Kyle Hailey
    Not trivial at all – I’m sure many of us have had to size for archived redo logs in the past and have had to guess quite wildly or derive from a similar system. Or wait until the system in question is in use!

    The question of how level 1 RMAN backups compare to the archived redo stream is an interesting one. I am going to guess it will come down to how much new data is generated as opposed to deleting and updating. If it is a question of which is the best backup method, I’d also factor in if the total volume is large. I’ve had issues in the past getting back each and every one of the archived redo logs if there are large quantities of them. The more stuff that went to tape in the greater number of files, the more likely ops will lose some or a read error will snag you :-)


nine − = 2