RE: Initrans (don't forget deadlock avoidance)
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 9 Sep 2004 18:26:59 -0400
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@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts:
