Oracle Advanced Performance Tuning Scripts

Redo Log Scripts

archival_headroom.sql

This script compares the time taken for the archival of each log file (taken from V$ARCHIVED_LOG) with the time before the log switch that reused that file and reports the percentage headroom that the tightest archival operation has had. If the headroom has been less than 50%, then you should either tune archival performance, or you should consider increasing the number of log files, or both.

View sample listing

Download script for: 8.0, 8.1 or 9.0

checkpoint_headroom.sql

Sorry, this new script has had to be withdrawn. I was wrong about the meaning of the X$TARGETRBA.LOGFILESZ column on which it was based. Thanks to the Oracle insider who corrected me.

commit_every.sql

Unnecessarily frequent commits are a major cause of poor performance. Each commit makes the process wait for at least one I/O operation, and places extra load on the LGWR process and the redo allocation latch. Active OLTP systems that commit more than 50 times per second and batch processes that commit repeatedly suffer the most noticeable performance degradation.

It is common practice to code batch processes to only commit every n rows to reduce the total time spent waiting for commits. This script implements the better alternative of committing periodically based on the elapsed time instead. For example, the packaged procedure COMMIT_EVERY.MINUTE can be called in place of the COMMIT statement in batch procedures that commit repeatedly. However, if there is a risk of interactive users being blocked waiting for a row level lock held by the batch process, then COMMIT_EVERY.SECOND or COMMIT_EVERY.FEW_SECONDS(2) might be more appropriate.

Download script for: 7.3, 8.0 or 8.1

lgwr_stats.sql

The statistics reported by this script indicate whether the log buffer is well sized. Please note that the sync cost ratio is not meaningful if LGWR is using asynchronous I/O (because in that case the log file parallel write time is much less than the physical write delay).

Download script for: 7.3, 8.0 or 8.1

log_block_size.sql

This script reports the operating system specific log block size.

Download script for: 7.3, 8.0 or 8.1

dbwn_sync_waits.sql

This script reports the number of times that DBWn had to wait for LGWR to sync the log buffer before it could begin to write a batch of blocks.

Download script for: 7.3 or 8.0

hold_logs_open.sh

This script is designed to be run daily from cron under Unix to hold the online log files open. This improves the performance on log switches. Please see our tip Tuning Log Switches for more details.

On some platforms the shell only supports input redirection for single digit file descriptors. If so, and if you have more than 7 log file members, then there are some lines in the script that can be uncommented to use a set of processes to hold open a set of 7 log file members each.

Download script for: all versions

log_switch_interval.sql

This script shows the shortest interval between two log switches in the living memory of the instance.

Download script for: 8.0 or 8.1

log_file_usage.sql

If you are planning to dump the current log file and are fearful of getting an enormous trace file, or if you are just curious to know how much of the current log file has been used, and how close your instance is to its next log switch, then this little script can help.

Download script for: 8.0 or 8.1


Ixora Pty Ltd.   All rights reserved.
12-Oct-2007 22:22
Search   Questions   Feedback   Up   Home