Re: Enqueue TX level 4 wait -- blocks dump
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 3 May 2004 18:25:00 +0100
Can you clarify your comments about ITL and ITC.
Do you mean that the value from ini_trans in
dba_tables and dba_indexes were 10 and 11
respectively ?
If so, then you should expect the number of
ITL slots in the tables and indexes to be 10
and 11 (assuming the initrans was not altered
on the fly after data had been entered). If
you want to determine the actual concurrency
from the block dumps, you would have to guess,
based on how close the SCNs were in the ITL
entries.
TX/4 arises from many causes - the most likely
one on inserts into heap tables relates to collision
on primary keys and foreign keys.
Could you have multiple processes trying to
insert the same PK, waiting, failing, then using
the next value from a sequence number to try
again ? Could you have processes inserting
child rows for parent rows that had not yet
been created ... and so on.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Optimising Oracle Seminar
http://www.jlcomp.demon.co.uk/seminar.html
June 2004 UK Manchester
July 2004 Iceland
July 2004 USA California
Aug 2004 USA North Carolina
Sept 2004 UK Manchester
Sept 2004 USA NYC
Oct 2004 USA Boston
----- Original Message -----
From: "Diego Cutrone" <diegocutrone@xxxxxxxxxxxx>
To: "Oracle List" <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, May 03, 2004 9:35 PM
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.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
