--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)
where,
(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'));
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)
where,
(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.
Comments
Post a Comment
Please add your comment here