Informatica and rollback segments

We are using Informatica Data Migration in Oracle. Yesterday the following problem occured. First, here are the constraints and environment: 1) I cannot use alter transaction use rollback segment since there is no scope inside Informatica (the user is not using any procedure where you can write it). 2) Data migration is of some 10 lakhs records. 3) We have 4 rollback segments: rb01(10MB), rb02(10MB), rb03(10MB) and rb04(970MB). I got "snapshot too old" error for the rb01 rollback segment. I don't know why Oracle used such a small segment for such a big transaction when it had one big transaction available. So, my solution was to make all small rollback segments offline. We are then left with only one big rollback segment. I started the transaction, and after 30 min I made all rollbacks online (since it is production database). I guess that it will be forced to use the big rollback segment? I don't know whether I have done it correctly or not. Do you have any other suggestions? Is there any way by which we can force a particular session to use big rololback segments?

    Requires Free Membership to View

    Login

You have reached a problem that is very typical in today's marketplace. Many vendors are creating products that are nothing more than canned software packages. We call these packages Commercial Off The Shelf (or COTS) packages. These COTS packages are using Oracle (or other vendor) databases to store the data. Unfortunately, you have almost no control over what goes on with the software packages. I know that I've fought with COTS packages in the past. It's difficult because you are pushing the limits of a COTS package, but you don't have any control. And many times, making big changes in how the COTS package works against the database can violate any support agreement you have with the COTS package vendor. I know that I've run against poorly written SQL statements in COTS packages that could benefit from adding an index or two to a table, but doing so means I lose support. So what are you to do? Many times, one has to work with the vendor support staff to resolve the problem. This often means that the problem won't get fixed until the next release of their software. I know that it's frustrating!

In your case, the solution is simple. (I bet you were beginning to wonder if I would ever help you out!) I seriously doubt that your COTS vendor cares about your rollback segments. That is entirely up to you. Rollback segments of 10 MBytes are not that small. One thing that you did not mention is the INITIAL and NEXT extent sizes or the MINEXTENTS or MAXEXTENTS settings. These can make a difference. I would suggest making all your rollback segments bigger to handle your transactions. You also only have 4 rollback segments. You may want to add more. It is not unusual for me to have 20 rollback segments up to 100 MBytes in size each. That means that my rollback segment tablespace is 2 GBytes!

Since you have no control over which RBS the COTS package will use, your only choice is to make all rollback segments of ample size!


This was first published in July 2001

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top