rollback segments

Quote:

---

Originally posted by mgg
so if I set the transactions =15 and the transactions_per_rollback_segment =1 and I have 15 rollback segments will this then force a transction into a separate rollback segment?

---

I can't tell you this with 100% reliability, but I'm pretty sure you'll never find two transactions in the same rollback segments in the above scenario. Oracle is clever enough to distribute transactions evenly among available rollback segments, so whenever there is a RB segment without any active transaction it will use that one for the next transaction.

It is not because of the use of TRANSACTIONS_PER_ROLLBACK_SEGMENT=1, it is simply because you have 15 RBs available and never more than 15 concurent transactions in the system.

That is what I am trying to accomplish. Originally we had the default settings: transaction_per_rollback_segment = 5 and I believe that we were getting more than one transaction in the rollback segment. And since these are long running queries & updates we ended up with ora-1555. We'll be trying this weekend.
Thanks for eveyone's input!

ORA-1555 makes me belive you are on the wrong track.

Allowing only one active transaction per rollback segment will probably not prevent the occurance of ORA-1555. It is very easy to demonstrate the ocuurance on ORA-1555 with only one user (with only one session) in the whole system! So only one user session in the database, he is performing only very short transactions (much shorter than the available space in rollback segments)), there can theoreticaly be no more than 1 active transaction in the whole system, he has 15 rollback segments available all by himself, yet he will recieve ORA-1555.

Having more than 1 concurent transaction in the rollback segment is not the general reason for ORA-1555.

So are you stating that the transaction(long running) could itself cause the ora1555? by the way we have set the transaction that is 2gb in size. any suggestions would obviously be appreciated.

correction to my last statement, rollback segment size is 4gb

you can always assign a session to a specific rollback segment, that way you have more 'control' which session is using which rollback segment.

Quote:

---

Originally posted by TheSlob
you can always assign a session to a specific rollback segment, that way you have more 'control' which session is using which rollback segment.

---

Or, you could just size your rollback segments correctly and not worry about it.

Quote:

---

Originally posted by mgg
So are you stating that the transaction(long running) could itself cause the ora1555?

---

Well, sort of. For example if you are using "fetch accross commit". Here is a sample scenario. You are the only user on the system. You open a cursor to select a large amount of rows from a table. In a loop, you are fetching those rows, perform some changes to their values and then write those updates back into your table. after each update (or after a batch of updates) you perform a COMMIT and continue to fetch rows from your opened cursor. Sooner or later you'll face ORA-1555, although all your transactions are very short. But the importatnt thiong is - your query is long running and while it is fetching rows table blocks are changed by updates. Sooner or later your query will not be able to reconstruct a view of some block as it was when you opened your cursor.

So now to your scenario. You've set some large RB to be used by your long running transaction (it would be better to say long running query). But the more important thing is: which rollback segments are other concurent transaction using while changing data of the table you are selecting from? Let's say you've set your long running transaction to use BIG_RB, but there is 50 other sessions on your system.As all of those 50 session perform only very short transactions, you've set them up all to use 5 relatively small rollback segments. All off those 50 sessions updates the table you are selecting from in your big transaction. If the transaction rate of those 50 session is high enough and your 5 RBs are small enough, your large transaction will face ORA-1555. Because it has to use 5 small rollback segments to construct read consistent view of some block, but the changes for that block have allready been overwritten in small rollback segments. So assigning big RB for that transaction does not help you much for avoiding ORA-1555. It helps you to avoid "ORA-01650 unable to extend rollback segment" and similar messages for the data your transaction changes, but ORA-155 is allways about the data your transaction needs to read from some rollback segment. And you can't control from which RB segent that read consistent view must be constructed from, unless you controll which RB segment each and every transaction on the system uses!

So for ORA-1555 it is not important how you size rollback segment used by your long transaction, it is important how you size all of your rollback segments!