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: Thu, 02 May 2013 06:49:50 -0500

I was unclear in the original email. The .001353 was *total* time the tool 
reported for network transfer, 358,963 trips. 

For this particular test, duration 211 seconds, no PIO's recorded for the 
query, less than .5 seconds CPU, 180 seconds message from
client, and 30 seconds unaccounted for time (mostly LOB related?). I have a 
tool that can help with the unaccounted for time and
help with your suggestions on timestamps. 

The opnet tool attributed 14 seconds to application time (SQL*Plus), .001353 
network, and the rest DB, so basically 197 seconds
attributed to the DB. From the 10046 if we take the 30 seconds unaccounted plus 
.5 CPU that is still far short of the 197 seconds
opnet is putting on the DB. And I'll have to ask the infrastructure folks about 
the point Mark made regarding the turns themselves.

The main thing I was going for was the question if there are any known 
instrumentation issues such as LOB operations, for example,
getting attributed to the wrong event, such as message from client, and if 
there were other known accounting issues with LOBS. From
some readings, and comments in this thread, it seems some (maybe much) of the 
LOB activity could fall into the unaccounted bucket.
Haven't come across anything yet regarding LOB activity getting attributed to 
the wrong event.

If I make any other progress in resolving the discrepancy I'll get back to the 

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 1:22 AM
> To: 'Oracle-L'
> Subject: Re: LOB Operation and SQL*Net Message From Client and cursor #0
> Questioning the tool is a good idea.
> Looking at the trace your turnaround between packets on cursor# 0 is in the 
> order of 350 microseconds
> - which is 0.00035, which is nearly 4 times faster than the tool is 
> reporting.  You might calibrate
> the tim= figures by running the query for at least 30 minutes so you get a 
> couple of date stamps in
> the trace that you can use to check that the time change from the date stamps 
> agrees with the tim= in
> microseconds. I don't think you've described the network - but I think it 
> would have to be 10Mb to be
> slow enough to give you an average of .001353 s for a couple of hundred bytes.
> The difference between the application and the SQL*Plus array processing may 
> be an OCI choice in
> SQL*Plus, it may simply be a choice to ignore the arraysize when there are 
> LOBs involved -
> interestingly there is a "lob attribute" column (which seems to be about 
> client temporary lobs) in
> v$session_connect_info, you might compare what you see there for an 
> application connection and an
> SQL*Plus connection.


Other related posts: