RE: LOB Operation and SQL*Net Message From Client and cursor #0
- From: "Larry Elkins" <elkinsl@xxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, "'Oracle-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 01 May 2013 20:18:49 -0500
Jonathan,
Double-checked again, and indeed the SQL*Plus client is doing R=1. Working too
many things at the same time and was going from
memory, so I forwarded some details to myself today. When I bypassed the LOB,
then yes, array fetches. Thanks for sanity checking
that for me, that is what I had thought I would normally see.
For the vendor application, we see the array fetch, but it is 43, not 100 as I
stated before. Here's an example showing the #0 waits
around the fetch from the actual cursor, from a trace without the 10051 event:
WAIT #0: nam='SQL*Net message from client' ela= 319 driver id=1413697536
#bytes=1 p3=0 obj#=-1 tim=40419361239085
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1
p3=0 obj#=-1 tim=40419361239118
WAIT #0: nam='SQL*Net message from client' ela= 267 driver id=1413697536
#bytes=1 p3=0 obj#=-1 tim=40419361239406
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1
p3=0 obj#=-1 tim=40419361239435
WAIT #0: nam='SQL*Net message from client' ela= 291 driver id=1413697536
#bytes=1 p3=0 obj#=-1 tim=40419361239754
WAIT #6: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1
p3=0 obj#=-1 tim=40419361239834
WAIT #6: nam='SQL*Net more data to client' ela= 15 driver id=1413697536
#bytes=2019 p3=0 obj#=-1 tim=40419361239903
WAIT #6: nam='SQL*Net more data to client' ela= 11 driver id=1413697536
#bytes=2005 p3=0 obj#=-1 tim=40419361239960
WAIT #6: nam='SQL*Net more data to client' ela= 6 driver id=1413697536
#bytes=2000 p3=0 obj#=-1 tim=40419361240012
WAIT #6: nam='SQL*Net more data to client' ela= 7 driver id=1413697536
#bytes=2000 p3=0 obj#=-1 tim=40419361240070
FETCH #6:c=0,e=326,p=0,cr=4,cu=0,mis=0,r=43,dep=0,og=4,tim=40419361240108
WAIT #6: nam='SQL*Net message from client' ela= 903 driver id=1413697536
#bytes=1 p3=0 obj#=-1 tim=40419361241045
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1
p3=0 obj#=-1 tim=40419361241079
WAIT #0: nam='SQL*Net message from client' ela= 273 driver id=1413697536
#bytes=1 p3=0 obj#=-1 tim=40419361241373
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1
p3=0 obj#=-1 tim=40419361241402
WAIT #0: nam='SQL*Net message from client' ela= 314 driver id=1413697536
#bytes=1 p3=0 obj#=-1 tim=40419361241743
With regards to Mark's comment about getting things as close together as
possible, I verified today the app server box is in the
same computer room as the database server box. The app server component is
running within a VM on that box.
And Freek, the LOB is defined as inline, and the largest is well below the
threshold that would cause it to migrate out of line
(using DBMS_LOB.GETLENGTH to check size).
Didn't get to do much testing on it today, but I worked with the infrastructure
guy and an opnet (AppTransactionXpert) trace for my
SQL*Plus session from my desktop, even though we see some differences in how it
fetches, still trying to better understand how
things are being counted by our tools. We limited the number of rows compared
to the real test, 100,000 or so. We still showed in
the opnet trace 358,963 trip, 73 million total bytes, showing avg application
message of 204 bytes and average network packets of
258 bytes. The thing that caught my interest is the tool says 0 for latency
effect, and 0.001353 seconds on network transfer. Not
being a network person, I would have expected a bit more than 0.001353, but
they are standing by that number with regards to time on
the network.
Larry G. Elkins
elkinsl@xxxxxxxxxxx
Cell: 214.695.8605
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Jonathan Lewis
> Sent: Wednesday, May 01, 2013 7:04 AM
> To: Larry Elkins; 'Oracle-L'
> Subject: RE: LOB Operation and SQL*Net Message From Client and cursor #0
>
>
>
> Larry,
>
> I've only got 11.2.0.2 in front of me at the moment (64-bit, Linux), and
> "select non_lob_col, LOB from
> test" shows r=1 for every fetch even though I've set the arraysize to 13, and
> can see r=13 when I do
> "select non_lob_col from test".
>
>
> Regards
> Jonathan Lewis
--
//www.freelists.org/webpage/oracle-l
Other related posts:
