Re: Doubt related do INITRANS

It seems a little unlikely that you could do better with a home-grown solution than with an Oracle sequence - but you may be doing something that eliminates the benefit of Oracle sequences but allows you to get extra mileage from your own. A critical error that appears frequently with Oracle sequences is that people forget to increase the cache (which defaults to 20) to something more appropriate - worst case scenario they set it to "nocache".

In you case, if you're going with a table with a few independent rows you could benefit by rigging the table so that you get one row per block. Set pctfree = 99 and, if necessary, add a column of varchar2(90) and a value of 90 characters in it so that the block is full as soon as you insert a row. This would avoid contention between sequences.

Regards
Jonathan Lewis

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

> 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