Re: Enqueue TX level 4 wait -- blocks dump -- Real Time case
- From: "Diego Cutrone" <diegocutrone@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 3 May 2004 17:38:34 -0700
Thank you Jonathan.
I changed the approach.
I started looking for this wait in realtime and I found it. As a matter of
fact it's still there andit seems it will be here for a while.
V$SESSION_WAIT
SQL> /
SID SEQ# EVENT
--------- --------- --------------------------------------------------------
--------
P1TEXT P1
P1RAW
---------------------------------------------------------------- --------- -
---------------
P2TEXT P2
P2RAW
---------------------------------------------------------------- --------- -
---------------
P3TEXT P3
P3RAW
---------------------------------------------------------------- --------- -
---------------
WAIT_TIME SECONDS_IN_WAIT STATE
--------- --------------- -------------------
112 49732 enqueue
name|mode 1.415E+09
0000000054580006
id1 196661
0000000000030035
id2 514403
000000000007D963
0 8614 WAITING
121 49454 enqueue
name|mode 1.415E+09
0000000054580004
id1 1114137
0000000000110019
id2 17942
0000000000004616
0 6072 WAITING
V$LOCK
1 select sid, type, id1, id2, lmode, request,ctime, block
2 from v$lock
3 where type in ('TX', 'TM')
4* order by 1
SQL> /
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
--------- -- --------- --------- --------- --------- --------- ---------
102 TX 196661 514403 6 0 440693 1
<<<<<< Blocking 112
102 TM 45172 0 2 0 440542 0
102 TM 45137 0 2 0 440693 0
112 TX 1114137 17942 6 0 439874 1
<<<<<<<< Blocking 121
112 TM 45137 0 3 0 439874 0
112 TM 45172 0 3 0 439874 0
112 TX 196661 514403 0 6 439874 0
121 TX 1179714 7865 6 0 438439 0
121 TM 45172 0 3 0 438439 0
121 TX 1114137 17942 0 4 438439 0
V$SQL
Don't know what's session 102 doing
SID SERIAL# OSUSER PIECE SQL_TEXT
--------- --------- ------------------------------ --------- ---------------
------------------------
121 11 erpapp2 0 INSERT INTO
PO_REQUISITION_LINES ( REQUISITION_LINE_ID,REQUISI
121 11 erpapp2 1
ON_HEADER_ID,LINE_NUM,LINE_TYPE_ID,CATEGORY_ID,ITEM_DESCRIPTIO
121 11 erpapp2 2
UNIT_MEAS_LOOKUP_CODE,UNIT_PRICE,QUANTITY,DELIVER_TO_LOCATION_
121 11 erpapp2 3
,TO_PERSON_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,SOURCE_TYPE_COD
121 11 erpapp2 4
LAST_UPDATE_LOGIN,CREATION_DATE,CREATED_BY,ITEM_ID,ITEM_REVISI
121 11 erpapp2 5
,QUANTITY_DELIVERED,SUGGESTED_BUYER_ID,ENCUMBERED_FLAG,RFQ_REQ
121 11 erpapp2 6
RED_FLAG,NEED_BY_DATE,LINE_LOCATION_ID,MODIFIED_BY_AGENT_FLAG,
121 11 erpapp2 7
RENT_REQ_LINE_ID,JUSTIFICATION,NOTE_TO_AGENT,NOTE_TO_RECEIVER,
121 11 erpapp2 8
RCHASING_AGENT_ID,DOCUMENT_TYPE_CODE,BLANKET_PO_HEADER_ID,BLAN
121 11 erpapp2 9
T_PO_LINE_NUM,CURRENCY_CODE,RATE_TYPE,RATE_DATE,RATE,CURRENCY_
121 11 erpapp2 10
IT_PRICE,SUGGESTED_VENDOR_NAME,SUGGESTED_VENDOR_LOCATION,SUGGE
121 11 erpapp2 11
ED_VENDOR_CONTACT,SUGGESTED_VENDOR_PHONE,SUGGESTED_VENDOR_PROD
121 11 erpapp2 12
T_CODE,UN_NUMBER_ID,HAZARD_CLASS_ID,MUST_USE_SUGG_VENDOR_FLAG,
121 11 erpapp2 13
FERENCE_NUM,ON_RFQ_FLAG,URGENT_FLAG,CANCEL_FLAG,SOURCE_ORGANIZ
121 11 erpapp2 14
ION_ID,SOURCE_SUBINVENTORY,DESTINATION_TYPE_CODE,DESTINATION_O
121 11 erpapp2 15
ANIZATION_ID,DESTINATION_SUBINVENTORY,QUANTITY_CANCELLED,CANCE
121 11 erpapp2 16
DATE,CANCEL_REASON,CLOSED_CODE,AGENT_RETURN_NOTE,CHANGED_AFTER
121 11 erpapp2 17
ESEARCH_FLAG,VENDOR_ID,VENDOR_SITE_ID,VENDOR_CONTACT_ID,RESEAR
121 11 erpapp2 18
_AGENT_ID,ON_LINE_FLAG,WIP_ENTITY_ID,WIP_LINE_ID,WIP_REPETITIV
121 11 erpapp2 19
SCHEDULE_ID,WIP_OPERATION_SEQ_NUM,WIP_RESOURCE_SEQ_NUM,ATTRIBU
121 11 erpapp2 20
_CATEGORY,DESTINATION_CONTEXT,INVENTORY_SOURCE_CONTEXT,VENDOR_
SID SERIAL# OSUSER PIECE SQL_TEXT
--------- --------- ------------------------------ --------- ---------------
------------------------
121 11 erpapp2 21
URCE_CONTEXT,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRI
121 11 erpapp2 22
TE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,AT
121 11 erpapp2 23
IBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,BOM_RE
121 11 erpapp2 24
URCE_ID,USSGL_TRANSACTION_CODE,GOVERNMENT_CONTEXT,CLOSED_REASO
121 11 erpapp2 25
CLOSED_DATE,TRANSACTION_REASON_CODE,QUANTITY_RECEIVED,TAX_CODE
121 11 erpapp2 26
D,TAX_USER_OVERRIDE_FLAG,OKE_CONTRACT_HEADER_ID,OKE_CONTRACT_V
121 11 erpapp2 27
SION_ID,SECONDARY_UNIT_OF_MEASURE,SECONDARY_QUANTITY,PREFERRED
121 11 erpapp2 28 RADE ) VALUES
( :b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11
121 11 erpapp2 29
b12,:b13,:b14,:b15,:b16,:b17,:b18,:b19,:b20,:b21,:b22,NVL(:b23
121 11 erpapp2 30
N'),:b24,:b25,:b26,:b27,:b28,:b29,:b30,:b31,:b32,:b33,:b34,:b3
121 11 erpapp2 31
:b36,:b37,:b38,:b39,:b40,:b41,:b42,:b43,:b44,:b45,:b46,:b47,:b
121 11 erpapp2 32
,:b49,:b50,:b51,:b52,:b53,:b54,:b55,:b56,:b57,:b58,:b59,:b60,:
121 11 erpapp2 33
1,:b62,:b63,:b64,:b65,:b66,:b67,:b68,:b69,:b70,:b71,:b72,:b73,
121 11 erpapp2 34
74,:b75,:b76,:b77,:b78,:b79,:b80,:b81,:b82,:b83,:b84,:b85,:b86
121 11 erpapp2 35
b87,:b88,:b89,:b90,:b91,:b92,:b93,:b94,:b95,:b96,:b97,:b98,:b9
121 11 erpapp2 36
:b100,:b101,:b102,:b103,:b104,:b105 )
37 rows selected.
SID SERIAL# OSUSER PIECE SQL_TEXT
--------- --------- ------------------------------ --------- ---------------
------------------------
112 179 erpapp2 0 UPDATE
PO_REQUISITION_HEADERS SET TRANSFERRED_TO_OE_FLAG=:b1 W
112 179 erpapp2 1 RE
REQUISITION_HEADER_ID = :b2
PO_REQUISITION_HEADERS is a view for PO_REQUISITION_HEADERS_ALL. Object
45137
And PO_REQUISITION_LINES is a view for PO_REQUISITION_LINES_ALL. Object
45172.
As far as I can see here,
Session 121 is executing an INSERT into PO_REQUISITION_LINES_ALL.
And it's taking:
TM - 3 on 45172 --
TX - 6 on 1114137
and it is also requesting a TX 4.
Session 112 is executing an UPDATE on PO_REQUISITION_HEADERS_ALL. Object
45137
And has:
TM- 3 on 45137
TM- 3 on 45172
<<<<<<<<<<Do not know why this is here
TX -6 on 1114137
and requires a TX - 6 on 196661
And finally session 102 is holding.
TM - 2 on 45172
TM - 2 on 45137
TX - 6 on 196661
So session 121 waits for 112, who is also waiting for 102. All on TX
enqueues.
There isn't any FK nor PK on these tables.
Do you know where can I go from here?
I don't remember what was the formula to derivate the RBS number and slot in
the transaction table from p1 and p2 TX enqueue........can you tell me?
(I am also going to take a processtate dump on 102 to see if I can see
what's that session doing.
I also thought on taking a enqueues dump but I'm not sure if this will be of
any help.)
Thank you very much.
Regards,
Diego.
----- Original Message -----
From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, May 03, 2004 11:53 AM
Subject: Re: Enqueue TX level 4 wait -- blocks dump
>
> Note in-line
>
> 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-l@xxxxxxxxxxxxx>
> Sent: Monday, May 03, 2004 10:56 PM
> Subject: Re: Enqueue TX level 4 wait -- blocks dump
> Let me tell you what I tried to do.
> For indexes for example, in many blocks all the ITLs had been used,
> wouldn't that mean that at some point in time there were 11 simultaneous
> transactions active? (counting also the recursive ones)
>
>
> [jl] No. Although indexes can be a little funny in their use of ITL
> [jl] entries, the basic principle is that a new transaction will use
> [jl] the itl entry that has the oldes commit scn in it. So this means
> [jl] that you will tend to see all 11 ITLs used.
>
> Index example.
> --------------------------------------------------------------------------
--
> --------------------------------
>
> Block header dump: 0x55802364
> Object id on Block? Y
> seg/obj: 0xb456 csc: 0x00.d86ff6c itc: 11 flg: - typ: 2 - INDEX
> fsl: 0 fnx: 0x0 ver: 0x01
>
> Itl Xid Uba Flag Lck Scn/Fsc
> 0x01 xid: 0x0002.012.00019480 uba: 0x10c0170e.240b.02 C--- 0
scn
> 0x00
> 00.06a5a457
> 0x02 xid: 0x0006.00d.000335f6 uba: 0x10c06d67.464b.0e --U- 1
fsc
> 0x00
> 00.0d96cc25
> 0x03 xid: 0x0001.031.00032a0e uba: 0x10c00b0f.456c.0a --U- 1
fsc
> 0x00
> 00.0d96cc29
> 0x04 xid: 0x0003.041.00033018 uba: 0x10c03b0c.44db.0c --U- 1
fsc
> 0x00
> 00.0d96cc3b
> 0x05 xid: 0x0003.002.00033015 uba: 0x10c03b0f.44db.19 --U- 1
fsc
> 0x00
> 00.0d96cde5
> 0x06 xid: 0x0003.039.00032c31 uba: 0x10c0cd44.447e.06 --U- 10
fsc
> 0x00
> 00.0d86ff6d
> 0x07 xid: 0x0003.021.00033019 uba: 0x10c03b0b.44db.0e --U- 1
fsc
> 0x00
> 00.0d96cc21
> 0x08 xid: 0x0007.026.000330af uba: 0x10c07ea9.43bd.2e --U- 1
fsc
> 0x00
> 00.0d8ed98e
> 0x09 xid: 0x0002.035.00031143 uba: 0x59401d01.4483.1b --U- 1
fsc
> 0x00
> 00.0d8ed992
> 0x0a xid: 0x0006.010.000335f7 uba: 0x10c06d66.464b.11 --U- 1
fsc
> 0x00
> 00.0d96cc19
> 0x0b xid: 0x0001.00d.00032a27 uba: 0x10c00b0e.456c.0d --U- 1
fsc
> 0x00
> 00.0d96cc1d
>
> Leaf block dump
> ===============
> header address 9223372041150438708=0x80000001000a9d34
> kdxcolev 0
> kdxcolok 0
> kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
> kdxconco 3
> kdxcosdc 1
> kdxconro 514
> kdxcofbo 1064=0x428
> kdxcofeo 1118=0x45e
> kdxcoavs 54
> kdxlespl 0
> kdxlende 0
> kdxlenxt 1434461029=0x55802365
> kdxleprv 1434456775=0x558012c7
> kdxledsz 0
> kdxlebksz 7800
>
> --------------------------------------------------------------------------
--
> --------------------------------
> There are some index blocks which have all the ITL slots used and some of
> them that don't. They have only 2 slots used.
> For the table, all ITLs have been used for almost all the table's blocks.
>
> [jl] There are some oddities with indexes and ITLs. It is possible
> [jl] that the blocks with only two slots used are branch blocks.
> [jl] There is also the oddity that when the first block created for the
> [jl] index (which is both a leaf and branch) splits, both the leaf blocks
> [jl] inherit the ITL count from the parent. Since the first block created
> [jl] is a branch block, it starts with only 2 ITL slots - whatever you
> [jl] specify for initrans.
>
> By checking the ITC value I was trying to find an ITC higher than 11, if I
> was lucky and found something like that,
> this would have meant that at some point in time there were more than 11
> transactions going on and that an additional
> ITL slot was needed and was allocated (as maxtrans and pctfree allowed
it).
> am I correct?
>
> [jl] Yes - but if itc was greater than 11, you would see more than 11
> [jl] slots in the ITL - slots do not get reclaimed.
>
> I really appreciate your comments.
> Thank you.
> Regards,
> 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
> -----------------------------------------------------------------
>
----------------------------------------------------------------
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:
