This content has been marked as final. Show 12 replies
i am not sure why you are using so much undo retention. may be you can use undo advisory feature from enterprise manager.1 person found this helpful
i have been using following script to optimize our undo size and undo retention.
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]"
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
hope it will help you.
i executed this query
it showed needed undo 3GB
my present undo is arnd 15 GB and 13 GB is getting used
out of which 5 GB is expired 7 GB unexpired and 1 GB active
undo retention is 7200 sec
why am i unable to retain undo space after 2 hrs?
Post the result of this query:
and the DDL you are using to change undo retention.
select tablespace_name, retention from dba_tablespaces order by 1;
Also give serious consideration, given how old your database is, to patching to at least 126.96.36.199 or upgrading to a currently supported version.
APPS_TS_ARCHIVE NOT APPLY
APPS_TS_INTERFACE NOT APPLY
APPS_TS_MEDIA NOT APPLY
APPS_TS_NOLOGGING NOT APPLY
APPS_TS_QUEUES NOT APPLY
APPS_TS_SEED NOT APPLY
APPS_TS_SUMMARY NOT APPLY
APPS_TS_TX_DATA NOT APPLY
APPS_TS_TX_IDX NOT APPLY
CTXD NOT APPLY
CUST_KPM NOT APPLY
CUST_KPM2 NOT APPLY
DISCOTBS NOT APPLY
KPMTEAM NOT APPLY
ODM NOT APPLY
OLAP NOT APPLY
OWAPUB NOT APPLY
PERFSTAT NOT APPLY
PORTAL NOT APPLY
SYSAUX NOT APPLY
SYSTEM NOT APPLY
TEMP NOT APPLY
database is upgraded to 10.2.0.4
can there be any kind of bug for the database
as there is a huge difference between
maxquerylen (12000) and tuned_undoretention (345000)
undoretention is 7200
alter system set undo_retention=7200 scope=both;
With 10g the undo_retention parameter auto tunes - so the parameter you set is not really relevant. So if you have autoextend on the UNDO tablespace then it will grow the datafiles.1 person found this helpful
you can go through following link which gives the complete detail how undo works1 person found this helpful
Do you have autoextend datafiles or not. If you do not have autoextend datafiles then the undo_retention value will auto-tune to fill the whole tablespace....
i have added an autoextensible datafile with maxsize , lets see what happens
I think it should not extend the tablespace - that is grow the datafile - because your max query length is much smaller that the tuned undo retention - but it will still attempt to fill the whole of the allocated space.
It is not recommended to keep undo tablespace in autoextend mode. you can estimate depend on type of transaction and set the value.
Edited by: Anil Malkai on Mar 9, 2010 6:19 AM
i knw, that is why i have added maxsize to it, i guess it wont grow beyond maxsize
oops sorry.. when you said maxsize for tablespace .. i thought maximum size of any tablespace (apart from big tablespace) is 32gb. :)