They write all to the same trace file. So there should be different
sid.serial# combinations.
-----Original Message-----
Boris Dali
Sent: Monday, January 05, 2004 9:49 PM
To: Multiple recipients of list ORACLE-L
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 trace.
..Or am I missing something?
- Anjo Kolk <anjo_at_oraperf.com> wrote: > 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 <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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Boris Dali
INET: boris_dali_at_yahoo.ca
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Anjo Kolk
INET: anjo_at_oraperf.com
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 - 19:14:25 CST
