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. 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. 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.

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

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 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. 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. 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. 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.

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
@ http://www.ixora.com.au/ - For DBAs
@ http://www.christianity.net.au/ - For all

-----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/