RE: Event 10046 trace

From: Nelson, Allan <anelson_at_miswaco.com>
Date: Thu, 17 Jun 2004 08:02:49 -0500
Message-ID: <7E0DB3A126BA9146AC30744E2B7E42451118EF@midhouhqxch01.net.smith.com>

The waits you highlighted will belong to the first dbcall after these waits. Assuming you started the tracing by connecting from a third session to a session in progress you probbly missed the PARSING IN CURSOR section for CURSOR#0. Without that you are going to have a hard time figuring out the waits.
You need to intiate tracing at the start of the session. Perhaps doing the alter session calls in an after logon trigger if you can't execute the session from sqlplus or you can't id the sid and serial# combination before the code sequence you are trying to trace starts.

Cary Millsap's "Optimizing Oracle Performance" has a good detailed discussion of how to deal with these kinds of issues.

Allan
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Madhu Konda Sent: Thursday, June 17, 2004 7:48 AM
To: oracle-l_at_freelists.org
Subject: Event 10046 trace

Hi All,

We are running Oralce 8.1.7. on HP-UX . Recently I traced a application (third party) and I see some the following information in the trace file .

PARSING IN CURSOR #6 len=181 dep=0 uid=8564 oct=6 lid=8564 tim=1314794115 hv=1049137217 ad='712a38b0' UPDATE SIEBEL.S_EVT_ACT SET

      LAST_UPD_BY = :1,
      MODIFICATION_NUM = :2,
      APPT_START_DT = :3,
      LAST_UPD = :4
   WHERE
      ROW_ID = :5 AND MODIFICATION_NUM = :6

END OF STMT

PARSE #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=1314794115
EXEC #6:c=0,e=1,p=0,cr=4,cu=27,mis=0,r=1,dep=0,og=3,tim=1314794116
WAIT #6: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0

WAIT #6: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0

EXEC #9:c=0,e=0,p=0,cr=0,cu=4,mis=0,r=1,dep=0,og=3,tim=1314794116
WAIT #9: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #9: nam='SQL*Net message from client' ela= 1 p1=1413697536 p2=1

p3=0
XCTEND rlbk=0, rd_onfiltered=0
WAIT #0: nam='log file sync' ela= 0 p1=170 p2=0 p3=0 WAIT #0: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 *** 2004-06-10 13:55:28.184
WAIT #0: nam='SQL*Net message from client' ela= 36301 p1=1413697536 p2=1 p3=0

>From the trace output I can see that Parse#6,Fetch#6,WAIT#6 are
attributed to Curosr #6 . But I am not able to figure out what event is invoking the WAIT#9 and WAIT # 0 . Also I am not able to figure out what event is causing this huge wait (363.01 secs) on SQL*Net message from client .

Can somebody please explain me whats going on here.

TIA,
Madhu

Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.

This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216]

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jun 17 2004 - 08:00:12 CDT