Re: Block dump - Uncommitted TXN - Help Urgent

  • From: BN <bnsarma@xxxxxxxxx>
  • To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • Date: Wed, 20 Sep 2006 13:03:35 -0400

On 9/20/06, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxxxxx> wrote:


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@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:
>
> Hi,
>
> The inittrans should correspond to your max active concurrent dml
> sessions on that table
>
> goran
>
>
> On 9/20/06, BN < bnsarma@xxxxxxxxx> wrote:
> >
> >
> >
> > On 9/19/06, Christian Antognini < 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
> > >
> > > 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
> > > --
> > > //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



Greetings
Mark, Thank you.

Right, I read that from the Metalink note: 62354.1

ST Enque and  Request=4 is  what I have noticed.



--
Regards & Thanks
BN

Other related posts: