Trying to understand the Undo size behaviour . I ran below query initially and noted down the values . Actual undo is 2500 , undo retention 900 and needed Undo size was 430 . Then I performed stress testing on the system(db) by executing ' insert into t1 select * from t1' . Ran this insert parallely in 3 sessions and it took 16 mins per insert to complete .
During the insert run time I refreshed the Undo query and observed needed Undo size was growing from 430 to 899 .
But after the dml completion . Needed Undo was still 899 . I kind of expecting it to get cleared after the operation and go back to original value i.e 430 . Please help me understand this behaviour and how to setback the Undo to its initial value
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'
/
You can not monitor UNDO tbs like that. UNDO tbs serves Oracle's MVCC. Every SQL sees data as they were at the moment when SQL was executed. If record was modified while SQL was running, previous version is read from UNDO.
There is no process to cleanup UNDO, there is no need to clean it up. Some other session will overwrite UNDO when it needs to store some other UNDO.
The only problem is when when a session overwrites UNDO when can be potentially used by other sessions. It is called "stealing" and there is a column in v$undostat for this purpose. When this happens other SQL can fail with "ORA-01555: Snapshot too old" - this means old version of data was not found in UNDO.
PS: undo_retention parameter is only used by auto-extensible UNDO tbs. It drives extension of UNDO tbs datafile. When you have fixed size UNDO(and you should have), this parameter is ignored.