Re: MAXTRANS in indexes

In article <C7Mvyx.Do8_at_world.std.com>, edwards_at_world.std.com (Jonathan Edwards) writes:
> Oracle doesn't do page-level locking, but this MAXTRANS limit is almost as
> bad. You have to permanently allocate space in each page for concurrent
> transaction accesses, and lock out transactions when this overflows.

It's only a problem if you hit the limit. Have you hit it? How close are you to hitting it? What sort of queries are you running and on what hardware to get this problem?

>
> Isn't this an enormous problem, particularly on indexes? Lets say that I am
> indexing a table on a sequence-generated field. By definition, all inserts
> will be clustered into the last leaf of the index. Therefore it would seem
> that I need INITRANS = entries/leaf to avoid lockouts. At 23 bytes/tran,
> this means about half the index is wasted on transactions!
> THis then doubles the IO to this index.

Normally an index entry will be updated in memory (ie SGA) and will happen quickly followed by a commit. It should only be a serious problem if you have a lot of processes hitting the same block and each wants to hold the lock for a long time before the commit;

>
> And I can't double the block size for this index to compensate, because
> Oracle only supports a single global permanent block size, which is a
> severe limitation in its own right.

True. But why not double the size of the block for your entire database?

Name one database package in existence that has a variable length block size. The whole point of the BLOCK is to allow for fast I/O while permitting data to be stored internally in a variable length format.

If your system is processing that many update transactions per second then you will have a problem. Alternatively, lower your block size to reduce the number of concurrent locks on each block.

Just some thoughts.

Bruce.... pihlab_at_hhcs.gov.au Received on Fri May 28 1993 - 05:43:03 CEST