Help on analyzing extended SQL trace for "SQL*Net message" waits
- From: "Khemmanivanh, Somckit" <somckit.khemmanivanh@xxxxxxxxxxxxxxxx>
- To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 May 2005 12:15:57 -0700
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
