| |
 |
|
Oracle Tips by Burleson |
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 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;
See code depot
This is an excerpt by Mike Ault’s book “Oracle9i
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
