Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

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.

Note: Make sure that this transaction is the only one running when the test is done, or the results will be invalid.

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.


 



 

The Oracle script collection contains more than 600 working Oracle scripts.  You can download them immediately at this link:
 


 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks