Rollback Segment Issue - Part II

[Chris Fung]

Hi all,

While I am reading the Oracle Concepts document (8i release2, part no. A76965-01), there is a description (P.4-23)on rollback segment which made me so confuse....

Quote:
Many active transactions can write concurrently to a single rollback segment - even the same extent of a rollback segment; however, each data block in a rollback segment's extent can contain information for only a single transaction.
unQuote

From my understand, the deallocation for the rollback segment extents will happen when the rollback segment's extents are no longer occupied by active transaction. If there is more than one active transactoin can write into a single extent or groups of extents, any in-doubt transactions will hold up the extents for a long period of time and eventually will cause the rollback segment to acquire additional extents. In worst case, the rollback segment may reach its maxextents and on the other hand will saturate the Unix filesystems.

What I am thinking is that if an extent is only reserve for one transaction only, then there will be no dependency for rollback segment deallocation (when setting the optimal value) and hence the rollback segment may not growth in an uncontrol manner.

Appreciate for you comments,

Chris,

[T G Manikandan]

Rollback segments can be design based on the size of transactions you run on the database.
Depending on the size of transactions you can set the MAXEXTENTS for the rollback segment.

If you have small number of transactions you can use smaller rollback segments for smaller transactions so that they are more likely to be cached.

The problem with the smaller rollback segments is that you get snapshot too old errors.


For your thoughts you can go for


1. you can allocate a seperate rollback segment to large transactions by using the
SET TRANSACTION USE ROLLBACK SEGMENT.

Like the large transactions can eat away all the extents if you don't allocate seperate rollback segment.


Thanks
2.Use can use

TRANSACTION_PER_ROLLBACK_SEGMENT initialization parameter to set the number of transactions per rollback segment.

[Rajesh Raghavendran]

Also remember that, when Optimal is not set, then Oracle will not shrink the RBS . This can eat away your tablespace even when not many transactions are active in the database.

IF this did not clarify your question.. what exactly is your question?

[Chris Fung]

Hi all,

From my perspective, if transactions accessing the rollback segment is restricted only down to the data block level but not the extent level (according to Oracle's concept guide), any abnormal transactions or long running transactions will hold up a lot of extents which is shared by more than one transactions. Even some of the other transactions are commited, the abnormal transactions will still hold up a lot of extents. At this moment, if new transactions are entering the problemed rollback segment, the rollback segment then will growth in an uncontrolable manner and this is highly undesirable !!

I just want to confirm whether my point of view is correct or not, so writing to seek for comments from U guys.

By the way, TG, the "TRANSACTIONS_PER_ROLLBACK_SEGMENT" parameter is no used to limit the number or transactions that can use a rollback segment. It only used to determine (Transaction/Transaction_per_rollback_segment)the number of rollback segments an instance can acquire when database starts up.

Cheers,

Chris,

[Brian Crabtree]

Kind of. In this case, rollback segments are used in a round-robin approach. Transactions automaticlly use the least-used rollback segment, so if you have 8 rollback segments, and 5 transactions, they will each use a single rollback segment, and no rollback segment will hold 2 transactions.

The "TRANSACTIONS PER ROLLBACK SEGMENT" is correct though, defaulting to 4. In this case, with 8 rollback segments, you can only have 32 concurrent transactions. In a OLTP enviroment, this is not alot, while in a DSS or DW enviroment, this is most likely more than enough (not always however).

As for rollback segment growth, this is correct. Each rollback segment will take up as many extents as needed, and will hold them unless the OPTIMAL size is set, or you shrink them (alter rollback segment shrink). Either option will free the unused extents from a rollback segment.

Setting OPTIMAL is good in some cases, and not in others though. Setting OPTIMAL too low can increase the number of shrinks, which can impact performance as extents are constantly moved between the free and used extent tables. Either way, it needs to be tuned, along with tuning scripts to ensure that your rollback segments extents are filled to capacity before a commit is done.

Hope this answers your questions.

Brian