RE: sql trace - forward attribution

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 01 Jan 2004 16:29:24 -0800
Message-ID: <F001.005DB750.20040101162924@fatcity.com>

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

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
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 Thu Jan 01 2004 - 18:29:24 CST