Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

ss Oracle Tips by Burleson

Chapter 6 Oracle Tablespace and File Internals Scripts

Statistics from the caches that affect redo operations are shown in the second half of the report. Let’s look at what these numbers tell us. The most important of the listed statistics are redo blocks written, redo entries linearized, redo small copies, and redo writes.

• redo blocks written is useful when two entries are compared for a specified time period. This will indicate how much redo is generated for the period between the two checks.
   

• redo entries linearized
   

• redo small copies tells how many times the entry was written on a redo allocation latch. This indicates that a redo copy latch was not required for this entry. This statistic should be compared with the redo entries parameter. If there is approximately a one-to-one relationship, then the system is making effective use of the redo allocation latch. If there is a large difference, then the log_small_entry_max_size init.ora parameter should be increased. If the log_simultaneous_copies parameter is 0, this value is ignored.
   

• redo writes is the total number of redo writes to the redo buffer. If this value is too large compared to the redo entries parameter value, then the DBA should tune the init.ora parameters mentioned in the previous sections to force prebuilding of the entries. If the entries are not prebuilt, it may require several writes to the buffer before it is fully entered. If it is prebuilt, it requires only one.
   

• redo log space wait tells whether users are having to wait for space in the redo buffer. If this value is nonzero, increase the size of the log_buffer in the initialization file.

• • redo buffer allocation retries is the number of repeated attempts needed to allocate space in the redo buffer. If this value is high in comparison to redo entries, it indicates that the redo logs may be too small and should be increased in size. Normally, this value should be much less than the redo entries statistic. In the example, it has a value of 5 compared to the entry’s value of 1044; this is satisfactory.
     

  • redo size is the total number of redo bytes generated since the database was started. Comparison of two readings will give the amount generated over time. This value can then be used to determine if the log switch interval is set properly. Too many log switches over a small amount of time can impair performance.

  Use the following formula to look at log switches over time:

  (X / (dN / dt)) / interval of concern

  Where:

  • X is the value of the log_checkpoint_interval or the size of the redo log in system blocks.
     

  • dN is the change in the redo size over the time interval.
     

  • dt is the time differential for the period (usually minutes).

  Once the number of log switches is known, the number determines the size of redo logs based on system I/O requirements. If the number of log switches needs to be reduced, increase the redo log size. Of course, this may impact system availability, since it takes longer to write out a large redo log buffer than a small one to disk. A balance must be struck between redo logs that are too small, affecting database performance, and logs that are too large, affecting I/O performance.