| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Enqueue TX level 4 wait -- blocks dump
Is it an insert of a row whose key value is the same as the key value of
some other uncommitted insert?
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 5/7 Dallas, 5/18 New Jersey, 6/22
Pittsburgh
- SQL Optimization 101: 5/3 Boston, 5/24 San Diego, 6/14 Chicago
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Diego Cutrone
Sent: Monday, May 03, 2004 3:35 PM
To: Oracle List
Subject: Enqueue TX level 4 wait -- blocks dump
Hi everybody,
I have a question on TX enqueues.
I'm seeing a lot of TX enqueues level 4 on a table. I have also found
that the statement that was waiting on this wait was an ***INSERT***
operation.
Oracle 8i
Table:
ITL = 10
4 FREELISTS
4 FREELISTS GROUPS
PCTFREE 20
I dumped every table block and checked the ITC entry. I found ITC was
almost always 10 for every block.
Then I checked for the average free space within the blocks, (supposing
the block had needed an additional ITL slot) and again I found that in
every block there was at least 80 bytes free. (as Avsp reported).
So I went for the indexes
ITL = 11
4 FREELISTS
4 FREELISTS GROUPS
PCTFREE 10
and I found no problem at all in the ITC entries.(most of them were 11)
But I found many blocks with kdxcoavs = 0. (no free space on them).
Taking into consideration that we're talking about an INSERT, Can it be possible that the TX 4 is being caused by an ITL shortage in the index?
(All slots are being used and there's no free space in the block, so when a new INSERT needs to put its entry in that block, despite of the fact that this block will surely need to split because there's no free space left in it, the transaction first needs to get a free ITL slot and as they are all taken and there's no free space it has to wait in a enqueue TX 4. Is this the way it works on this case?)
What do you think?
Thanks,
Diego.
PS: (I still have to check one last index, but I believe I will find the same I have just found so far)
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon May 03 2004 - 12:34:14 CDT
 |
 |