No, Each session will have its own sid and serail#, but they all run in the same process. Basically the client side tells oracle, that it wants to switch from session to session and oracle will keep the state of the switched out session. So you don't have to commit or rollback on every switch that you perform. SQL trace is inherited by the process it you set in a session, so other sessions that run in the same process will produce also trace output.
Anjo. -----Original Message----- Boris Dali Sent: Monday, January 05, 2004 7:34 PM To: Multiple recipients of list ORACLE-L Thanks, Anjo. When session switching occurs does the new session get the same sid and serial#? And what happens with the session being "switched/replaced" - does the transaction it was performing get commited/rollbacked? I don't see XCTEND markers before those pesky WAIT #0 in the trace file. Also if session gets switched, wouldn't this terminate sql trace for the session (in my case it doesn't)? Thanks, Boris Dali. --- Anjo Kolk <[EMAIL PROTECTED]> wrote: > > > Cursor 0 also happens in oracle due to session > switching (multiple > sessions in the same process), oracle apps uses that > but it also could > happen with certain other application servers > (haven't investigated it). > > Anjo. > > > -----Original Message----- > Boris Dali > Sent: Monday, January 05, 2004 3:59 PM > 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? > > > 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. > > 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? > > 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: > > > > > > .... > === 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: Anjo Kolk 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).
