RelationalDBDesign RelationalDBDesign

Tuning Instance   «Prev 

Redo Log Performance

Here is a summary of the major points from this module for your review:
  1. Redo log performance is influenced by the log init.ora parameters, the size of the redo log files, and the size of the archived redo log file system.
  2. The log_buffer parameter should be increased (up to one megabyte) whenever you experience high redo log space requests or redo log space wait time. Online redo log files should be sized to perform a log switch no more than twice per hour.
  3. A high value of redo buffer allocation retries indicates that you may want to increase the size of the online redo log files. The archived redo log file system must be sized such that it can hold enough redo files to roll-forward. It should also be sized such that it never becomes full.
  4. The log_small_entry_max_size parameter should be set small enough that it is smaller than the redo entries. The default value is 80 bytes. If you have high redo copy latches, increase the log_simultaneous_copies parameter in your init.ora file. This parameter is obsolete in Oracle 8.1.5 (Oracle8I).
  5. The log_checkpoint_interval init.ora parameter should be set so you only encounter a checkpoint at each log switch.

Advanced Oracle SQL Tuning

Managing Temporary Undo

Temporary undo records are stored in the database's temporary tablespaces and thus are not logged in the redo log. When temporary undo is enabled, some of the segments used by the temporary tablespaces store the temporary undo, and these segments are called temporary undo segments. When temporary undo is enabled, it might be necessary to increase the size of the temporary tablespaces to account for the undo records. Enabling temporary undo provides the following benefits:
  1. Temporary undo reduces the amount of undo stored in the undo tablespaces. Less undo in the undo tablespaces can result in more realistic undo retention period requirements for undo records.
  2. Temporary undo reduces the size of the redo log. Performance is improved because less data is written to the redo log, and components that parse redo log records, such as LogMiner, perform better because there is less redo data to parse.
  3. Temporary undo enables data manipulation language (DML) operations on temporary tables in a physical standby database with the Oracle Active Data Guard option. However, data definition language (DDL) operations that create temporary tables must be issued on the primary database.
You can enable temporary undo for a specific session or for the whole system. When you enable temporary undo for a session using an ALTER SESSION statement, the session creates temporary undo without affecting other sessions. When you enable temporary undo for the system using an ALTER SYSTEM statement, all existing sessions and new sessions create temporary undo.