| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help on analyzing extended SQL trace for "SQL*Net message" waits
Take a look at Dan Tow's "Tuning SQL." The answer you need is in there.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Visit www.hotsos.com for curriculum and schedule details...
-----Original Message-----
From: Khemmanivanh, Somckit [mailto:somckit.khemmanivanh_at_weyerhaeuser.com]
Sent: Thursday, May 12, 2005 4:56 PM
To: Cary Millsap; Oracle-L Freelists
Subject: 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]
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 =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=3
D=
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=3
D=
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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu May 12 2005 - 18:11:40 CDT
 |
 |