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

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <elkinsl@xxxxxxxxxxx>, "'Oracle-L'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 30 Apr 2013 09:24:20 -0400

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.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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 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



Other related posts: