Monitoring Rollback Current Usage Oracle Tips by Burleson Consulting

To identify which users are using which rollback segments, run the script in Source 11.25. The report generated shows the Oracle Process ID, the System Process ID, and the rollback segment in use. Listing 11.21 shows an example of output from an active rollback report.

SOURCE 11.25 Example of SQL script to generate active rollback report.

rem   Name     : TX_RBS.SQL
rem   Purpose: Generate a report of active rollbacks
rem   Use     : From SQL*Plus
rem   History:
rem   Date        Who                What
rem   Sept 91     Lan Nguyen      Presented in paper at IOUG
rem               Walter Lindsey
rem   5/15/93     Mike Ault       Added Title80, sets and output
rem   1/04/97     Mike Ault       Verified against 7.3
rem   5/16/99     Mike Ault       Verified against Oracle8i
rem   10/14/01    Mike Ault       Verified against Oracle9i
rem                               reformated added curext, curblk
rem*************************************************************
COLUMN  name FORMAT a10         HEADING "Rollback|Segment"
COLUMN  pid  FORMAT 99999       HEADING "Oracle|PID"
COLUMN  spid FORMAT 99999       HEADING "Sys|PID"
COLUMN  curext FORMAT 999999    HEADING "Current|Extent"
COLUMN  curblk FORMAT 999999    HEADING "Current|Block"
COLUMN transaction FORMAT A15   Heading 'Transaction'
COLUMN program FORMAT a10 HEADING 'Program'
SET PAGES 56  LINES 80 VERIFY OFF feedback OFF
START title80 "Rollback Segments in Use"
SPOOL rep_out\&db\tx_rbs
SELECT
     r.name, l.Sid, p.spid,
     NVL(p.username, 'no transaction') "Transaction",
     p.program "Program",
     s.curext,s.curblk
FROM
     v$lock l,
     v$process p,
     v$rollname r,
     v$rollstat s
WHERE
         l.Sid = p.pid (+)
     AND TRUNC(l.id1(+) / 65536) = r.usn
     AND l.type(+) = 'TX'
     AND l.lmode(+) = 6
     AND r.usn=s.usn
     AND p.username is not null
ORDER BY r.name;
SPOOL OFF
SET PAGES 22  LINES 80 VERIFY ON feedback ON
CLEAR COLUMNS
TTITLE OFF

LISTING 11.21 Example of output from active rollback report.

Date: 10/14/01                                              Page:   1
Time: 03:56 PM            Rollback Segments in Use          Remote DBAUTIL
                              galinux1 databa

Rollback             Sys                                  Current Current
Segment          SID PID       Transaction     Program     Extent   Block
---------- --------- --------- --------------- ---------- ------- -------_SYSSMU6$          7 157       oracle          oracle@tus       0       2
                                               cgalinux (
                                               RECO)

Monitoring Rollback Transaction Size

To determine if your rollback segments are properly sized, you can run some sample transactions through the script in Source 11.26. To do so, 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 your transaction is the only one running when you do the test, or the results will be invalid.

SOURCE 11.26 Script to generate total rollback bytes used in a transaction.

rem**********************************************************
rem  Name     : UNDO.SQL
rem  Purpose: Document rollback usage for a single
rem    transaction
rem  Use  : Note: You must alter the UNDO script and add a
rem    call to the transaction at the indicated line
rem  Restrictions:     : The database should be placed in Remote DBA mode and
rem    this transaction should be the only one running.
rem  History:
rem   Date                 Who        What
rem   Sept 91        Lan Nguyen       Presented in paper at IOUG
rem                  Walter Lindsey
rem   5/15/93        Mike Ault Changed to use one table
rem
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;    
 WHERE tran_no=1;
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 Remote DBA has one transaction whose rollback usage he or she is concerned about, the script in Source 11.25 can be run with the transaction in question executed in the indicated spot in the script. The data generated will tell the Remote 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 transactions 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 your 7.x , 8.x or 9.x database:

     SELECT segment_name, segment_type, tablespace_name
     FROM sys.Remote DBA_segments
     WHERE segment_type = 'DEFERRED ROLLBACK';

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 its 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
     WHERE status = 'PENDING OFFLINE'
     AND         v$rollname.usn = v$rollstat.usn;

TIP: Be sure your 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 Activity with V$ and Remote DBA_ Views

The redo logs provide the information required to redo transactions performed 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. In high-activity environments, the use of five mirrored groups of 5 megabytes each will ensure that there is no log contention. Sizing redo logs and number required is not an exact science; it must be done by trial and error. Monitor the alert log for waits on log switches or checkpoints to see if your logs are large enough or if you have enough of them.

See Code Depot for Full Scripts

---

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.