"Snapshot too old" for a report program

[Former Member]

We get "ORA-1555 error: snapshot too old" for a report job, NOT an update job.

We can add rollback segments to the DB.

Why we cannot increase the commit frequency to fix this issue?

Thanks!

[former_member204746 · Accepted solution]

possible solutions:

1. create a big rollback segment. search the web for keyword PSAPROLLBIG.

2. convert to PSAPUNDO, as started by others, check SAP note 600141.

3. analyze the program and if it is a Z program, ask your program to add more commits in his program.

Good luck.

[lbreddemann · Accepted solution]

> 1. create a big rollback segment. search the web for keyword PSAPROLLBIG.

> 2. convert to PSAPUNDO, as started by others, check SAP note 600141.

> 3. analyze the program and if it is a Z program, ask your program to add more commits in his program.

HI all,

of course Stefan and Eric have already given the right hints here.

But the main point here is not the configuration of the UNDO management.

I know that this kind of problems bears a big mental hurdle in it, which is also difficult to jump over for many supporters.

The notion of Undo/Rollback data beeing used mainly for ROLLBACK actions is unfortunately totally wrong.

The primary and main usage of this data is to deliver the consistent view (as already explained in this thread).

So the single big question here is: why does a single statement of your report needs to visit so many pages from a certain point in time?

In the wast majority of cases this is because the statement is badly tuned.

• Maybe there is no appropriate index available.

• Or it's just written badly, so that unnecessary data must be read by Oracle.

• Maybe the CBO does something wrong which must be checked.

• Maybe the data is scattered over too many blocks and clustering them together somehow could help

The core point here is: figure out what causes the statement to touch so many pages and fix that.

Then you are most likely to come over this problem.

All other recommendations, especially the move to automatic undo management: full ack.

But the solution to your problem is to fix your statement.

regards,

Lars

[former_member204746 · Accepted solution]

which point to my number 3:

3. analyze the program and if it is a Z program, ask your program to add more commits in his program.

[stefan_koehler · Accepted solution]

Hello Eric,

 ...ask your program to add more commits in his program.

As i already explained above .. more commits / a higher commit frequency will not fix the problem, because of the query still needs a consistent state of the table data (at execution start time).

@ Lars:

Of course you are right, that such a situation occurs mostly in a very long running query and this query should be tuned. But to be honest, if such a question is asked here in a forum .. do you think it is effective to ask for optimizer data, etc.? I don't think so.

This discussion reminds me of your blog: /people/lars.breddemann/blog/2009/09/09/a-fix-is-not-a-solution

Do you remember my last call (round about 1.5 weeks ago)?

Just check the status of it .... i would be a lucky customer, if i would just get a "fix" or even better just a reaction of development support and not the final solution. The same discussion )

Regards

Stefan

[lbreddemann · Accepted solution]

> As i already explained above .. more commits / a higher commit frequency will not fix the problem, because of the query still needs a consistent state of the table data (at execution start time).

that's correct

> @ Lars:

> Of course you are right, that such a situation occurs mostly in a very long running query and this query should be tuned. But to be honest, if such a question is asked here in a forum .. do you think it is effective to ask for optimizer data, etc.? I don't think so.

That totally depends on the kind of query, really.

If the query + execution plan is posted there's already something we could say.

If the index structure is posted as well - even better.

> Do you remember my last call (round about 1.5 weeks ago)?

> Just check the status of it .... i would be a lucky customer, if i would just get a "fix" or even better just a reaction of development support and not the final solution. The same discussion )

Don't tell me... I do know the status of this specific message, but as soon as it is forwarded it's out of reach for me.

BTW: the parameter workaround you asked there for - you may definitively go for it as long as you did not hear any better response.

It really shouldn't heavily impact your systems performance, as this specific feature is not something any of the transactions is build upon.

regards,

Lars

[stefan_koehler]

Hello,

> We can add rollback segments to the DB.

Are you really using manual undo management ? I would highly recommend to migrate to automatic undo management.

> Why we cannot increase the commit frequency to fix this issue?

A higher commit frequency will not fix the problem. Just think about why you receive an ORA-1555. Your query in the report have to read the data in a consistent state as it is at execution start time. Now if another session changes data (and commit or rollback it (in case of a rollback it depends on some other factors)) while your query is running, the old data is still needed for the query to get consistent data. These "old" data is stored in the rollback or undo segments. Now if you increase your commit frequency you still need the space for the "old data" in the rollback or undo segments to get a consistent query.

Regards

Stefan

[Former Member]

Hi!

We have the same issue.

Could you tell me how to migrate to automatic undo management? We are at Oracle 9.

Thanks a lot!

[stefan_koehler]

Hello Christy,

the procedure is described in sapnote #600141

Please keep in mind that such a problem can still occur with automatic undo management ... but it is much "easier" to handle.

Regards

Stefan

[Former Member]

You either add more rollback or move to undo

For information about rollback and how it works do read an excellent note on Rollback - 60223

For Undo mangement just follow the not mentioned by Stefan - 600141

Just decide on a good value for undo_retention 12 hrs is the default for it

Pravin