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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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 =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 -- //www.freelists.org/webpage/oracle-l