Since oracle 9i, rollback segments
are re-named 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.
OPTIMAL UNDO RETENTION = ACTUAL UNDO SIZE /(DB_BLOCK_SIZE * UNDO_BLOCK_PER_SEC)
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;
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.
OPTIMAL UNDO RETENTION = ACTUAL UNDO SIZE /(DB_BLOCK_SIZE * UNDO_BLOCK_PER_SEC)
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;
Comments
Post a Comment