Snapshot too old in 9i

[soestx]

I have a database running on 9i with automated UNDO management but am still getting the "SNAPSHOT TOO OLD" error when running long queries and even with exports. I have changed the UNDO_RETENTION paramater to various levels, reducing it and also increasing it. I have also quadrupled the UNDO tablespace datafiles over the past week and am still getting the error. I have queried the v$undostat view filtering for the largest SSOLDERRCNT and get the following....


BEGIN_TIM 27-Jun-03
END_TIME 27-Jun-03
UNDOTSN 1
UNDOBLKS 971
TXNCOUNT 7373
MAXQUERYLEN 3127
MAXCONCURRENCY 4
UNXPSTEALCNT 0
UNXPBLKRELCNT 0
UNXPBLKREUCNT 0
EXPSTEALCNT 0
EXPBLKRELCNT 0
EXPBLKREUCNT 0
SSOLDERRCNT 74
NOSPACEERRCNT 0

Anybody have any tips on troubleshooting?

Thanks,
Steven

[adrianomp]

Look for short DML, try to reduce their commit frequency and, if possible, run your query on another time to avoid concurrency with these DML - or the opposite. Depending of the query (we don't know how it looks like), it may be rewritten to avoid fetch across commits.

Try to read this too:

http://asktom.oracle.com/pls/ask/f?p...:275215756923,