Keeping old snapshots young with some Oracle magic

July 13, 2011 at 2:18 pm | Posted in Oracle, Technical Tips | 1 Comment

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

1 Comment »

RSS feed for comments on this post. TrackBack URI

  1. […] ETA: Be sure to read Bob’s followup post here: […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Entries and comments feeds.

%d bloggers like this: