Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Block dump - Uncommitted TXN - Help Urgent
Note that if you see a TX enqueue waiting on mode 4, that's not
necessarily ITL slot shortage. If the waiting statement is an INSERT
and the object is a heap table, then it's definitely not ITL shortage.
(If there's ITL shortage on a table block, INSERT will go to another
block on the free list, if it's on the index, INSERT will do a block
split.)
The other cause TX enqueue mode 4 waits is concurrent inserts colliding on a unique index, PK, or UK. (i.e. session 1 inserts key value 1, session 2 inserts value 2, session 1 attempts to insert value 2, session 2 attempts to insert value 1)
Hope that helps,
-Mark
-- Mark J. Bobak Senior Oracle Architect ProQuest Information & Learning "A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly. Specialization is for insects." --Robert A. Heinlein ________________________________ From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of BN Sent: Wednesday, September 20, 2006 10:15 AM To: goran bogdanovic Cc: oracle-l_at_freelists.org Subject: Re: Block dump - Uncommitted TXN - Help Urgent On 9/20/06, goran bogdanovic <goran00_at_gmail.com> wrote: Hi, The inittrans should correspond to your max active concurrent dml sessions on that table goran On 9/20/06, BN < bnsarma_at_gmail.com <mailto:bnsarma_at_gmail.com> > wrote: On 9/19/06, Christian Antognini < Christian.Antognini_at_trivadis.com <mailto:Christian.Antognini_at_trivadis.com> > wrote:Received on Wed Sep 20 2006 - 11:13:30 CDT
> I was thinking of INITRANS , is there a way to
track that from
> v$lock,
> I remember once Steve Adams answering some
body looking at v$lock
> looking at Request Column, not sure though
Hi Here an example of sessions waiting for a transaction slot: SQL> SELECT event FROM v$session_wait WHERE sid = 12; EVENT ----------- enqueue SQL> SELECT type, id1, id2, lmode, request FROM v$lock WHERE sid = 12; TY ID1 ID2 LMODE REQUEST -- ---------- ---------- ---------- ---------- TM 34522 0 3 0 TX 393247 9877 0 4 As you can see the request mode is S (4). HTH Chris -- http://www.freelists.org/webpage/oracle-l Greetings, 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 -- Regards & Thanks BN Greetings Yes, I understand, is there a view in the Datadictionary to check how much each table has reached, other than tracking/sampling v$lock or v$transaction -- Regards & Thanks BN -- http://www.freelists.org/webpage/oracle-l
![]() |
![]() |