Thanks a lot for your reply, Cary. One follow-up question. What would motivate "a chat" of sometimes 5, sometimes 10-20 'SQL*Net message to/from client' consecutive wait lines emitted to the trace file in the following manner:
WAIT #0: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message from client' ela= 678 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message from client' ela= 3463 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #0: nam='SQL*Net message from client' ela= 3322 p1=1413697536 p2=1 p3=0 .... I see this pattern of "message exchanges" before calling a stored code from the app server (OCI), so using forward attribution it is a call to a stored code that it to blame correct? I can't of course eliminate a call to a stored code but is there something that can be done to minimize amount of these 'SQL*Net message...' lines? While the latency of these waits is low, these 3-5 milliseconds get accumulated slowly, but surely. Also does cursor #0 has some special meaning in traces? I can't seem to create a test-case where I get cursor #0 emitted for me and yet tracing real applications I see it all over (like in the excerpt above) I guess I have more than one follow-up question :-( Thanks, Boris Dali. --- Cary Millsap <[EMAIL PROTECTED]> wrote: > >.... > >WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > >WAIT #31: nam='SQL*Net message from client' ela= > 692 p1=1413697536 p2=1 > p3=0 > >WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 > p3=0 >FETCH > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > >WAIT #31: nam='SQL*Net message from client' ela= > 2295 p1=1413697536 > p2=1 p3=0 > >.... > > Boris, "SQL*Net message..." events are > "between-call" events. Their > times are not included in the following dbcall's > elapsed time. But it > *is* appropriate to "blame" the dbcall that follows > for the time > consumed by the event. That is, if you can eliminate > the dbcall that > follows, then you can eliminate the between-call > event (and its elapsed > time). The "assignment of blame" is what "forward > attribution" is about. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 1/27 Atlanta > - SQL Optimization 101: 2/16 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -----Original Message----- > Boris Dali > Sent: Monday, December 29, 2003 9:39 AM > To: Multiple recipients of list ORACLE-L > > I don't have the book with me right now, but I am > obviously missing something in the "forward > attribution" concept as it doesn't seem to help me > in > explanation of the following lines: > > .... > WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > WAIT #31: nam='SQL*Net message from client' ela= 692 > p1=1413697536 p2=1 p3=0 > WAIT #31: nam='SQL*Net message to client' ela= 1 > p1=1413697536 p2=1 p3=0 > FETCH > #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 > WAIT #31: nam='SQL*Net message from client' ela= > 2295 > p1=1413697536 p2=1 p3=0 > .... > > Shouldn't 1 + 692 + 1 (and possibly + 2295 ???) be > less than 261? > > Oracle 9.2.0.4.0 on HP-UX 11.11 > > Thanks, > Boris Dali. > > ______________________________________________________________________ > > Post your free ad now! http://personals.yahoo.ca > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Boris Dali > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Cary Millsap > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). ______________________________________________________________________ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).