When you see ORA-1555s do you just go ahead and increase UNDO_RETENTION ?
Recently, I had a string of ORA-1555s occurring frequently through the day.
One quick suggestion was to increase UNDO_RETENTION. Some DBAs might do that.
However, that would have been jumping to a conclusion.
This was a database that had been running 9.2 for more than 2 years. I had one other
9.2 database with the same schema, usage and comparable size {for another business
unit} and wasn't seeing ORA-1555s there.
The trace files showed me that these ORA-1555s were always for the same SQL.
I knew that this SQL was a Refresh Query being executed to refresh a Materialized View
in another database.
Apparently, it was only this query that was reporting ORA-1555s at about 1 out of every 4
refreshes (the refresh being hourly) . However, later, a developer also reported a
similar query on the same tables taking a long time (but not yet erroring out on ORA-1555s}.
So, the fix was not to increase UNDO_RETENTION but to tune/optimize the tables
and specific queries. The rest of the database schema had no issues.
Friday, January 2, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment