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: VIVEK_SHARMA <vivek_sharma_at_inf.com>
Date: Mon, 29 May 2000 21:32:10 +0530
Message-Id: <10512.107027@fatcity.com>


> Gaja Wrote :-
> I have used INITRANS of 4 in most OLTP environments with a
> DB_BLOCK_SIZE of 8K, for those tables that encounter a lot of
> concurrent changes. The rationale there was that in our
> "infinite wisdom", we knew that on an average, no more than 4
> txns. will touch a block at the same time.
>
> Qs. How to Find out what No. of Concurrent Transactions will touch 1
> Block ?
> Does it Depend on the SIZE of the Transaction Data inserted ? Any
> methodology for Calculation ?
>
>
> Steve Wrote > my guess is that it was actually an ITL entry shortage
> & Gaja wrote -
> Some set it the same value as INITRANS, but be aware that any txn.
> that visits a block and is unable to procure a slot (because
> none is available), then it will wait until a slot becomes
> available.
>
> NOTE - Though FREELISTS was set = 50 , INITRANS & MAXTRANS were ALSO set
> to 50
> The DB server is an 18 CPU machine .
> Qs Were we VERY WRONG in setting such Values for INITRANS & MAXTRANS ?
> Qs. Could such MAXTRANS Value have caused the Transactions to wait ?
>
>
> Gaja Wrote - If we assume even distribution of 50000 txn. in a given hour,
> it
> translates to 833.33 txns./minute, which in turn is 13.88 txns./sec.
>
>
> The Assumption is Correct . The Transactions are Spread out over the Hour
> NOTE - Almost ALL Transactions are INSERTS . NO Deletes / Updates
>
>
> >Steve Wrote > my guess is that it was actually an
> > >ITL entry shortage (see page 47 in my book).
> > > If this is 8i, you may want to consider using the
> > > 'alter table minimize records_per_block'
> >
>
> Vivek wrote:
> > On Oracle 7.3.4 with 8Kb DB_BLOCK_SIZE ,
> > for the respective TableS , should INITRANS & MAXTRANS
> > be Altered to Control the No. of rows per block ? How ?
> >
>

> Said another way, it controls the block level transactional
> concurrency. These txn. slots are utilized by txns. when they
> make modifications to the block. Conceptually you can think of
> each txn. signing in, using one of these slots before making
> modifications to any rows. Then at the row level there is a
> lock-byte which indicates the slot# which is locking the row.
> The slot# in turn will contain the txn.-id of the txn. locking
> the row.
>
>
> Vivek wrote:
>
> > Gaja ,
> > Thanks so much for the Explanation . Was the Greatest
> > Gaja Wrote >"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".
> >
> > Qs. 1 Should Number of Rollback Segments be INCREASED to
> > 12,000 approx from 160 or Some intermediate value ?
>
> I have not worked in any environment that required 12000
> rollback segments, but then again I need to see more of the
> world...;-). In your original posting you said that your system
> had to process 50000 OLTP txns. in 1 hour. We need to also
> verify how long a given txn. is active and how much work it
> performs.
>
> If I were you, I would work off the 13.88 txns./second number.
> Theoritically, using the formula, you do not need more than 4
> rollback segments, but then again you will have to size the
> rollback segments right and then monitor the database to see
> whether the contention goes away. One can speculate that even
> 160 rollback segments that you have now is too much, but one
> cannot categorically say that until one studies all the related
> metrics on your database.
>
> The formula (no. of concurrent txns./4) works for most
> environments, you will have to be the judge as to whether it
> works for yours. If it does not, then you will have to create
> more rollback segments as needed, until the contention is at a
> minimal.
>
> Vivek wrote:
>
> > NOTE That The Transactions OLTP in nature
> >
> > QUERY :-
> > select table_name,avg_row_len
> > from sys.dba_tables
> > where ...
> > /
> >
> > OUTPUT :-
> > TABLE_NAME AVG_ROW_LEN NUMBER of ROWS
> > INSERTed per Tran
> > ------------------------- ------------
> > ---------------------------------
> > DAILY_TRAN_DETAIL_TABLE 186 2
> > DAILY_TRAN_HEADER_TABLE 91 1
> > TEMP_DAILY_TRAN_TABLE 73 2
> >
> > Thus 1 Transaction = 550 BYTES - Correct ?
> >
>
> 1 txn. here affects 550 bytes of data, however that does not
> mean that you are generating 550 bytes of rollback. For
> inserts, the rowid information of the inserted rows is adequate,
> to perform a rollback operation later on if needed.
>
>
> > Gaja Wrote >The size of the extents in the rollback segments
> >> can be derived by performing an avg(used_ublk)
> >> in v$transaction during peak periods.
> >
>
> Vivek wrote:
> >OK -will revert back to you after next Transaction Runs
> > Will use the Query :-
> > select avg(used_ublk) from v$transaction;
> >
> Will wait for your feedback.
>
>
> > Gaja Wrote > "Proactive configuration of an optimal number of
> > freelists and the effective use of the INITRANS parameter "
> >
>
> Vivek wrote:
>
> > Qs. How is the Value of INITRANS , MAXTRANS to be determined
> > ? Any Thumb rule ?
>
> There are no hard and fast rules for INTRANS & MAXTRANS
> configuration. Basically it boils down to how many txns. will
> attempt to modify rows within the same block at the same time.
> Every environment is different.
>
> I have used INITRANS of 4 in most OLTP environments with a
> DB_BLOCK_SIZE of 8K, for those tables that encounter a lot of
> concurrent changes. The rationale there was that in our
> "infinite wisdom", we knew that on an average, no more than 4
> txns. will touch a block at the same time. Most txn. slot
> contention/problems, arise during inserts anyway and that
> problem can be alleviated by configuring (2 * # of CPUs) worth
> of freelists. Deletes and Updates are lesser of a problem.
>

Received on Mon May 29 2000 - 11:02:10 CDT

Original text of this message

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