Re: TX Enqueue | what to do?

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:b85ut7$mc5$1$8300dec7_at_news.demon.co.uk...
>
> There are several causes of TX enqueue waits.
> The ITL issue that you describe comes about
> no. 4 in order of probability on the list, and is
> a fairly exotic occurrence.
>
> The commonest cause is that you literally
> have two sessions trying to lock the same row.
>
> Following on from that is the possibility that
> you have some foreign key indexes missing
> that you happen to need to support the
> DML activity (particularly full row updates,
> or deletes at the parent end of a pk/fk)
> on your application.
>
> There are other reasons, but ITL contention
> is a bit unlikely, and a quick and dirty fix for
> that is simply to set the INITRANS on the relevant
> objects to be larger than the number of concurrent
> transactions that could reasonably be expected
> to hit one block at any one time.
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
> ____UK_______April 22nd
> ____USA_(FL)_May 2nd
> ____Denmark__May 21-23rd
> ____Sweden___June
> ____Finland__September
> ____Norway___September
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK_(Manchester)_May x 2
> ____Estonia___June (provisional)
> ____Australia_June (provisional)
> ____USA_(CA, TX)_August
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> "Kenneth A Kauffman" <kkauffman_at_nospam.headfog.com> wrote in message
> news:zwupa.344300$0g4.9333286_at_news2.east.cox.net...
> > My understanding of TX Enqueue is that it is a transaction level
> wait state.
> > Meaning, that within an 8k block, there may be several rows of data.
> When
> > one of those rows within the block is marked as locked for an oracle
> > transaction, other rows are subsequently blocked from access. This
> is the
> > condition of a TX Enqueue wait state. Additional understanding
> leads me to
> > believe that if I have this symptom occur frequently, I would need
> to adjust
> > my block size to something smaller to avoid transaction contention.
> Since
> > Oracle 9i supports multiple block sizes at a table level, this is
> not a big
> > deal to do a rebuild targeting the tables in question.
> >
> > Here is the quesitons:
> >
> > 1) Would Oracle partitioning help me in this case? It would seem to
> me less
> > likely if its adjacent data within the same block.
> >
> > 2) Is redesigning the application to use optomistic locking my only
> other
> > choice? There is a lot of code time involved in doing this and also
> doesn't
> > guarantee the integrity as well as pessimistic locking.
> >
> > 3) How do I target which blocks are the "offenders" in this
> condition?
> >
> > I see TX Enqueues as my top wait event within a set of statspack
> reports
> > during a LoadRunner test. If anyone has any further clarification on
> this
> > point or if any of my understanding is inaccurate, please do tell.
> :)
> >
> > ken k
> >
> >
>
>

Thank you sir. Your help is appreciated.

ken k Received on Wed Apr 23 2003 - 10:05:44 CDT