Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: sql trace - forward attribution
Trace file has server process number in it's name, not session number, thus
as long as the sessions are served by the same server process, the contents
will be written into one single file.
Tanel.
> Right, but the new session (that inherits the sql
> trace attribute) - wouldn't it produce a **separate**
> trace file? In my case there's only one trace file
> with sid.serial# clearly stated at the begining of the
> trace file and WAIT #0 scattered all over the t
race....OramImissingsomething---AnjoKolkanjo_at_oraperf.zl6wroteNo,Eachsessionw
illhaveitsownsidandserail#,buttheyallruninthesameprocess.Basicallytheclients
idetellsoracle,thatitwantstoswitchfromsessiontosession 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 <anjo_at_oraperf.com> 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 <cary.millsap_at_hotsos.com> 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 <cary.millsap_at_hotsos.com>
> > wrote:
> > > >
> > > > >....
> > > > > >WAIT #31: nam='SQL*Net message to client'
> > ela=
> > > 1
> > > > > p1=1413697536 p2=1 p3=0
> >
> === message truncated ===
>> Post your free ad now! http://personals.yahoo.ca
> ______________________________________________________________________
>> To REMOVE yourself from this mailing list, send an E-Mail message
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: tanel.poder.003_at_mail.ee 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: ListGuru_at_fatcity.com (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).Received on Mon Jan 05 2004 - 15:09:25 CST
![]() |
![]() |