Re: INITRANS and MAXTRANS are confusing me?

Answers embd.

> Granted, we were unaware that the blocks available free space limited
> the number of transactions (besides MAXTRANS). That led us to to
> underestimate how important it is to increase INITRANS. More surprising
> was that this scheme can actually lead to runtime errors (deadlocks).
>
> Correct, we run some loads in 64 parallel tracks.

In this case you should look into FREELISTS and FREELIST GROUPS, e.g. you can load into let say 16 blocks in parallel, meaning that you'd have 4 concurrent inserts per block, thus inittrans 4 is enough. So, insert concurrency problems can be solved, without waste of space.

If you do have tens of concurrent *updates* in single block, then you don't have much more to do than increasing INI/MAXTRANS (if can't change the design). Also, you probably see lots of buffer busy waits as well then.

> Exactly, see Metalink Note:115467.1 "ORA-60 DEADLOCK DETECTED ON
> CONCURRENT INSERTS INITRANS/MAXTRANS" for a full description. Of course
> (as I pointed out) the only way to eliminate the deadlocks in Oracle's
> scheme would be to have INITRANS as high as the maximum number of
> transactions that could ever occur on a block.

Again, check the freelists option to relieve (and get rid of) insert concurrency problems.
Because if you say you have to have 30+ concurrent *different transaction updates per block* in a very big table (where this 10% space waste matters), then it's time to change the design or live with the (performance) overhead of ITLs.

>
> I didn't say IT looked foolish. I said the IDEA that Oracle's scheme is
> flawless is foolish.

Ok.
Perfectness depends on circumstances.

Tanel. Received on Wed Mar 12 2003 - 17:11:46 CST