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

  • From: D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
  • To: "elkinsl@xxxxxxxxxxx" <elkinsl@xxxxxxxxxxx>
  • Date: Tue, 30 Apr 2013 21:19:09 +0200

sql*net from client can also point to processing or think time on the
client / application server and not only time spend on the network.

One of the problems I have seen with lobs is that they are always
returned row by row instead of returning an array of them causing the
network latency to play a very important role. Also I have noticed that
some connection libraries (odbc if I recall correctly) will first
retrieve the length of the lob before retrieving the actual data (adding
another roundtrip to it).


Freek D'Hooge
Oracle Database Administrator
email: freek.dhooge@xxxxxxxxx
tel +32(03) 451 23 82

On di, 2013-04-30 at 03:22 +0200, Larry Elkins wrote:
> EE on AIX 6.1
> Any known instrumentation issues / oddities when fetching LOBS?
> Tracing a process, 10046 level 8, 91% of the time on sql*net message from 
> client, with 99.8% of that accumulated under cursor #0.
> Retraced with event 10051 turned on, indicated they were OPI call type 96, 
> Lob/FILE operations. 
> So, simple enough, a little over a million calls back on forth on those OPI 
> level calls to get the LOB data. Precious little DB
> activity. 
> But, techs with opnet installed and gathering data in different tiers swear 
> there is little time on the network between the app
> server and the unix host where the database resides, that the time is *in* 
> the DB. He's measuring nic to nic and acknowledgments
> between the app server and db server host, at least that's the way he 
> described it. DB host is a VM. 
> So first step is to make sure there isn't some sort of instrumentation bug in 
> Oracle related to LOBS, and that the time I'm showing
> on sql*net message from client is indeed just that, and not something like 
> "uncounted" CPU. And if it is indeed sql*net message from
> client, then we can start to look at where that time he shows is "in the 
> database" actually is on that host.
> Larry G. Elkins
> elkinsl@xxxxxxxxxxx
> --
> //

Other related posts: