Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Transactions waiting for Lock on Rollback Segments

RE: Transactions waiting for Lock on Rollback Segments

From: <LBhat_at_LEVI.com>
Date: Sun, 21 May 2000 19:02:30 -0700
Message-Id: <10504.106178@fatcity.com>


Hi Steve/Gaja,

Thanks for the detailed notes.

This creates a doubt in my mind how far it is worth to trust the Oracle Manuals.

Regards.

Bhat

-----Original Message-----
From: Gaja Krishna Vaidyanatha [mailto:gajav_at_yahoo.com] Sent: Monday, May 22, 2000 2:54 AM
To: Multiple recipients of list ORACLE-L Subject: RE: Transactions waiting for Lock on Rollback Segments

Friends,

I totally agree with Steve's sentiments. There are many issues on the table here, and my response to all of them make this posting a long one. Thank your for your patience is reading this through.

The limitation to prevent us from creating more than 50 rollback segments was lifted in early Oracle7, when Oracle started supporting the max_rollback_segments parameter. The rollback_segments parameter itself could not support more than 50 entries prior to Oracle7, and hence the limitation. The documentation has not changed with time. This limitation has been long lifted. If there is justification and a need to create more rollback segments, then by all means go right ahead and do it. Personally, I have created as many rollback segments as the value of the max_rollback_segments parameter.

The "ball park" optimum number of rollback segments that is required for most environments can be derived by the formula : "no. of concurrent txns/4". The size of the extents in the rollback segments can be derived by performing an avg(used_ublk) in v$transaction during peak periods. The number that is acquired from the above average should then be raised to the next power of 2(e.g. if the avg(used_ublk) is 14045, then the initial and next size for the rollback segments can be 16K). Obviously, this is not the size for the big_roll rollback segments that you use for your batch jobs.

The goal here is to try to accomodate the undo entries of a transaction in 1 extent, to avoid re-reading the rollback segment header. The rollback segment header contains the transaction table and the pointers to the location where a given transaction's undo entry is located. Reading the transaction table over and over again can and will cause contention for that rollback segment.

I am not in any way suggesting that multiple transactions should not write their undo entries to the same extent. All I am trying to communicate here is a design to reduce contention on the rollback segment header and the transaction table that it hosts.

The minimum number of extents in a rollback segment should be at least 20. Simulations and tests done in Oracle's Internal Labs in the past using this MINEXTENTS value, has shown to reduce the probability of the "snapshot too old" error occurring.

Personally, I am not a big fan of the "OPTIMAL" parameter as this again increases the probability of the "snapshot too old" error. I'd rather shrink the rollback segment via a job at a time that is appropriate (wee hours of the morning) than have "OPTIMAL" kill a long-running query (batch report). I know if you are running a database prior to 7.3, then you are out of luck. I have observed that adhering to the above set of rules has helped me proactively manage the rollback segment problem very well. Your mileage might vary, but is definitely worth the effort.

The Tuning Manual's recommendation to create as many freelists as the number of concurrent inserts is extreme, absurd and makes no sense. The law of diminishing returns will come to play in a significant manner, if this recommendation is implemented.

For e.g., if a 1000 concurrent inserts frequently occur on table, the effect of configuring 1000 freelists (even if supported) on the high water mark of the table is palpable. When a 1000 freelists are configured for a table, and when a new extent is allocated for the table with say a 10000 blocks, then each freelist will contain 10 blocks. When multiple concurrent inserts hit this table, each transaction can and will potentially be assigned a different freelist and hence will insert into different blocks.

We are all aware of the fact that a full table scan will read and process "high water mark" worth blocks. By configuring an outlandish number of freelists, on very large tables with many concurrent inserts, the "high water mark" of the table is unnecessarily inflated, which can cause stress on the I-O sub-system, while performing full table scans on these tables. Basically, you are doing more I-O than what is required.

In most environments, the need for more than (2 * # of CPUs) worth of freelists should be questioned. As I mentioned before, the law of diminishing returns will play its part and the performance "delta" between having a table with (2 * # of CPUs) worth of freelists and anything that is significantly higher, will be miniscule. Plus, you will have to factor the additional cost of the inflated high water mark of the table.

I know this from having done some tests in 7.3.5 and I am pretty sure that this functionality has not changed radically in Oracle8/8i. Proactive configuration of an optimal number of freelists and the effective use of the INITRANS parameter should take care of block level contention in most cases. Thanks for your patience.

Best Regards,

Gaja.

Gaja Krishna Vaidyanatha   | 3460 West Bayshore Road,
Manager - Integration      | Palo Alto, CA 94303
& Consulting Services      | gaja_at_brio.com
Global Alliances           | (650)-565-4442
Brio Technology            | www.brio.com 

"Opinions and views expressed are my own and not of Brio Technology"



Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger. http://im.yahoo.com/
-- 
Author: Gaja Krishna Vaidyanatha
  INET: gajav_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
Received on Sun May 21 2000 - 21:02:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US