Re: INITRANS and MAXTRANS are confusing me?

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 Tue Mar 11 2003 - 20:11:00 CST