Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

Monitoring Rollback Transaction Size

Monitoring Rollback Transaction Size

Run some sample transactions through the script below to determine whether the rollback segments are properly sized. Simply place the SQL from the transaction or the call to the transaction into the script where indicated, and execute the script.

undo.sql

SET FEEDBACK OFF  TERMOUT OFF
COLUMN name FORMAT a40
DEFINE undo_overhead=54
 
DROP TABLE undo_data;
CREATE TABLE undo_data
     (
     tran_no number, start_writes number, end_writes number
     );
INSERT INTO undo_data
SELECT 1, SUM(writes),0 from v$rollstat;
SET FEEDBACK ON  TERMOUT ON
rem
rem   INSERT TRANSACTION HERE
rem
SET FEEDBACK OFF  TERMOUT OFF
UPDATE undo_data SET end_writes = SUM(writes) FROM v$rollstat;    
 SEE CODE DEPOT FOR FULL SCRIPT
SET FEEDBACK ON  TERMOUT ON

SELECT  ((end-writes - start_writes) - &undo_overhead)
"Number of Rollback Bytes Generated"
FROM undo_data;
SET TERMOUT OFF FEEDBACK OFF
DROP TABLE undo_data;

If the DBA is concerned with the rollback usage in an individual transaction, the script above can be run with that transaction in the indicated spot in the script. The generated data will tell the DBA the exact amount of rollback usage for the transaction. This data can then be used to create a custom rollback segment that can be brought online and used during that transaction. Again, the script and test run of the transaction must be the only active transaction in the database when the test is run.

Monitoring Deferred Rollback Segments

If a rollback segment is taken offline, its transactions may be placed in a temporary segment in the rollback segment’s tablespace. These temporary segments are referred to as deferred rollback segments. The following SQL code will list any deferred rollbacks in a 7.x, 8.x or 9.x database:

deferred_rbs.sql

--**********************************************
--
--   Copyright © 2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.
--   For commercial licensing, e-mail rtp AT rampant.cc
--
 

-- *********************************************
 
SELECT
   segment_name,
   segment_type,
   tablespace_name
FROM
   sys.dba_segments
SEE CODE DEPOT FOR FULL SCRIPT

Example output from the preceding select statement:

SEGMENT_NAME   SEGMENT_TYPE      TABLESPACE_NAME
------------   ----------------- ---------------
RBK1           DEFERRED ROLLBACK USERS

Under Oracle7, if a rollback segment is taken offline, its status will be changed to PENDING OFFLINE, and it will be taken offline as soon as it’s pending transactions are complete. The preceding SELECT statement could be used to determine if any of these active transactions are in a deferred state. To determine if a rollback segment under Oracle7 has outstanding transactions, the following SELECT statement is used.

SELECT
   name,
   xacts 'ACTIVE TRANSACTIONS'
FROM    
   v$rollname,
   v$rollstat
SEE CODE DEPOT FOR FULL SCRIPT

AND
   .usn;

Be certain the database has a sufficient number of online rollback segments. If the ratio TRANSACTIONS/ TRANSACTIONS_PER_ROLLBACK is exceeded, the system automatically brings online any available public rollback segments. If the only available public rollback happens to be the maintenance segment in the system space, it will be brought online and could cause havoc in the system tablespace, as it extends to accommodate transactions.

Monitoring Redo Log Activity

The redo logs provide the information required to redo transactions on the database. For an Oracle8, Oracle8i, or Oracle9i database, redo logs are placed in log groups whose members consist of individual log files. For Oracle8, Oracle8i, or Oracle9i, there should be at least two mirrored groups of log files on separate drives to start up; three are highly recommended.

If activity is expected to be high, the use of five mirrored groups of 5 megabytes each will ensure that there is no log contention. Determining the number and size of redo logs is not an exact science; it must be done by trial and error. The alert log can be monitored for waits on log switches or checkpoints to help determine this.