out of control rollback seg

[ocp2b]

One of the other DBA's I work with has been trying to debug a rollback problem. Here is the issue:

An application keeps getting an Ora 1555 - Snapshot to old error. We have copied the application and DB to a test machine for debugging purposes. Only one user is using the application and db. We have experimented numerous times with increasing the rollback segments/extent sizes to eliminate the error. Currently, we are up to 4 rollback segments totaling 600meg in space. Initial/next extents are 31 meg each. The database itself is only 300 meg. We are monitoring the undo(header) in v$waitstat. It is always between 1-5. The wraps in v$rollstat is small (1-5), the extends in v$rollstat is usually 1-5. We can't seem to get rid of the error message. We are at a loss. Does anyone have any suggestions? One other thing. The process(application) will only use one rollback segment at a time. The other three remain unused. It doesn't use the same one everytime. As we start and stop the application, Oracle ramdonly chooses the rbs to use.

Thanks.

[gpsingh]

why don't u dump the trace for this error.
Since it is consistent, did u check for which particular query/DML is running when this error comes.
U can see thin in v$sqltext.
what is the Oracle block size.

[CHALAVADI]

Are you doing any fetches across commits ?

[julian]

How many extents do you have in a RBS?
What is OPTIMAL set for?

[ocp2b]

To answer CHALAVADI question: I think we do have fetches across commits. But I'm not sure how to verify that. The application was written in C/C++ and I'm not familiar with C/C++.

To answer julian's question: Each rbs currently has 3 min extents and max extents unlimited.

The dba keeps changing the rbs's. Current settings are:
7 rbs, 3 min extents, 30 meg initial & next, Optimal 90 meg.

Hope this helps.

Thanks.

[pando]

I wonder is it so hard to configure a RBS? Looking at your storage parameters it seems that your DBA has no much clue about RBS

Avoid 1555

• A higher MINEXTENTS value, this will avoid new transactions wrap the the extents freed by the large transaction therefore the read consistency wont be lost
• Larger extent sizes, this will avoid the large transaction to extend in several extents therefore the possibility of extents being wraped by other transaction is decreased
• A higher OPTIMAL value, avoid dynamic extent allocation, in other words keep large extents or high number of extents (both of above points)

Higher RBS may and may NOT help, in a busy system it wont help that is for sure. For example you have 10 RBS and they are all active, one with the large transaction (let's call it RBS01) and the others with smaller transactions but higher number of transactions
imagine this:

1. there is a long select going and it uses the before image in RBS01
2. the large transaction commits
3. a new transaction comes in
4. Oracle assigns RBS01 to this new transaction because Oracle 's RBS allocating algorithm is that it assigns the transaction to the RBS with less active transaction
5. the new transaction overwrites the before image of previous large transaction
6. the long select lost the trace of before image, 1555 occurs

In an active/busy system this will happen because of the small transactions (and many) so more RBS does not really help

Also the question is asking of how to avoid overwritting extents in RBS since A & E are related of tuning the number of extents they should be more correct

geez I love cut & paste

If you have fetch across commit then there is nothing you can do except re-code your application to avoid 1555

[ocp2b]

Thanks. I've suspected the code to be the problem. How can I/us/we from the database side confirm/prove that the code is causing fetches across commits? Because as we all know, telling a developer their code is causing problems without any proof is asking for a fight.

[pando]

there are a few ways, you can either trace the session then tkprof which is 100% accurate or you can check v$sesstat of the session executing the process and check for statistics about commit and fetched rows

but just ask the guy who wrote the code, does he commit for each row modified? he would answer as a fool yes or no because it's not a tricky or offensive question and you are not telling him he is doing something wrong (yet), once he says yes then you can start bash him

[julian]

To answer julian's question: Each rbs currently has 3 min extents and max extents unlimited.

The dba keeps changing the rbs's. Current settings are:
7 rbs, 3 min extents, 30 meg initial & next, Optimal 90 meg.

Tell your DBA that a guy over the Intenet :-) told you to modify a bit the RBSs in the following way: 20 min extents, initial and next 1M, optimal 20M, and the number of rollback segments should equal (for 8i for example) number of concurrent users/4. Usually 4-8 RBSs is OK.

[sriniarvin]

Julian's suggestion could be tried.
However, OCP2b have mentioned that Oracle selects the RBS randomly. Try to allocate RBS of appropriate size depending upon the requirement, like:
1. Setting the parameter ROLLBACK_SEGMENTS=(rbs1,rbs2,....). Oracle will allocate rbs1 for the first assignment and rbs2 for the next ..........Hence arrange the RBS as per requirement.
2. Assigning rollback segments explicitly for the transactions.