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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 30 Apr 2013 09:24:20 -0400
Message-ID: <030601ce45a6$06b2c570$14185050$_at_rsiz.com>

So a lot of the time is spent on the line turn-around, and both tools count that latency against the other guy.

Can you use a bigger packet?
Can you try placing your client process directly on the dbserver?

One last thing: Usually tracing on is a minor side effect, but if there is a throughput challenge on your trace/output directory and your only wait is a long stream of message waits, that could add up to significance. So carefully compare the times traced and untraced to rule this out.

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Larry Elkins
Sent: Monday, April 29, 2013 9:23 PM
To: 'Oracle-L'
Subject: LOB Operation and SQL*Net Message From Client and cursor #0

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 - 15:24:20 CEST