Q
Problem solve Get help with specific problems with your technologies, process and projects.

Long-running query causes "snapshot too old" error

I have a production database and I am receiving the "ORA-1555: Snapshot too old" error when a long-running query is executed. I tried changing the UNDO_RETENTION to 40,000 seconds (as the query takes more than 24 hours to complete processing). The UNDO tablespace is set to autoextend mode.

I have a production database running in AUM and I am receiving the "ORA-1555: Snapshot too old" error when a long-running query is executed. I tried changing the UNDO_RETENTION to 40,000 seconds (as the query takes more than 24 hours to complete processing). The UNDO tablespace is set to autoextend mode. Please find below the queries and results for the suggested values:
SQL> SELECT MAX(maxquerylen) FROM v$undostat;

MAX(MAXQUERYLEN)
----------------
           74670

SQL> SELECT MAX(undoblks) FROM v$undostat;

MAX(UNDOBLKS)
-------------
       257252
The problem now is can we set the UNDO_RETENTION value to the above value (more than 74,670 seconds)? Will it create any performance-related issue for any of the other processes?
Your UNDO_RETENTION setting should be at least MAX(maxquerylen) as seen in V$UNDOSTAT over time. And your Undo tablespace should be at least MAX(undoblks) times your DB_BLOCK_SIZE parameter. Setting your UNDO_RETENTION too large will not necessarily cause performance problems, but it can require a larger Undo tablespace than you really need. Oracle will try to keep all of that Undo for a very long time. If I saw the max query length of 74,670 seconds, I would consider setting UNDO_RETENTION to at least 100,000 seconds and even 150,000 seconds, but I'm not sure that I would consider 400,000 seconds.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close