Managing UNDO Tablespace - Optimal UNDO SIZE And UNDO

Managing UNDO Tablespace by properly setting the size of UNDO tablespace and UNDO retention is a very important DBA task. To avoid the head ache of doing this manually, AUTOMATIC UNDO MANAGEMENT can be turned on, so that Oracle itself would manage this task. To do this, initialization parameter UNDO_MANAGEMENT should be set to AUTO.

sql> alter system set undo_management=auto scope=spfile;

Note that this is a static parameter which requires a database restart for the parameter to take effect.

Apart from this, we need to tune the following parameters too.
1. UNDO TABLESPACE size
2. UNDO_RETENTION parameter.

1. To Calculate the UNDO TABLESPACE size.

Undo Tablespace Size = Undo Retention * DB Block Size * Undo Blocks Generated per second.

To find the Current Undo Size

SQL> SELECT SUM(d.bytes/1024/1024) “UNDO_SIZE_MB”
FROM v$datafile d,
v$tablespace t,
dba_tablespaces dt
WHERE dt.contents = ‘UNDO’
AND dt.status = ‘ONLINE’
AND t.name = dt.tablespace_name
AND d.ts# = t.ts# ;

UNDO_SIZE_MB
————
2246

To find the Undo Blocks generated per second

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
“UNDO_BLOCK_PER_SEC”
FROM v$undostat;

UNDO_BLOCK_PER_SEC
——————
4.27333333

To find DB block size

SQL> SELECT TO_NUMBER(value) “DB_BLOCK_SIZE [KByte]”
FROM v$parameter
WHERE name = ‘db_block_size’;

DB_BLOCK_SIZE [KByte]
———————
8192

Query to find actual Undo Size Needed as per the formula

SQL>SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MB]”,
SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]”,
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
“NEEDED UNDO SIZE [MB]”
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = ‘UNDO’
AND c.status = ‘ONLINE’
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = ‘undo_retention’
AND f.name = ‘db_block_size’
/

ACTUAL UNDO SIZE [MB] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MB]
———————— ———————- ———————-
2246 54000 1802.8125
2. To find the optimal value of UNDO_RETENTION

To calculate the optimal value of UNDO_RETENTION for the given UNDO tablespace size.

SQL>SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]”,
SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]”,
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) “OPTIMAL UNDO RETENTION [Sec]”
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = ‘UNDO’
AND c.status = ‘ONLINE’
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = ‘undo_retention’
AND f.name = ‘db_block_size’
/
ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
———————— ———————– —————————-
2246 54000 67275

According to the result, change the dynamic initialization parameter UNDO_RETENTION( in seconds).

SQL> alter system set undo_retention =67275 scope=both;
Research and Article Contribution by : Divya