Re: Doubt related do INITRANS

Cache 1? That doesn't make sense. There is no justifiable reason to force cache 1. An Oracle sequence can't guarantee gapless sequences, regardless of the cache setting. If you really *need* a gapless sequence, you can't use a sequence. Or better, revisit the design to avoid the requirement for the gapless sequence.

-Mark

On Thu, Dec 17, 2020 at 11:53 AM Powell, Mark <mark.powell2_at_dxc.com> wrote:

>
> Eriovaldo, I hope your design scales. You will want to test scalability
> as best you can, as early in development as you can before this hits
> production.
>
>
> 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:* Thursday, December 17, 2020 10:03 AM
> *To:* Jonathan Lewis <jlewisoracle_at_gmail.com>
> *Cc:* ORACLE-L <oracle-l_at_freelists.org>
> *Subject:* Re: Doubt related do INITRANS
>
> Jonathan,
>
> Unfortunately I need to use cache equal to 1. This is my strong
> requirement.
> It involves the whole infrastructure dimension and now, events of wait in
> the database are being supported by the infrastructure and under control.
> Many thanks for attention.
>
> Regards
> Eriovaldo
>
>
> Em qui., 17 de dez. de 2020 às 11:41, Jonathan Lewis <
> jlewisoracle_at_gmail.com> escreveu:
>
>
> 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