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

From: D'Hooge Freek <Freek.DHooge_at_uptime.be>
Date: Tue, 30 Apr 2013 21:19:09 +0200
Message-ID: <1367349549.3107.63.camel_at_dhoogfr-lpt1>

Larry,
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).

regards,

-- 
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(03) 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer.html




On di, 2013-04-30 at 03:22 +0200, Larry Elkins wrote:


> 10.2.0.5 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_at_verizon.net

> 

> 

> 

> --

> http://www.freelists.org/webpage/oracle-l

> 

> 

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

Received on Tue Apr 30 2013 - 21:19:09 CEST