Re: Doubt related do INITRANS

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