Thursday, November 29, 2007

How to optimize UNDO tablespace ?

--contributed by Manjunath Ulthi,

It becomes a challenge to figure out the right amount of UNDO to be allocated to a database. Try this method :

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

(UR) UNDO_RETENTION in seconds - an init parameter
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

Two (UPS) can be obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE.
The third piece of the formula (DBS) requires a query against the database. The number of undo blocks generated per second can be acquired from V$UNDOSTAT.

The following formula calculates the total number of blocks generated and divides
it by the amount of time monitored, in seconds:

SQL>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400)
FROM v$undostat;

The following query calculates the number of bytes needed:

SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));

Simple, ain't it ? Please provide your inputs & actual case studies through the comments link ! We love feedback.

No comments:

Post a Comment