 |
|
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. |
 |
|