IN follow-up of a performance issue, we received a statspack report,
which has "enqueue" as the FIRST top-5 wait event

Time duration : 2 hours
Enqueue : 62,000 / WaitTime ( CS )15,000,000 / Total % 74%

Similar situation for a similar DB for anotehr application for another
client.

Referring to :
http://otn.oracle.com/oramag/oracle/...o13expert.html
initrans and maxtrans is set to 1 and 255 respectively.

Any suggestions on how the wait-time on enqueue be reduced to minimal
or negligible.

Your responses are appreciated.

Thanks,
JK

If your sessions are waiting on the Enqueue event, then they are waiting
for locks to be released before they can continue. So you'll have to
revisit the application and see if you can reduce the lock contention.

HTH,
Brian

JK_Yahoo wrote:
--
================================================== =================

Brian Peasland
oracle_dba (AT) remove_spam (DOT) peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
the three"

"Brian Peasland" <oracle_dba (AT) remove_spam (DOT) peasland.com> wrote

That or possibly depending on how your applications load and subsequently
manipulate data, you may have insufficient transaction slots due to your
initrans settings.

You don't use bitmap indexes by any chance ....

Cheers

Richard

"Richard Foote" <richard.foote (AT) bigpond (DOT) com> wrote

Ooops, forgot to mention try tracing an affected session (10046 level 8/12
event) and suz out what specifically it's waiting on ...

Richard

Richard Foote wrote:

Looking for a good source I can point my students to with respect to tracing.
Something more tutorial with step-by-step than the standard otn documents.

Any suggestions will be appreciated.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

In the two hours windows, your wait time on enqueue was
15,000,000CS=150,000 seconds, almost 20 times the window's length.
It's very likely that you have classic blocking/locking issues--I have
got the rows you want to have already locked. You can query V$lock to
see who the blockers are and what they are doing and who the blockees
are and what they are trying to do. After you know the reason you
might fix it by rewriting the application code.

HTH,
http://www.webyourcompany.com

JKMalpani (AT) Yahoo (DOT) com (JK_Yahoo) wrote in message news:<7a58b2f6.0306241007.5608970b (AT) posting (DOT) google.com>...

"Norman Dunbar" <Norman.Dunbar (AT) lfs (DOT) co.uk> wrote



--
Niall Litchfield
Oracle DBA
Audit Commission UK

"Daniel Morgan" <damorgan (AT) exxesolutions (DOT) com> wrote

Hi Daniel,

Metalink has a number of notes with regarding to tracing to get you started.
These include:

Note: 21154.1 Event 10046 enable SQL statement tracing
Note: 39817.1 Interpreting RAW 10046 output

There are also some excellent articles by Cary Millsap and Jeff Holt on
their Hotsos site (www.hotsos.com)

Steve Adams as usual has lots on good stuff on tracing (www.ixora.com.au)

Then there's what's in my head but I fear your students might be traumatised
by what else they might find in there ...

Cheers

Richard

Thanks Niall.

-----Original Message-----
From: Niall Litchfield [mailto:n-litchfield (AT) audit-commission (DOT) gov.uk]
Posted At: Thursday, June 26, 2003 1:26 PM
Posted To: server
Conversation: Statspack and Enqueue wait times
Subject: Re: Tracing Tutorial for Daniel


Chapter 10. DBMS_PROFILER is also covered in the same chapter.

Norman Dunbar wrote:

Thanks. And I have a copy of the book. But it is copywrited and I can
legally, or ethically, photocopy for students and with WROX out of business
can't put it on the textbook list.

That is why I'm looking for a web source.

Once again ... thanks.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)