out of control rollback seg
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: out of control rollback seg

  1. #1
    Join Date
    Sep 2001
    Posts
    163
    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.

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    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.

  3. #3
    Join Date
    Sep 2000
    Posts
    77
    Are you doing any fetches across commits ?
    Thanks

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    How many extents do you have in a RBS?
    What is OPTIMAL set for?



  5. #5
    Join Date
    Sep 2001
    Posts
    163
    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.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  7. #7
    Join Date
    Sep 2001
    Posts
    163
    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.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.




  10. #10
    Join Date
    Nov 2001
    Posts
    3
    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.
    Aravinth

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width