From oracle 9i, rollback segments are re-named as undo logs, traditionally it were stored in rollback segments until a commit or rollback segments were issued.
Automatic undo management allows the DBA how long information should be retained after commit. The larger your undo tablespace the more you can hold for long running DML operation (Preventing snapshot to old error on long running queries).
You can choose to allocate a specific size for the UNDO tablespace and then set the optimal UNDO_RETENTION according to UNDO size. This is specially useful when you disk space is limited and you do not want to allocate more space than required UNDO size.
The formula to calculate the Optimal undo retention is,
OPTIMAL UNDO RETENTION = ACTUAL UNDO SIZE /(DB_BLOCK_SIZE * UNDO_BLOCK_PER_SEC)
Get the values for the above formula using the below queries and subsitute to get the optimal undo retention value,
Find Actual Undo Size:
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#;
UNDO_SIZE
----------
7948206080
Find Undo Blocks per Second:
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
6.47
Find Undo Block Size:
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';
DB_BLOCK_SIZE [Byte]
--------------------
8192
Therefore,
OPTIMAL UNDO RETENTION = 7948206080 /(8192 * 6.47)=149959.8145 [sec]
Using the below query you can find all those information collectively:
Script to find required Optimal Undo Retention:
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]
---------------------- ---------------- -----------------------------
7580 10800 149960
Script to Calculate Needed Undo size for database:
In Oracle, UNDO size can be controlled with the undo_retention parameter and the size of the UNDO tablespace, thus the setting for these are determined by the level of DML activity in the database:
1. If you are using heavy DML operation make sure there is enough sized rollback segments.
2. If you expect heavy DML load then must have multiple undo tablespace.
3. Try to limit the number of simultaneous users per UNDO to four.
4. For large batch transactions create special large extent rollback segments in a separate tablespace from the other rollback segments,
only bring them online when needed and use SET TRANSACTION command to assign them to specific transactions.
5. Try to avoid running large batch transactions simultaneously with OLTP or smaller transactions.
UNDO_SIZE = UNDO_RETENTION * DB_BLOCK_SIZE * UNDO_BLOCK_PER_SEC
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
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 [MByte]"
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] NEEDED UNDO SIZE [MByte]
----------------------- ---------------- -----------------------
7580 10800 545.90
For any queries you can email me on, samiora@gmail.com
