Re: Block dump - Uncommitted TXN - Help Urgent

On 9/20/06, Bobak, Mark <Mark.Bobak_at_il.proquest.com> 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_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> wrote:
> > >
> > >
> > >
> > > On 9/19/06, Christian Antognini < Christian.Antognini_at_trivadis.com >
> > > 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
> > > > --
> > > > 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
>

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

--
http://www.freelists.org/webpage/oracle-l