Re: Doubt related do INITRANS

Hi Jonathan,

I got the message.
The application can also access other lines with less frequency, and only one line exhaustively.
Based on the concept of the initrans, I will keep the initrans greater than 1 (I am using 10) for this table.
I had already done some tests using an Oracle sequence object and I got better performance with the table using 1 million lines. I need to review my tests.
Now, everything is clear.
Thanks for answering.

Regards
Eriovaldo

Em qui., 17 de dez. de 2020 às 08:36, Jonathan Lewis <jlewisoracle_at_gmail.com> escreveu:

>
> Of course, if you have 20 rows in this block of your "sequences" table and
> a couple of hundred users who are constantly trying to increment one or
> other of the sequences then you might need to set initrans to 20 to make
> sure that every row in the block can be locked by a different transaction
> at the same time. (At that point you might find that you run into problems
> with "buffer busy wait" waits - as Oracle can do on its SEQ$ table if
> someone creates a load of sequences with a low (or zero) sequence cache
> size.
>
>
> Regards
> Jonathan Lewis
>
>
> On Thu, 17 Dec 2020 at 11:30, Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>> 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
>>
>>
>>

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