Re: INITRANS and MAXTRANS are confusing me?

"Noons" <wizofoz2k_at_yahoo.com.au.nospam> wrote in message news:Xns933DD52833E1BTokenthis_at_210.49.20.254...
> Following up on Richard Kuhler, 13 Mar 2003:
>
> >
> > I'll agree only if you qualify that statement to say 'as long as no
> > block is out of transaction entries'. If there are 10 rows in a block
> > and only room for 1 transaction entry then only 1 session can lock rows
> > in that block. So Transaction 1 locks the first row and that's it, the
> > rest of the rows cannot be locked now. All other transactions wanting
> > to modify rows in that block must wait (or fail due to deadlock).
>
> That,I'm sorry, is completely wrong. Or you are running a
> weird version of Oracle. OF COURSE you can have more than one
> process updating each a single row (or multiple rows) in a block.
> Provided they are not the SAME rows. It's so simple to prove
> I won't even bother. Otherwise it wouldn't be row locking!
> Something else is going "clunck".
>
> INITRANS is a starting point, but the number of
> locks is dynamically expandable. What you probably have is
> very serious contention for the transaction entries, which
> is indeed a problem.
>

Hi Nuno,

I think what Richard is getting at is that there is no free space left in the block for additional transaction entires to be created. If the block is absolutely chokers and you set INITTRANS too low, you could run out of space and locking/deadlocking issues could result.

I would suggest a possible solution to many of Richard's problems would be to increase the freelist/freelist groups or look at using ASSM (Howard's not around is he ?) if it's the loading of the data that's causing the problems. If the problems are subsequent to that, increasing the pctfree or inittrans could be the goer (although you want to be cautious with any approach that potentially wastes space below the HWM). Unfortunately, all this requires the rebuilding of the objects to affect existing blocks.

Yes there could be issues with inappropriate block locking but it's generally a result of a preventable design flaw.

Cheers

Richard Received on Thu Mar 13 2003 - 06:48:52 CST