In-line...
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, January 05, 2004 8:59 AM To: Multiple recipients of list ORACLE-L Thanks, Cary. Could you elaborate what do you mean by "wait events associated with COMMIT processing"? Why does Oracle need this "exchange of messages" with the client (well, with the app server really in my case of a 3-tier deployment) to perform a commit? [Cary Millsap] The event most often associated with COMMIT processing that is attributed to cursor #0 is 'log file sync'. In any event, as I described earlier in my case I think Cursor #0 doesn't fall in neither of the two uses you mentioned. Bug 2425312 is RPC related as I understand. I don't work distributed (single DB) and app server (and clients - thin) don't have their own SQL engine, so all SQL processing is happening strictly on the DB server. So this doesn't seem to apply to me. [Cary Millsap] Oracle Forms and one of Oracle's report writers (I forget the name) trigger this bug). And I see Cursor #0 used with no commits/rollbacks as part of one Oracle transaction. I see these WAIT #0 flying back and forth between DB and the app server sometimes 20 times just before stored procs are called and I can't figure out why. Another bug? [Cary Millsap] I'm very curious, too. I don't know the answer. Can you produce a minimal test case that reproduces the behavior? Thank you, Boris Dali. --- Cary Millsap <[EMAIL PROTECTED]> wrote: > Boris, > > Cursor #0 seems reserved for two special uses: (1) > wait events > associated with COMMIT processing (also, of course, > ROLLBACK and > SAVEPOINT), and (2) wait events associated with > dbcalls not instrumented > because of bug 2425312. > > > 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: Thursday, January 01, 2004 10:29 AM > To: Multiple recipients of list ORACLE-L > > 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 > === message truncated === ______________________________________________________________________ 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).
