Re: INITRANS and MAXTRANS are confusing me?

I'm sure you realize that you made a misstatement here given the content of your posting. I think it's worth pointing out that Oracle does NOT "support an unlimited number of concurrent locks". In fact the original poster's question points this out since MAXTRANS limits the number of transaction entries (and hence locks) allowed per block. Besides the possibility that there's no more free space in the block (in which case you might only get INITRANS transaction entries/locks in that block).

We're actually having problems with this because of the large amount of parallel ETL activity on a data warehouse system I'm currently working on. Deadlock failures are becoming pretty common in some of our processes because of this limitation. We're at the point that we may have to bump INITRANS up and rebuild the table (since the setting doesn't fix existing blocks). Unfortunately, this will mean reserving space in every block for something that may never actually be needed. Even then, the only way we can guarantee there won't be a deadlock would be to set INITRANS at least as high as the maximum parallelism. In our case, that would mean reserving about 10% of every block just for transaction entries (that translates into money).

While I see many of the advantages to the Oracle scheme, I think it's foolish to think it doesn't have it's own problems.

Richard Kuhler

Mark D Powell wrote:
>
> w.gamble_at_pentasafe.com (Wes Gamble) wrote in message news:<37fe2b65.0303111159.ca39183_at_posting.google.com>...
> > Thanks for any help in advance.
> >
> > I just learned about INITRANS and MAXTRANS and what their purpose is.
> > Why does information about the individual transactions have to be
> > stored in data blocks to achieve locking? Why can't you just keep
> > track of the number of transactions which are accessing data in the
> > block in order to manage locking? Do individual transactions have to
> > be included in the block in order to satisfy Oracle's read consistency
> > model?
> >
> > If it helps to understand my confusion, I am used to the Informix
> > world where lock data was centrally managed, not with the data.
> >
> > Pointers to relevant references are welcomed.
> >
> > Thanks,
> > Wes Gamble
>
> Wes, by using an interested transaction list, ITL, to manage locks in
> the actual data blocks Oracle can support an unlimited number of
> concurrent locks, while an rdbms manager that manages the locks in
> memory is limited to the amount of resource it can used. This is why
> DB2 has lock escalation and recommends frequent commit so lock
> resources can be fried up. Oracle on the other hand recommends you do
> as much work as possible before commiting (limited by available rbs
> space and concurrent DML activity against the same table/indexes).
>
> Initrans preallocates ITL work areas into the block so that Oracle
> does not have to try to allocate a work area while processing the
> block during an insert, update, or delete. If a free ITL does not
> exist to support a DML action Oracle will allocate one if space is
> available. This is relativey expensive and preallocating potentially
> saves time on objects subject to concurrent block access. Maxtrans
> limits the number of ITL entries that will be taken from a block.
> Each ITL consumes 24 bytes.
>
> Before 9 the default was 1 ITL per table block and 2 per index. I
> believe that with 9 or 9.2 Oracle upped the default to 2. Tables that
> use a key created via a sequence generator where the inserts, at least
> in the index, are going to be in the same block may benefit from
> having multiple ITL entries preallocated.
>
> HTH -- Mark D Powell --
Received on Wed Mar 12 2003 - 13:43:02 CST