Keeping old snapshots young with some Oracle magicJuly 13, 2011 at 2:18 pm | Posted in Oracle, Technical Tips | 1 Comment
Tags: snapshot too old
In my last wanderings on this site, I mentioned the Oracle error message “snapshot too old.” Once we determined this wasn’t a reference to an 8 x 10 glossy of Joan Rivers before her first face lift, we discussed the set of circumstances which can lead to this error, and then invited feedback on suggestions to eliminate or reduce the likelihood of this happening. After an unprecedented response to this question, I’m posting these bloggers’ solutions here.
From J. Kimmel in Los Angeles, CA . . . . . Night time is where it’s all happening!
Take those long-running reports and put them into a batch job to run late at night. That way admins don’t have to contend with all those updates that interactive users are performing during the day. If you have no undo segments being written to in the database, you’ll be able to avoid a “snapshot too old” situation.
From C. Sheen in Malibu, CA . . . FLASHBACK CHARLIE
If you want to be a winner, you’re going to want to implement the Oracle Flashback features. Set the initialization parameter UNDO_RETENTION to the maximum number of seconds you want to be able to be able to flash back. (I bumped that number way up to accommodate all my raging tiger blood.) Oracle will retain the undo data for the specified duration, thus preventing the situation where the required undo segment has been overwritten. This insures the database will be able to construct the read consistent view of the data being selected. Hey, does anybody know if you can do FLASHBACK CHARLIE without erasing the goddesses from my undo segment?
From L. Gaga, Outer Space. . .Make it automatic
Starting in release 11g, the default method for managing your undo is “automatic.” This means to let Oracle be responsible for specifying the undo tablespace(s) as well as creating the proper number of undo segments and sizing them appropriately. As the workload changes, Oracle will adjust either up or down the number of undo segments available for your instance.
There are two things to examine when allowing Oracle to manage the undo data. First, make sure you’re providing Oracle with an accurate read of your database. This is normally accomplished through statistics. And second, always, always make sure that Oracle is not using the SYSTEM tablespace to store the undo data. If somehow this should happen, you will definitely notice a big hit in performance, especially in an OLTP environment.
Until next time,
Bob aka orcltestguy