Re: sql trace - forward attribution

Oracle Portal uses session switching as well (and Apps 11i uses Portal...)

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, January 05, 2004 7:49 PM

>
>
> 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
> > > >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: 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: Cary Millsap
> > > INET: cary.millsap_at_hotsos.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
> >
> === 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).
>

-- 
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).