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: Sat, 04 May 2013 08:30:00 -0500

Had to put this in the backburner for a bit, but had already tired 400 for long 
and longc with no impact, and tried with 4000 after
your suggestion. The round trips remained at 534,073 for all the tests.

Copied the table to an DB sitting on a non-VM database server, 
SQL*Plus with the 4000 settings. I've made sure the table
was cached for all these tests to eliminate PIO differences (which I would be 
able to account for anyway since the would be recorded
in the trace). So, for both 10g VM and 11g non-vm, here's what we would see:

TRIPS:          534,073
ROWS:           300,700
LOBREADS:       233,700 (66,700 null nclobs)
LOBREAD TIME:   5.918873 seconds, median 24 microseconds, avg 25.3268 
microsecond, with a few outlier on the high side, and a
fastest of 23 microseconds.

Though the 10g trace doesn't record the LOBREADS, a 10051 trace would dump OPI 
calls, albeit without timing information.
Total time was 6 minutes (11g non-vm) versus 8 minutes (10g, heavily loaded 
Total db time "non-idle" time + unaccounted stayed right around 50 seconds for 
both environments, 46-48 for 11g and 50-52 for 10g

Though this is really starting to compare apples to oranges, the main things I 
was looking for were (1) to see how much accounted
for time would fall under LOBREAD, and (2) to see if the bulk of the time still 
falls on SQL*Net message from client, just in case
there was in issue in measurements around all the LOB activity in 10g. Lastly, 
there was some question if the VM layer was adding
much. If we had shown completion in 10% - 20% of original times, then this 
would warrant looking into deeper, though it still could
have been due to numerous other factors, not necessarily VM versus non-VM. 

Larry G. Elkins
Cell: 214.695.8605

> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
> Behalf Of Jonathan Lewis
> Sent: Thursday, May 02, 2013 9:25 AM
> To: 'Oracle-L'
> Subject: Re: LOB Operation and SQL*Net Message From Client and cursor #0
> Even worse - in some respects.
> You said the 358,953 trips amounted to roughly 73MB, which (allowing for
> overheads) is about 0.75 gigabits.
> On a 10Gb link that should a minimum of around 0.075 seconds (I know that 
> still leaves a lot of
> missing time to account for, but it does highlight the problem of 
> measurement.)
> Have you tried setting the SQL*Plus environment to optimize the test:
> set long 4000
> set longchunksize 4000
> This should reduce your roundtrips to 2 per row rather than 3.5 - it might be 
> interesting to see how
> varying longchunksize affects the reports you get from the monitor tool.
> Trying to identify the timing, my LOBREAD times were reported as ca. 25 
> microseconds when I enabled
> sql_trace, but anything from 160 to 1,200 microseconds when I started using 
> strace as well (which
> shows that the time includes some of the instrumentation time). I think the 
> LOBREAD time is likely to
> be true since extra lobreads (of an inline lob, at any rate) don't do extra 
> buffer gets etc. Any other
> time in the database is the effect of unloading and loading the network 
> buffer (plus instrumentation
> time).
> (And I've just realised that since you're on 10g you probably don't have 
> LOBREAD lines appearing in
> the trace file.)


Other related posts: