RE: Help on analyzing extended SQL trace for "SQL*Net message" waits

Thanks Cary!! I loved your book :-)

I have a hard time trying to figure out though what would be the optimal execution plan though.

Do I just start looking at other available access paths? What if this turns out to be the most optimal plan for my physical layout? Do I start considering other indexes, etc...

Thanks for the input, I'd like to see how the gurus approach it...

-----Original Message-----
From: Cary Millsap [mailto:Cary.Millsap_at_hotsos.com]=20 Sent: Thursday, May 12, 2005 1:44 PM
To: Khemmanivanh, Somckit; 'Oracle-L Freelists' Subject: RE: Help on analyzing extended SQL trace for "SQL*Net message" waits

Start tuning the code. You do not have a network performance issue here.

You're just spending a lot of time and effort (cr+cu) fetching 5 rows at a
time. Probably an inefficient execution plan, as you've said. All the ela
values for 'SQL*Net message from client' calls are in the 1000-microsecond-or-less range (< 0.001000 seconds), which is no problem at
all.

(By the way, the Oracle kernel doesn't ship blocks via SQL*Net to the client; it ships rows.)

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Visit www.hotsos.com for curriculum and schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Khemmanivanh, Somckit
Sent: Thursday, May 12, 2005 2:16 PM
To: Oracle-L Freelists
Subject: Help on analyzing extended SQL trace for "SQL*Net message" waits

Hi,

My database is 9205 on HP-UX 11.11. I was looking at a query that was taking a long time to run today.

My guess from the trace is that, the Oracle server process is reading 1 block and shipping it over SQL*Net to the client (which is very inefficient for data transfer). This is custom code for a batch job. The batch job seems to be stuck processing this table 1 block at a time...

Here's the execution plan (I have an optimizer trace also, if you'd like to see it):

Execution Plan

 SELECT STATEMENT ( Estimated Costs =3D3D 1,231 , Estimated #Rows =3D3D = 2 )

        INLIST ITERATOR             TABLE ACCESS BY INDEX ROWID BSAD

                INDEX RANGE SCAN BSAD~1

Is this a analysis correct? How would you proceed? Start tuning the code? Is there a SLOW network issue here?

Thanks much!!!

Here's some of the trace output:

EXEC =3D
#218:c=3D3D0,e=3D3D170,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D=
3D0,og=3D3D4,tim=3D
3D=3D
2741509950677
WAIT #218: nam=3D3D'SQL*Net message to client' ela=3D3D 4 = p1=3D3D675562835 =3D
p2=3D3D1 p3=3D3D0
FETCH
#218:c=3D3D4710000,e=3D3D4624500,p=3D3D0,cr=3D3D17030,cu=3D3D0,mis=3D3D0,=
r=3D3D5,dep=3D3
D=3D
0,og=3D3D4,tim=3D3D2741
514575382
WAIT #218: nam=3D3D'SQL*Net message from client' ela=3D3D 869 = p1=3D3D675562835
=3D
p2=3D3D1
p3=3D3D0
EXEC =3D
#218:c=3D3D0,e=3D3D133,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D=
3D0,og=3D3D4,tim=3D
3D=3D
2741514576639
WAIT #218: nam=3D3D'SQL*Net message to client' ela=3D3D 3 = p1=3D3D675562835 =3D
p2=3D3D1 p3=3D3D0
WAIT #218: nam=3D3D'db file sequential read' ela=3D3D 9476 p1=3D3D265 = =3D
p2=3D3D8914 p3=3D3D1
FETCH
#218:c=3D3D4720000,e=3D3D4631872,p=3D3D1,cr=3D3D17031,cu=3D3D0,mis=3D3D0,=
r=3D3D5,dep=3D3
D=3D
0,og=3D3D4,tim=3D3D2741
519208547
WAIT #218: nam=3D3D'SQL*Net message from client' ela=3D3D 921 = p1=3D3D675562835
=3D
p2=3D3D1
p3=3D3D0
EXEC =3D
#218:c=3D3D0,e=3D3D177,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D=
3D0,og=3D3D4,tim=3D
3D=3D
2741519209948
WAIT #218: nam=3D3D'db file sequential read' ela=3D3D 469 p1=3D3D48 =3D p2=3D3D111586 p3=3D3D1
WAIT #218: nam=3D3D'SQL*Net message to client' ela=3D3D 4 = p1=3D3D675562835 =3D
p2=3D3D1 p3=3D3D0
FETCH
#218:c=3D3D4730000,e=3D3D4628436,p=3D3D1,cr=3D3D17031,cu=3D3D0,mis=3D3D0,=
r=3D3D5,dep=3D3
D=3D
0,og=3D3D4,tim=3D3D2741
523838427
WAIT #218: nam=3D3D'SQL*Net message from client' ela=3D3D 1625 =3D p1=3D3D675562835 p2=3D3D1
p3=3D3D0
EXEC =3D
#218:c=3D3D0,e=3D3D118,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D=
3D0,og=3D3D4,tim=3D
3D=3D
2741523840405
WAIT #218: nam=3D3D'SQL*Net message to client' ela=3D3D 6 = p1=3D3D675562835 =3D
p2=3D3D1 p3=3D3D0
WAIT #218: nam=3D3D'db file sequential read' ela=3D3D 534 p1=3D3D48 =3D p2=3D3D111033 p3=3D3D1
FETCH
#218:c=3D3D4670000,e=3D3D4574333,p=3D3D1,cr=3D3D17032,cu=3D3D0,mis=3D3D0,=
r=3D3D5,dep=3D3
D=3D
0,og=3D3D4,tim=3D3D2741
528414780
WAIT #218: nam=3D3D'SQL*Net message from client' ela=3D3D 972 = p1=3D3D675562835
=3D
p2=3D3D1
p3=3D3D0
EXEC =3D
#218:c=3D3D0,e=3D3D193,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D=
3D0,og=3D3D4,tim=3D
3D=3D
2741528416234
WAIT #218: nam=3D3D'SQL*Net message to client' ela=3D3D 6 = p1=3D3D675562835 =3D
p2=3D3D1 p3=3D3D0
FETCH
#218:c=3D3D4600000,e=3D3D4502623,p=3D3D0,cr=3D3D17030,cu=3D3D0,mis=3D3D0,=
r=3D3D5,dep=3D3
D=3D
0,og=3D3D4,tim=3D3D2741
532918903
WAIT #218: nam=3D3D'SQL*Net message from client' ela=3D3D 856 = p1=3D3D675562835
=3D
p2=3D3D1
p3=3D3D0
EXEC =3D
#218:c=3D3D0,e=3D3D241,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D=
3D0,og=3D3D4,tim=3D
3D=3D
2741532920293
WAIT #218: nam=3D3D'SQL*Net message to client' ela=3D3D 3 = p1=3D3D675562835 =3D
p2=3D3D1 p3=3D3D0
FETCH
#218:c=3D3D4640000,e=3D3D4545241,p=3D3D0,cr=3D3D17033,cu=3D3D0,mis=3D3D0,=
r=3D3D5,dep=3D3
D=3D
0,og=3D3D4,tim=3D3D2741
537465763
WAIT #218: nam=3D3D'SQL*Net message from client' ela=3D3D 890 = p1=3D3D675562835
=3D
p2=3D3D1
p3=3D3D0
EXEC =3D
#218:c=3D3D0,e=3D3D90,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D3=
D0,og=3D3D4,tim=3D3
D=3D
2741537466975
WAIT #218: nam=3D3D'SQL*Net message to client' ela=3D3D 3 = p1=3D3D675562835 =3D
p2=3D3D1 p3=3D3D0
FETCH
#218:c=3D3D4620000,e=3D3D4518006,p=3D3D0,cr=3D3D17030,cu=3D3D0,mis=3D3D0,=
r=3D3D5,dep=3D3
D=3D
0,og=3D3D4,tim=3D3D2741
541985016
WAIT #218: nam=3D3D'SQL*Net message from client' ela=3D3D 1294 =3D p1=3D3D675562835 p2=3D3D1
p3=3D3D0
EXEC =3D
#218:c=3D3D0,e=3D3D139,p=3D3D0,cr=3D3D0,cu=3D3D0,mis=3D3D0,r=3D3D0,dep=3D=
3D0,og=3D3D4,tim=3D
3D=3D
2741541986713

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l