Re: Event 10046 trace

From: Madhu Konda <konda_madhusudhan_at_yahoo.com>
Date: Thu, 17 Jun 2004 11:19:04 -0700 (PDT)
Message-ID: <20040617181904.62003.qmail@web50610.mail.yahoo.com>

Hi ,
Thanks much for your time and taking a look into this . I attched the trace file for your reference . From the trace file I have prepared the following resource profile .

WaitTotal(secs)db file sequential read 8.7direct path read 0direct path write 0file open 0.08latch free 2.84log file sync 0.33SQL*Net message from client 932.29SQL*Net message to client 0.01SQL*Net more data from client 3.15SQL*Net more data to client 0.47(blank) Grand Total947.87

This tells me that there is some kind of network delay , but I am not able to figure out which sql statements are cuaing this slowness. Also this is the tracing of siebel handheld synching session via landline phone conection . Please take a look into the trace file and let me know your findings.

Thanks,
Madhu

"Danisment Gazi Unal (ubTools)" <dunal_at_ubTools.com> wrote: Hi,

According to my observation, WAIT#0 is related to implicit commits in local and/or remote databases.

For example,

If you INSERT a row in SQL*Plus, then exit without COMMITing, you will not see a COMMIT statement in the trace, but you'll see XCTEND line and WAIT#0 lines. Because, SQL*Plus will autoCOMMIT. In that case, WAIT#0 is related XCTEND.

If you make a Remote Procedure Call(RPC), you will see WAIT#0 calls for RPCs, too.

I'm not sure if it's documented in Oracle docs., but there is no guarantee that if a kernel call(PARSE,FETCH,EXEC,WAIT) N is related to the first previous cursor N in the trace file. In fact, it may be related to the first subsequent cursor N. To understand that, you should check PARSE call of kernel call N. If there is no PARSE call in the trace, it means statement is PARSEd before you enabled the trace. Since it's not possible for the different statements to have same cursor number at the same time, and then if there is no PARSE call of cursor N, it means kernel call N is related to subsequent cursor N.

If you email me your event10046 trace file, I can analyze and email you the results.

best regards...

http://www.ubTools.com
Web Based Oracle Products and Services

Original Message ----- From: Madhu Konda To: oracle-l_at_freelists.org Sent: Thursday, June 17, 2004 3:47 PM 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.

Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.

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
-----------------------------------------------------------------



application/x-zip-compressed attachment: ora_19241_seblp101.zip

Received on Thu Jun 17 2004 - 13:24:32 CDT