Speed Oracle by Increasing the Log File Size

If you want to speed up large numbers of INSERTs, UPDATEs, and DELETEs, increase the sizes of your log files and make sure they are on the fastest disk.

Previously, you could also increase the LOG_CHECKPOINT_INTERVAL if it was set such that it would checkpoint prior to a log switch, and this currently defaults to zero (which means switching based on the redo log being full). The LOG_CHECKPOINT_INTERVAL determines the length of time between checkpoints. Therefore, any recovery that involves applying the online redo logs is affected—meaning complete database recovery or instance recovery. Increasing the size of your log files can increase the time needed for media recovery.

Oracle relies on online redo log files to record transactions. Each time a transaction takes place in the database, an entry is added to the online redo log file. If you increase the size allocated for the redo log files, you can increase performance by decreasing the overall number of required log switches and checkpoints. Uncommitted transactions generate redo entries, too, because they generate undo records and these undo records are also written to the redo logs. You can watch the logs spin during a large batch transaction. But keep the following characteristics in mind when you make modifications to the size of your log files:

• A log file must be online and available while the database is up or the database will halt (one of the few things that stops the database immediately).
• Online redo log files are recycled and offline redo log files are written to archived log files automatically (if archiving is activated).
• Minimum is two online redo log files. Online redo log file multiplexing (additional copies) is recommended to provide redundancy in case an online redo log file is lost.
• The number of initial log files and their sizes are determined automatically when the database is created.
• Archive logging can be turned on and off by restarting the database in MOUNT mode and then using the ALTER DATABASE command.
• Checkpoints occur when committed transactions in redo logs get written to the database. Checkpoints also update the datafile headers to set the checkpoint SCN, which is used during the rolling back phase of recovery. If the current SCN for the database at the time of failure was 234578, and the datafiles have a checkpoint SCN of 234500, then only the changes in the redo logs from 234500 to 234578 need to be rolled back. Checkpoints are basically consistency markers for the database—a way of saying everything is in sync at this point.