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: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
Date: Sun, 21 May 2000 10:46:05 -0700 (PDT)
Message-Id: <10504.106170@fatcity.com> 





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
Received on Sun May 21 2000 - 12:46:05 CDT












Original text of this message













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