Oracle Advanced Performance Tuning Scripts

Rollback Segment Scripts

rolling_back.sql

If the number of rollback segment blocks in use by a transaction (as seen in V$TRANSACTION.USED_UBLK) is decreasing, then that transaction is rolling back. For large transactions, it may be helpful to be able to estimate when the transaction will finish rolling back. This can be done by monitoring the rate at which the number of used rollback segment blocks is decreasing, and then extrapolating based on the assumption of a constant rate of roll back.

Unfortunately, this does not work for rollback as part of transaction recovery after an instance has crashed or been aborted. However, from release 8.1, the number of undo blocks in use by each transaction is maintained in the transaction tables of the rollback segment header blocks. So the information needed to calculate a rate of rollback can be obtained by looking directly at the rollback segment header blocks. The rollback segment header block transaction tables can be read via X$KTUXE, however the number of undo blocks in use is not always accurately reflected here until release 9i. Prior to that, it is necessary to dump the rollback segment header blocks to a trace file in order to get the number of undo blocks still in use by a transaction that is being rolled back as part of transaction recovery. The procedure to do this is explained in MetaLink note 169138.1

This script monitors V$TRANSACTION (or X$KTUXE from 9.0) for up to 10 seconds to look for any transactions that are rolling back, and to estimate when their roll back operations will complete.

Download script for: 7.3, 8.0, 8.1 or 9.0

rollback_reuse_time.sql

A query or transaction is at risk of an ORA-1555 (snapshot too old) error, if it runs long enough for an extent to be reused in any online rollback segment. This script looks at how many times rollback segment extents have been reused since instance startup, and reports the average time taken before an extent is reused. This may serve as an indication of how long a query or transaction may run before being at risk of this error.

However, this indication may not be very reliable:

Download script for: 7.3, 8.0 or 8.1

shrink_rollback_segs.sql

This script shrinks all online rollback segments back to their optimal size, if possible. This should not be done lightly, because it increases the risk of ORA-1555 (snapshot too old) errors in queries and transactions that have already started. However, it reduces the risk of this error affecting transactions and queries that have not yet started. It also reduces the risk of running out of space in the rollback segment tablespaces.

Download script for: 7.3, 8.0 or 8.1

sub_optimal.sql

If the OPTIMAL setting on a rollback segment is too low, Oracle has to dynamically expand and then shrink the rollback segment repeatedly. This script suggests raising the OPTIMAL setting if rollback segments of that size have been shrinking more than once per reuse cycle, and have shrunk at least twice. The suggested OPTIMAL setting is the present OPTIMAL value plus an average shrink, rounded to a whole number of extents, minus one block for the segment header. This is based on the assumption that all the extents are equally sized.

Download script for: 7.3, 8.0 or 8.1

enough_rollback_segs.sql

This script reports the number of rollback segments, the peak number of concurrent transactions, and the number of buffer busy waits for the rollback segment header blocks. Waits to read and write the blocks are distinguished, but the numbers may be inaccurate if rollback segments have been taken offline. Write waits are a stronger indication of insufficient rollback segments than are read waits.

Download script for: 7.3, 8.0 or 8.1

rollback_segment_life.sql

A transaction ID consists of its rollback segment number, the slot number in the transaction table of that rollback segment and a wrap number which distinguishes consecutive uses of that slot. The wrap number is 4 bytes long, and when it gets to 0xFFFE, that rollback segment cannot take any more transactions, because it cannot give them a new transaction ID. At that point an ORA-1558 error will be returned, saying that the rollback segment has to be dropped and recreated. This script shows what percentage of the useful life of each online rollback segment has been used up.

Download script for: 7.3, 8.0 or 8.1


The following set of scripts can be used to protect business critical long running reports or programs from the ORA-1555 (snapshot too old) error. Please see our tip Avoiding ORA-1555 Errors for more detail.

prevent_1555_setup.sql

This script creates a table in the SYSTEM schema that is used to record and implement the protection of rollback segments.

Download script for: 7.3, 8.0 or 8.1

prevent_1555.sql

This is the main script of the set. It is called to ensure protection from ORA-1555 errors for a specified number of seconds. This script calls protect_rbs.sql in the background for each online rollback segment.

This script generates a shell script, and thus is Unix specific.

Download script for: 7.3, 8.0 or 8.1

protect_rbs.sql

This script first shrinks the specified rollback segment to reduce the risk of running out of space in the rollback segment tablespaces. It then records its protection in the control table, before leaving an uncommitted transaction sleeping for the required number of seconds.

Download script for: 7.3, 8.0 or 8.1

prevent_1555_wait.sql

This script must be run after the dummy transactions have been created in each online rollback segment. It waits for all older active transactions to finish. This is necessary in environments with other long-running transactions that may not yet have completed, because the earlier undo for those transactions is not protected and might be required by the critical report unless those transactions are allowed to finish before the critical report starts.

Download script for: 7.3, 8.0 or 8.1

protected_rollback_segs.sql

This script is used to report the protection status of the rollback segments.

Download script for: 7.3, 8.0 or 8.1


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