RE: Block dump - Uncommitted TXN - Help Urgent

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • To: <bnsarma@xxxxxxxxx>, "goran bogdanovic" <goran00@xxxxxxxxx>
  • Date: Wed, 20 Sep 2006 12:13:30 -0400

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
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 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of BN
Sent: Wednesday, September 20, 2006 10:15 AM
To: goran bogdanovic
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Block dump - Uncommitted TXN - Help Urgent

On 9/20/06, goran bogdanovic <goran00@xxxxxxxxx> wrote: 

        The inittrans should correspond to your max active concurrent
dml sessions on that table
        On 9/20/06, BN < bnsarma@xxxxxxxxx <mailto:bnsarma@xxxxxxxxx> >

                On 9/19/06, Christian Antognini <
<mailto:Christian.Antognini@xxxxxxxxxxxx> > wrote: 

                        > 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
                        Here an example of sessions waiting for a
transaction slot:
                        SQL> SELECT event FROM v$session_wait WHERE sid
= 12;
                        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). 

                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


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

Regards & Thanks

Other related posts: