Snapshot too old on 9i (9.1.2)

[raghud]

We are getting snapshot too old on 9i . We got Undo retention = 3 hours and having undo tablespace 11 GB. Users never used more than 1GB . But we are getting Snapshot too old error. Our system tablespace size is 2gb . We got lot of objects owned by sys(Oracle finanacials 11i). Any solution??

[julian]

Originally posted by raghud
We are getting snapshot too old on 9i . We got Undo retention = 3 hours and having undo tablespace 11 GB. Users never used more than 1GB . But we are getting Snapshot too old error. Our system tablespace size is 2gb . We got lot of objects owned by sys(Oracle finanacials 11i). Any solution??

The error ORA-01555 is reported when the flashback data is not available anymore in the UNDO Tablespace. Just reduce a bit the UNDO_RETENTION. 3h is really a lot.

[grjohnson]

I'd be inclined to think you may be getting the error due to a long running query/report on your DB. Therefore I'd have receommended either a). A larger retention time or b). Run the query/report out of the main business hours.

Julian, could you explain why decreasing the retention period would reduce the occurance because it has me a little confused, as Oracle and my own experience tell me to increase. But maybe I'm the one confused

Cheers,

[stmontgo]

frequent commits in your proccesses?

[raghud]

I went from 30 MI to 3 hours on Undo retention because off this problem. But too may frequent commits also a problem because Oracle is trying to update the buffered rows which are parallelly selected by other processes. I already recommanded users to commit frequently.

[slimdave]

Originally posted by raghud
I already recommanded users to commit frequently.

That may be the wrong advice. Frequent commits are a cause of, not a solution to, this problem.

[grjohnson]

Is it a batch job type process that is failing? You shouldn't commit in a cursor.

[raghud]

The app is using lot of cursors. Might be they are commiting lot of times.

[slimdave]

That would be the first place to look, i think. If the error really is due to committing inside a cursor then there is no amount of UNDO tweaking that will help.

[julian]

Originally posted by grjohnson
I'd be inclined to think you may be getting the error due to a long running query/report on your DB. Therefore I'd have receommended either a). A larger retention time or b). Run the query/report out of the main business hours.

Julian, could you explain why decreasing the retention period would reduce the occurance because it has me a little confused, as Oracle and my own experience tell me to increase. But maybe I'm the one confused

Cheers,

The bigger the retention period, the more commited information needs to be kept in the undo segments. Thus the chance for Oracle to overwrite such commited transactions grows. If such info is overwritten, then ORA-01555 will be raised. That's OK as this is just a warning.