RE: Initrans (don't forget deadlock avoidance)

Hi Mark and list,

Comments inline below ...

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/         - For DBAs
@   http://www.christianity.net.au/  - For all 

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
> Sent: Friday, 10 September 2004 8:27 AM
> To: Oracle-L
> Subject: RE: Initrans (don't forget deadlock avoidance)
>
>
> I'm going to have to quibble a bit with this, since the only
> way I know of
> to guarantee no deadlocks is to set initrans to at least the
> maximum number
> of threads updating a set of tables and maintaining an order
> of update list
> of the tables for all transactions against members of that
> set of tables.

Your INITRANS requirement is overstated. You get equivalent protection if you update the tables in order and avoid ITL waits. If you don't get ITL waits, then you cannot get an ITL deadlock.

> If
> any of the tables has high variability in the length of the
> values of some
> columns it can result in no space for additional ITLs which
> leads to an
> unneeded deadlock by obstructing update to rows in the block
> that would
> otherwise be available.

Sure it is possible to use up the PCTFREE allowance and be unable to dynamically grow the ITL, but this just leads to an ITL wait, not a deadlock. You need at least 2 such waits to have a deadlock.

> As opposed to deadlocks due to bad design or
> transactions updating tables in a haphazard order, I refer to these
> deadlocks as "false deadlocks" where they are an artifact of block
> transaction management as opposed to bad design.

The term "false deadlocks" is normally reserved for cases in which an assumed deadlock is reported, but there was in fact no loop in the full wait-for graph.  

> So in addition to avoiding ITL waits, I would also say to
> pre-emptively set
> INITRANS when you need to systematically guarantee no deadlocks.

I don't think you've made your case here. If ITL waits have been effectively avoided, then there is nothing to do "in addition" regarding the INITRANS setting. It cannot cause a deadlock, if it doesn't cause any waits.

> Of course this also implies that the architecture of your
> system includes a
> way to control the maximum number of updating threads against
> a given set of
> tables. If you don't have that, I don't believe you can guarantee no
> deadlocks in multi-table transactions.

If you're still talking about ITL deadlocks here, then yes it is possible to guarantee that they will not happen. You just need to limit the number of rows_per_block to the value of INITRANS.  

> If there is high variability in column value lengths, reserving an
> appropriate number of ITLs with INITRANS can be less space
> consuming than a
> very large PCTFREE setting, which in any case is not a
> guarantee of free
> space after updates.

I proposed a non-zero PCTFREE, not a "very large" one. Yes, a preemptive  ITL slot only wastes 24 bytes, much less than the 81 bytes of free space reserved by a PCTFREE of just 1 on a typical 8K block. But block free space is seldom all needed. If it is, then you're in row migration territory. So you don't need to allow extra free space for ITL growth. Just set PCTFREE to avoid row migration, and you'll have enough for modest ITL growth too. It is not a choice between wasting a little space or wasting a lot. It is a choice between wasting a little space or wasting none.

> If you know for sure there is a certain maximum
> variablility in the length of your rows after the initial
> insert, then you
> may be able to calculate that you can leave a large enough
> PCTFREE such that
> dynamic ITL growth will always be sufficient, but I think
> that maps out to
> the same amount of reserved space and is a moderately
> difficult calculation.

Basing PCTFREE on the maximum variability in row length is very wasteful, and adding an allowance to that for ITL growth might make things worse. Instead, PCTFREE should be as small as you can get it without row migration, and there should be no explicit allowance for ITL growth.

> If you can tolerate a certain level of deadlocks in your
> system then it may
> in fact be a good choice to improve your data density by
> refraining from
> setting INITRANS, as per Steve.

I think that by "deadlocks" you meant "ITL waits". Even so, I was not suggesting that they be tolerated - rather that they be used as an indicator of insufficient ITL slots (however, it could also indicate poor process design).

> With reasonable settings for
> PCTFREE you
> might in fact get the statistical likelihood of "false
> deadlocks" very low,
> especially if there is low variability in the change of
> length of your rows
> after initial insert.
>
> If someone knows another way to guarantee no "false deadlocks" due to
> insufficient ITLs, I'll gladly be educated.

Well, I mentioned above that you just need to limit the number of rows_per_block to the value of INITRANS. That will guarantee no ITL waits, as well as no ITL deadlocks. However, given that the risk can be well managed without it, and that the guarantee has a non-trivial cost, it would be strange to require that guarantee.

> Regarding your question about number of CPUs, please note
> that the number of
> CPUs is not tightly bound to the number of parallel threads
> running on a set
> of tables since there is no direct linkage between Oracle
> transactions and
> the OS scheduler. That is, you might have 10 threads running
> on a 4 CPU
> machine on the one hand and you might limit by architectural
> control to 10
> threads running on a 32 CPU machine.
>
> Regards,
>
> mwf
>
> -----Original Message-----
> <snip>
>
> No, don't set INITRANS to a non-default value unless you need to do so
> to avoid ITL waits. ITL waits are counted in V$SEGSTAT. The ITL can
> grow dynamically and preallocating extra slots just wastes space.
> Instead, ensure that you never set PCTFREE to zero, except on truly
> read-only tables.
>
> Only set FREELISTS on tables that get enough concurrent inserts to
> cause dynamic ITL growth. Take a few block dumps, and use the highest
> ITL size seen, raised to the next prime. There is very little downside
> here, only a few more block below the HWM, so you don't have to be too
> careful.
>
> @ Regards,
> @ Steve Adams
>
> -----Original Message-----
> <snip>
>
> I have heared the best number for Initras is the number of
> CPU but what
> about the Freelists is there any standard for this parameter too.
> Any idea for any standards for these two parameters? Number
> of CPU or
> average number of Transaction or what?
> Thanks,
>
>
> Hamid Alavi
>
> <snip>

--
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/