Managing UNDO Tablespace – Optimal UNDO SIZE And UNDO_RETENTION
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