Skip to Main Content
  • Questions

  • "Error occurred : ORA-01555: snapshot too old: rollback segment number 313 with name "_SYSSMU313_2192191193$" too small"

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Riyaz.

Asked: October 17, 2016 - 4:48 am UTC

Last updated: October 18, 2016 - 3:03 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I'm posting a email conversation between a DBA and a Technical lead in our organization. Is the answer you gave 15 years back on "ORA-01555" is still valid for the new oracle releases.


DBA --
"This is due to long running query run out of UNDO space in instance 2.
We have allocate ample space for undo (60 GB) on each instances based on the retention. Need to see why this sudden demand and which query is causing this?

Oracle suggest that

The best solution is to tune the query can to reduce its duration. If that cannot be done then increase UNDO_RETENTION based on QUERY DURATION to allow it to protect the committed transaction slots for a longer period of time

So we have increased undo tablespace size up to 120 GB and retention up to 6 hours. Please note that we cannot increase undo size to unlimited and later reclaim, reclaim need a database downtime with critical "

"TL" --
https://asktom.oracle.com/pls/asktom/f?p=100:11%3A0%3A%3A%3A%3AP11_QUESTION_ID:1441804355350

"I myself have never hit an ORA-1555 in a live system (development sure, we were sizing it then tho). if you size your rollback adequately, neither will you.

The ORA-1555 happens when people try to save space typically. They'll have small rollback segments that could grow if they needed (and will shrink using OPTIMAL). So, they'll start with say 10 or so 1meg rollback segments. These rollback segments COULD grow to 100meg each if we let them (in this example) however, they will NEVER grow unless you get a big transaction.

If your database does lots of little transactions, the RBS will never grow on their own. They will stay small.

Now, someone needs to run a query that will take 5 minutes. On your system however the rollback wraps every 2 minutes due to lots of little transactions going on. In this system, ORA-1555's will happen frequently. What you need to do here is size rollback so that it wraps less frequently (less frequently then your long running queries). Here if I sized the rollback so that I had 10, 10meg segments (not so they could GROW to 10meg but that they are starting at 10meg) we would wrap maybe every 20minutes now. that'll give that 5minute query plenty of time to complete without reusing rollback it needs."

DBA --
"this is a pretty much old discussion before 11g. In 11g it slightly changes on undo management. However, below I’ve provided a note from Oracle regarding this issue and better if we follow it. Explaining ORA-1555 Error (Doc ID 467872.1)"

and Connor said...

The concept is by and large unchanged - its just that with auto undo management, some things are handled by the database and not the DBA.

But (we'll exclude LOBs here) there are only 2 things that are going to cause ora-1555.

1) A query runs longer than the undo_retention parameter. This is *not* to say a query running this long will *always* get a ora-1555, but they longer they run, the more likely it is they'll try request a block for a point in time (the start of the query) that can no longer be 'manufactured' with the available undo.

2) You are recycling undo *faster* than the undo_retention due to space pressure. So in this case your "effective" retention is less than the specified retention.

Both are really just examples of the same thing - a query needs to go back in time to a point where undo to resurrect data as that point in time is no longer available. So the solution is always going to be - make the queries shorter, or make the undo available for longer.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.