RE: LOB Operation and SQL*Net Message From Client and cursor #0

From: Larry Elkins <elkinsl_at_verizon.net>
Date: Wed, 01 May 2013 06:39:54 -0500
Message-id: <000601ce4660$994b3290$cbe197b0$_at_net>



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_at_verizon.net
Cell: 214.695.8605

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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.

--
http://www.freelists.org/webpage/oracle-l
Received on Wed May 01 2013 - 13:39:54 CEST

Original text of this message