is there a way to limit the number of transactions allowed in the rollback segment?
Printable View
is there a way to limit the number of transactions allowed in the rollback segment?
Set Transactions_per_rollback_segment to a desired value in the init.ora file.
does that apply for public rollback segments?
The transactions_per_rollback_segment parameter does not limit the number of transactions allowed in the rollback segment.
It is used to determine how many PUBLIC rollback segments to bring online at startup. (transactions/transactions_per_rollback_segment) rounded up.
TRANSACTIONS_PER_ROLLBACK_SEGMENT can not be used to limit the number of transactions per rollbeck sagment. And it doesn't matter if RBs are public or private.
This parameter is there only to help the server to calculate how many rollback segments it has to open upon startup, based on the TRANSACTIONS parameter. But it doesn't impose any limit in the number of transactions each rollback can actually handle.
AFAIK there is no way you can limit the number of transactions per rollback segment. Unless of course you have only one rollback segment online, whereas TRANSACTIONS parameter impose that limit.
When many transactions are concurrently proceeding, more rollback information is generated at the same time. You can indicate the number of concurrent transactions you expect for the instance with the initialization parameter TRANSACTIONS, and the number of transactions you expect each rollback segment to have to handle with the initialization parameter TRANSACTIONS_PER_ROLLBACK_SEGMENT. Then, when an instance opens a database, it attempts to acquire at least TRANSACTIONS/ TRANSACTIONS_PER_ROLLBACK_SEGMENT rollback segments to handle the maximum amount of transactions. Therefore, after setting the parameters, create TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_ SEGMENT rollback segments.
TRANSACTIONS_PER_ROLLBACK_SEGMENT specifies the number of concurrent transactions you expect each rollback segment to have to handle. The minimum number of rollback segments acquired at startup is TRANSACTIONS divided by the value for this parameter. For example, if TRANSACTIONS is 101 and this parameter is 10, then the minimum number of rollback segments acquired would be the ratio 101/10, rounded up to 11.
But the question was "how to limit the number of transactions allowed in the rollback segment?", not how to specify the expected number of transactions per RB. As allready said, you can't use this parameter to limit the allowable transactions per RB.
Limit how? Like you want to specify (for example) a maximum of 17 transaction for RBS05? Not way to do that!Quote:
Originally posted by mgg
is there a way to limit the number of transactions allowed in the rollback segment?
But as you know there is the radical way of limitting them to 0 by taking the RBS offline :-)
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?
Nope.
The transactions_per_rollback_segment parameter is only read and used at instance startup to determine whether or not the system should bring more public rollback segments online.
It doesn't limit usage. If t/tprs rounded up <= online public rb segs, nothing happens. End of story.
It's a beautiful day in Nebraska!
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.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?
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.
Or, you could just size your rollback segments correctly and not worry about it.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.
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.Quote:
Originally posted by mgg
So are you stating that the transaction(long running) could itself cause the ora1555?
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!