Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

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.

     



 

The Oracle script collection contains more than 600 working Oracle scripts.  You can download them immediately at this link:
 


 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks