Re: RE: Transactions waiting for Lock on Rollback Segments

Hi !

The WAITS for ITL entry will happen only if you don't have space in that datablock to create an additional transaction slot. This is created dynamically subject to the free space in that datablock.

I hope this clarifies.

K Gopalakrishnan
Bangalore, INDIA

• Original Message ----- From: VIVEK_SHARMA <vivek_sharma_at_inf.com> Date: Sunday, June 4, 2000 10:02 am Subject: RE: Transactions waiting for Lock on Rollback Segments

> Gopal Thanks so much for the thread . Was very Enlightening indeed
>
> EXCERPTS - kGopal Wrote
> 1. Increase the INITRANS
> 2. Increase the PCTFREE for that table
> 3. Use ALTER TABLE MINIMISE RECORDS_PER_BLOCK to reduce the contention
>
>
> > 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
> >
>
>
> Qs. 1 The AVG_ROW_LEN = 186 , Since 2 rows are inserted per
> transaction into
> DAILY_TRAN_DETAIL_TABLE can we assume that 186 * 2 = 372 Bytes will
> populate the SAME Data Block ?
> Qs. 2 Further , since the DB_BLOCK_SIZE is 8 K , can we assume
> that a MAX.
> of 8000 / 372 = 21.5 Transactions can populate 1 Oracle Block for the
> DAILY_TRAN_DETAIL_TABLE ?
>
>
> Qs 3 INCREASE of PCTFREE - Any heuristic for the same ?
>
> KGopal wrote :-
> INITRANS is the way of defining the number of transaction slots.
> The higher
> INITRANS you define you get more the number of concurrent updates
> on that
> block.Otherwise the next transaction will wait till the
> transaction commits
> (or rollbacks).
> The Interested Transaction List grows dynamically as needed
> subject to the
> availability of space in that datablock or till it hits the
> MAXTRANS.Remember the transaction slots created once can not be
> used for
> storing the row information. For example if a datablock with (10
> INITRANS)10 transaction slots will be wasting 240 bytes (or 230
> bytes) of disk space.
> Qs 4 Are the Above 2 Statements NOT Seemingly Contradictory ?
> The 1st Statement indicated that a next Concurrent Transaction
> will WAIT
> till a previous transaction commits or rolls back
> The 2nd Statement indicates that the INITRANS GROWS Dynamically
> till it
> reaches the MAXTRANS Value .
> OR do you mean that the WAITS will occur only after INTRANS has
> become EQUAL
> to the MAXTRANS Value ?
>
>
> 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 ?
>
>
>
> -----Original Message-----
> From: kgopalakrishnan [SMTP:kgopalakrishnan_at_mantraonline.com]
> Sent: Wednesday, May 31, 2000 12:44 AM
> To: VIVEK_SHARMA
> Subject: ITL
>
> Hi !
>
> I had the same issue in PLSQL pipeline
Received on Sun Jun 04 2000 - 07:21:45 CDT