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

From: Khemmanivanh, Somckit <somckit.khemmanivanh_at_weyerhaeuser.com>
Date: Thu, 12 May 2005 12:15:57 -0700
Message-ID: <65C0D8935651CB4D96E97CEFAC5A12B90A5BA4@wafedixm10.corp.weyer.pri>

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 =3D 1,231 , Estimated #Rows =3D 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 =
#218:c=3D0,e=3D170,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D0,og=3D4,tim=3D=
2741509950677
WAIT #218: nam=3D'SQL*Net message to client' ela=3D 4 p1=3D675562835 = p2=3D1 p3=3D0
FETCH
#218:c=3D4710000,e=3D4624500,p=3D0,cr=3D17030,cu=3D0,mis=3D0,r=3D5,dep=3D=
0,og=3D4,tim=3D2741
514575382
WAIT #218: nam=3D'SQL*Net message from client' ela=3D 869 p1=3D675562835 = p2=3D1
p3=3D0
EXEC =
#218:c=3D0,e=3D133,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D0,og=3D4,tim=3D=
2741514576639
WAIT #218: nam=3D'SQL*Net message to client' ela=3D 3 p1=3D675562835 = p2=3D1 p3=3D0
WAIT #218: nam=3D'db file sequential read' ela=3D 9476 p1=3D265 = p2=3D8914 p3=3D1
FETCH
#218:c=3D4720000,e=3D4631872,p=3D1,cr=3D17031,cu=3D0,mis=3D0,r=3D5,dep=3D=
0,og=3D4,tim=3D2741
519208547
WAIT #218: nam=3D'SQL*Net message from client' ela=3D 921 p1=3D675562835 = p2=3D1
p3=3D0
EXEC =
#218:c=3D0,e=3D177,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D0,og=3D4,tim=3D=
2741519209948

WAIT #218: nam=3D'db file sequential read' ela=3D 469 p1=3D48 =
p2=3D111586 p3=3D1
WAIT #218: nam=3D'SQL*Net message to client' ela=3D 4 p1=3D675562835 =

p2=3D1 p3=3D0
FETCH
#218:c=3D4730000,e=3D4628436,p=3D1,cr=3D17031,cu=3D0,mis=3D0,r=3D5,dep=3D=
0,og=3D4,tim=3D2741
523838427
WAIT #218: nam=3D'SQL*Net message from client' ela=3D 1625 = p1=3D675562835 p2=3D1
p3=3D0
EXEC =
#218:c=3D0,e=3D118,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D0,og=3D4,tim=3D=
2741523840405
WAIT #218: nam=3D'SQL*Net message to client' ela=3D 6 p1=3D675562835 = p2=3D1 p3=3D0
WAIT #218: nam=3D'db file sequential read' ela=3D 534 p1=3D48 = p2=3D111033 p3=3D1
FETCH
#218:c=3D4670000,e=3D4574333,p=3D1,cr=3D17032,cu=3D0,mis=3D0,r=3D5,dep=3D=
0,og=3D4,tim=3D2741
528414780
WAIT #218: nam=3D'SQL*Net message from client' ela=3D 972 p1=3D675562835 = p2=3D1
p3=3D0
EXEC =
#218:c=3D0,e=3D193,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D0,og=3D4,tim=3D=
2741528416234
WAIT #218: nam=3D'SQL*Net message to client' ela=3D 6 p1=3D675562835 = p2=3D1 p3=3D0
FETCH
#218:c=3D4600000,e=3D4502623,p=3D0,cr=3D17030,cu=3D0,mis=3D0,r=3D5,dep=3D=
0,og=3D4,tim=3D2741
532918903
WAIT #218: nam=3D'SQL*Net message from client' ela=3D 856 p1=3D675562835 = p2=3D1
p3=3D0
EXEC =
#218:c=3D0,e=3D241,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D0,og=3D4,tim=3D=
2741532920293
WAIT #218: nam=3D'SQL*Net message to client' ela=3D 3 p1=3D675562835 = p2=3D1 p3=3D0
FETCH
#218:c=3D4640000,e=3D4545241,p=3D0,cr=3D17033,cu=3D0,mis=3D0,r=3D5,dep=3D=
0,og=3D4,tim=3D2741
537465763
WAIT #218: nam=3D'SQL*Net message from client' ela=3D 890 p1=3D675562835 = p2=3D1
p3=3D0
EXEC =
#218:c=3D0,e=3D90,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D0,og=3D4,tim=3D=
2741537466975
WAIT #218: nam=3D'SQL*Net message to client' ela=3D 3 p1=3D675562835 = p2=3D1 p3=3D0
FETCH
#218:c=3D4620000,e=3D4518006,p=3D0,cr=3D17030,cu=3D0,mis=3D0,r=3D5,dep=3D=
0,og=3D4,tim=3D2741
541985016
WAIT #218: nam=3D'SQL*Net message from client' ela=3D 1294 = p1=3D675562835 p2=3D1
p3=3D0
EXEC =
#218:c=3D0,e=3D139,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D0,og=3D4,tim=3D=
2741541986713

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

Received on Thu May 12 2005 - 15:20:33 CDT