Ummar -- Thanks for the question regarding "Please sujjest how to avoid Frequent snapshot too old error", version 9.2.0.6.0

You Asked

Hi,

We are getting the Snapshot too old error very frequently in one of our Production instances. Since every time we increase the undo_retention. can you sujjest any permanant solution for the resolution of this error.

Thanks and Regards
Ummar

and we said...

You must

a) set your undo_retention to an interval that is longer than your longest running query

Maxquerylen in v$undostat can help you there.

b) ensure you have sufficient undo tablespace space - against, v$undostat will be crucial there, you can measure the amount of undo you generate in a given period of time - so as to determine how much space you need to allocate.

Reviews

Sujjest the best possible solution   June 14, 2007 - 2am Central time zone Bookmark | Bottom | Top Reviewer: Ummar Mir from INDIA Hi, I have checked it before as wel, since the longest running query goes beyond 20000, and our undo can support this as wel, but the only concern here is that if we increace the retention more than this what will be the impact on the other smaller running queries? Thanks and Regards Ummar Followup   June 14, 2007 - 7am Central time zone: ... what will be the impact on the other smaller running queries? ... they will continue to operate as before. Do not commit accross fetch   June 14, 2007 - 2am Central time zone Bookmark | Bottom | Top Reviewer: Mohamed Houri from FRANCE In order to help you avoiding snapshot too old error 1. Do not commit accross fetch LOOP .... COMMIT; -- if you have a commit here than remove it -- and do it outside the loop END LOOP; 2. Ensure (if possible) that you (or other sessions) are not updating data you have selected in the begining of your process 3. You can also put the content of your main query into a plsql table, close you cursor and loop on the plsql table instead of looping on your cursor 4. make your system restartable so that even if you encounter the ora-01555 you can restart your process from the point where it fails.

---

Write a Review