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@xxxxxxx>
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
*Sent:* Wednesday, December 16, 2020 3:46 PM
*To:* ORACLE-L <oracle-l@xxxxxxxxxxxxx>
*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
