This content has been marked as final. Show 12 replies
I went with several documents including asktom's articles & http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/undo004.htm#BABJCFFE
SQL> select max(MAXQUERYLEN) from v$undostat;
undo retention is 4000 so if we increase undo_retention more than longest running query can we avoid ORA-01555 SNAPSHOT too old errors.
undo tablespace is autoextend on, and the undo tablespace is around 35GB, even not using atleast 60% of the UNDO, so is it better to extend undo_retention > 5100 ?
Can anyone give suggestion that if we increase undo retention > 5100 , can overcome this problem?
Thanks in advance.
Edited by: CKPT on Nov 11, 2010 1:24 AM
Thanks for your reply.
1. to set UNDO_RETENTION parameter to value greater than your longest running queryIts Ok , i will do
2. to have a fixed size undo tablespaceNo, do you mean autoextensible is off? no undo tablespace is autoextensible
3. to monitor ORA-01555 with instance alert log and V$UNDOSTAT.SSOLDERRCNTSQL> select MAXQUERYLEN,SSOLDERRCNT from v$undostat where MAXQUERYLEN>5000;
4. if possible try to tune the longest running queriesIt is materialized view.. i will do
Seeking more help Thanks :)
Edited by: CKPT on Nov 11, 2010 1:51 AM
I believe your maxquerylen value is increasing in a pase; as initially you had posted it as 1400 and now it has crossed 5000.
I think its better for you to set undo_retention to a value of 7000+ and monitor continously.
Btw: Make sure UNDO tbs has enough free space as well.
If maxquerylen value is increasing; you have to keep on increasing the undo_retention and decide accordingly.
By the way; it would be always better if you could fine tune the problematic queries.
I am not sure - but I dont think it would be a better option to turn OFF the autoextend mode of UNDO tbs as you are facing the 1555.