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 06:39:54 -0500

On point A you are referring to just the LOB itself, correct? Because the "r" 
value on the FETCH for the "real" cursor show array
fetches, just that we then see a lot of back on forth on the LOB after that. My 
basic (mis)understanding was array fetches were not
used at all, the LOB made things single row fetches. But it seems from the 
trace, and with the 10051, array fetches are used for the
non-LOB columns, then going back and getting the LOB in all those small ops. 
I'll investigate B, and do some more testing as well.

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 5:08 AM
> To: 'Oracle-L'
> Subject: RE: LOB Operation and SQL*Net Message From Client and cursor #0
> 
> 
> You may have covered this in an email I missed, but if you're testing from 
> SQL*Plus then
> 
> a) the arraysize is ignored if you actually fetch the LOB
> 
> b) if you set longchunksize to be less than long (the defaults are 80 each) 
> then you get multiple
> LOBREAD passes for each lob fetch, for example if you set long 20000 and 
> leave longchunksize to 80,
> and have LOBs of 4,000 bytes you will see 51 (seems like one to many) calls 
> to LOBREAD.  The LOBREAD
> calls can be very quick - which can lead to all sorts of unaccounted time and 
> granularity errors at
> all the different stages of monitoring. Possibly the application has a 
> similar pair of configuration
> options.

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


Other related posts: