Re: Enqueue TX level 4 wait -- blocks dump
- From: "Diego Cutrone" <diegocutrone@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 3 May 2004 15:07:03 -0700
Hi Cary.
Unfortunately I don't know that.
I have the inserts commands, but they are wrapped and binded, check it out:
WIP_REQUIRSITION_LINES
INSERT INTO WIP_OPERATIONS (
WIP_ENTITY_ID,OPERATION_SEQ_NUM,ORGANIZATION_ID,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,OPERATION_S
EQUENCE_ID,
STANDARD_OPERATION_ID,DEPARTMENT_ID,DESCRIPTION,SCHEDULED_QUANTITY,
QUANTITY_IN_QUEUE,QUANTITY_RUNNING,QUANTITY_WAITING_TO_MOVE,QUANTITY_REJECTE
D,QUANTITY_SCRAPPED,
QUANTITY_COMPLETED,FIRST_UNIT_START_DATE,FIRST_UNIT_COMPLETION_DATE,LAST_UNI
T_START_DATE,
LAST_UNIT_COMPLETION_DATE,PREVIOUS_OPERATION_SEQ_NUM,NEXT_OPERATION_SEQ_NUM,
COUNT_POINT_TYPE,
BACKFLUSH_FLAG,MINIMUM_TRANSFER_QUANTITY,DATE_LAST_MOVED,ATTRIBUTE_CATEGORY,
ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7
,ATTRIBUTE8,
ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRI
BUTE15,
OPERATION_YIELD,OPERATION_YIELD_ENABLED )
SELECT
:b1,:b2,:b3,:b4,:b5,:b4,:b5,:b8,DECODE(:b9,0,'',:b9),DECODE(:b11,0,'',:b11),
DECODE(:b13,1,:b14,-999),DECODE(:b14,0,''
PO_REQUISITION_LINES
INSERT INTO PO_REQUISITION_LINES
(
REQUISITION_LINE_ID,REQUISITION_HEADER_ID,LINE_NUM,LINE_TYPE_ID,CATEGORY_ID,
ITEM_DESCRIPTION,UNIT_MEAS_LOOKUP_CODE,UNIT_PRICE,QUANTITY,DELIVER_TO_LOCATI
ON_ID,
TO_PERSON_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,SOURCE_TYPE_CODE,LAST_UPDATE_L
OGIN,
CREATION_DATE,CREATED_BY,ITEM_ID,ITEM_REVISION,QUANTITY_DELIVERED,SUGGESTED_
BUYER_ID,
ENCUMBERED_FLAG,RFQ_REQUIRED_FLAG,NEED_BY_DATE,LINE_LOCATION_ID,MODIFIED_BY_
AGENT_FLAG,
PARENT_REQ_LINE_ID,JUSTIFICATION,NOTE_TO_AGENT,NOTE_TO_RECEIVER,PURCHASING_A
GENT_ID,
DOCUMENT_TYPE_CODE,BLANKET_PO_HEADER_ID,BLANKET_PO_LINE_NUM,CURRENCY_CODE,RA
TE_TYPE,RATE_DATE,
RATE,CURRENCY_UNIT_PRICE,SUGGESTED_VENDOR_NAME,SUGGESTED_VENDOR_LOCATION,
SUGGESTED_VENDOR_CONTACT,SUGGESTED_VENDOR_PHONE,SUGGESTED_VENDOR_PRODUCT_COD
E,UN_NUMBER_ID,
HAZARD_CLASS_ID,MUST_USE_SUGG_VENDOR_FLAG,REFERENCE_NUM,ON_RFQ_FLAG,URGENT_F
LAG,CANCEL_FLAG,
SOURCE_ORGANIZATION_ID,SOURCE_SUBINVENTORY,DESTINATION_TYPE_CODE,DESTINATION
_ORGANIZATION_ID,
DESTINATION_SUBINVENTORY,Q
However, we are talking about an Oracle apps, so maybe with these statements
someone in the list might know if this module behaves the way you have
described below.
Thank you!
Regards,
Diego.
----- Original Message -----
From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Sunday, May 02, 2004 10:36 PM
Subject: 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
> - Hotsos Symposium 2005: March 6-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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)
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
----------------------------------------------------------------
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:
