| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: sql trace - forward attribution
I actually build a testcase for this and it still failed on 9.2 without
any patches. It is supposed to be fixed in some later patch. I don't
have the patches
-----Original Message-----
Anjo Kolk
Sent: Monday, January 05, 2004 6:49 PM
To: Multiple recipients of list ORACLE-L
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.
> 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.
> >
> >
>
-- 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: 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 - 13:49:26 CST
 |
 |