RE: Block dump - Uncommitted TXN - Help Urgent

  • From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
  • To: "BN" <bnsarma@xxxxxxxxx>
  • Date: Thu, 21 Sep 2006 17:03:21 +0200

> Yes I saw  Request=4 for quite a few sessions. I ahve Identified the
> Tables that needs INITRANS Bump. Is there a way to figureout  how
> much should I bump for each table in question

If you really experience ITL waits I'm not aware of THE method to
correctly size these stuffs... I'll probably do it in this way...

1) I would find out if the problem is due to MAXTRANS or to free space.
In fact Oracle should dynamically allocate additional ITL slots if
necessary, provided you have free space in the blocks *and* MAXTRANS has
not been reached. 

2a) If the problem is MAXTRANS, i.e. if the number of ITL slots has not
been (unnecessarily) limited, then there are two possibilities:

- Increase MAXTRANS.
- Increase INITRANS.

If there are few ITL waits (few means that the ratio between the number
of DML and the waits is small, e.g. few percent) the former is probably
If there are lots of ITL waits, i.e. INITRANS is way too small, the
latter is probably better.

2b) If the problem is free space, then there are two possibilities:

- Increase PCTFREE.
- Increase INITRANS.

To choose between these two the same method as before can be used.

Note: IMHO is often better to set MAXTRANS to a high value and increase
PCTFREE of 1 or 2 percent instead of setting INITRANS to very high


Other related posts: