Re: Doubt related do INITRANS

 Eriovaldo,

The purpose of INITRANS (pre-allocating an "interested transaction list") is to ensure that N different transactions can change (at least) N different rows in the block at the same time. If you're going to have only one row in the block then at most one tranaction can change the row at any one moment, any other transactions that want to change that row will queue up on the "transaction id" - i.e. the transaction table entry in the undo segment header - of the transaction that is currently modifying the row and wait (showing a TX lock waiting (usually) for a mode 6 lock) until the first transaction commits.

This means, among other things, that you won't need to set initrans to N because you think there will be N transactions trying to update that row, leaving it to default will be perfectly adequate. Try the experiment - create the table , insert the row, try to update it from 20 different sessions without committing. then commit each one in turn as it get to the front of the queue. Then dump the block - you'll find that the ITL still consists of two entries.

Regards
Jonathan Lewis

On Thu, 17 Dec 2020 at 01:37, Eriovaldo Andrietta <ecandrietta_at_gmail.com> wrote:

> Mark,
> I got the message.
> I consider not using ITL x 2 for indexes.
> In my situation the lock is not automatic, it is forced for update via
> application code.
> Imagine an application server submitting lots of requests for DML on a
> table for the same line. for example id = 10.
> For each submit the database does the select for update where id = 10 (all
> sessions use the same id) and does the commit for each submit.
> A requested line stays locked for a bit time until commit and after it the
> line is released for the next requester session.
> If this situation is considered as concurrency for DML operation in the
> same line/block, so I understand that I need more ITL pre-allocated.
>
> Thanks for the answer.
> Eriovaldo
>
>
>
> Em qua., 16 de dez. de 2020 às 18:21, Powell, Mark <mark.powell2_at_dxc.com>
> escreveu:
>
>> The manual says one ITL for a table but the last time I checked via a
>> block dump there are actually two. I do not know where you got the rule
>> that an index should have the table number of ITL X 2 but that is not
>> necessary. Only sessions that perform DML need to allocate an ITL.
>> Readers just check the ITL to find the UNDO version of rows when
>> necessary. Oracle will attempt to automatically add an ITL when needed.
>> Pre-allocation guarantees N number of concurrent DML sessions can access
>> the block and if you expect only 1 or 2 concurrent updaters will access a
>> block concurrently then the default value of two for the index is
>> adequate. Otherwise you should not need to pre-allocate more ITL's that
>> you expect concurrent update sessions to access the block in either the
>> table or its indexes.
>>
>> Mark Powell
>> Database Administration
>> (313) 592-5148
>>
>>
>> ------------------------------
>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
>> behalf of Eriovaldo Andrietta <ecandrietta_at_gmail.com>
>> *Sent:* Wednesday, December 16, 2020 3:46 PM
>> *To:* ORACLE-L <oracle-l_at_freelists.org>
>> *Subject:* Doubt related do INITRANS
>>
>> Hi,
>>
>> I saw in the database and documentation that For tables INITRANS is
>> equal to *1* and for indexes, 2.
>>
>> Doubts are :
>>
>> a.) for indexes, We always must use the number used by table * 2 ??
>>
>> b.) Imagine that I have an initrans 10 for a table and 20 for indexes of
>> this table (the table can have one or more than one ).
>> And an applicationI execute select for update for 1 line in
>> different sessions , like this, with purpose to retrieve a number
>> select number_seq into seq from table_a where id = 10 for
>> update;
>> commit;
>> It causes a lock. This table works like a database sequence.
>> Think in simultaneous connections and a lot of transactions.
>> When the commit is executed , the lock is released.
>>
>> Doubts here are :
>>
>> b1.) What is the advantage to configure initrans equal to 10 and
>> indexes equal to 20 for this table ???
>> b2.) What is different in this case doing commit line by line if I
>> had INITRANS equal 1 for table and 2 for indexes ?
>>
>> Regards
>> Eriovaldo
>>
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l