RE: LOB Operation and SQL*Net Message From Client and cursor #0
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "'Oracle-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 May 2013 07:20:21 -0400
Excellent point, JL!
Now, in another piece of the thread, the OP responded to a suggestion to
try putting the client on the database server as, in part:
" Can't get the client code there, but taking the SQL statement in question,
the one driving all the elapsed time, I run SQL*Plus on the server, both
through SQL*Net and bypassing SQL*Net, array size 100 like they do, and "set
autotrace trace stat" to discard the rows and not eat time rendering them on
the screen, and the processing time drops to 1/10 what it was within the
application, with the actual DB time about the same. Unaccounted for time
was also similar, though percentage wise constituted a much larger slice of
total time."
So, in the normal client operation, we can safely conclude that 90% of the
normal service time is manipulation of the data (receipt into client
buffers, rendering on the screen) on the client PLUS differences between
using the server located client and using the remote located client in the
time to stuff data in the communication buffers and send the data to the
client.
IF a version of the real remote client can be configured such that it
doesn't bother with rendering the LOB in any way, that should help a lot in
determining where 90% of the current service time resides.
IF, indeed, it is in the rendering and processing on the client, then both
the network monitor and the dbtime monitors are probably close enough to
correct and what needs to be fixed is the client rendering (not the network
or the database).
Sometimes, when the network guys say "It's not ME!" and the database guys
say "It's not ME!" and the storage guys say "It's not ME!" they are all
correct and the problem really is in the client processing software. (By the
way, this can also include client "think time" as that looks about the same
to network and db guys in a process progress diagram.)
Good luck Larry. You seem to be taking reasonable stepwise progress toward
identifying the problem.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Wednesday, May 01, 2013 6:08 AM
To: 'Oracle-L'
Subject: RE: LOB Operation and SQL*Net Message From Client and cursor #0
You may have covered this in an email I missed, but if you're testing from
SQL*Plus then
a) the arraysize is ignored if you actually fetch the LOB
b) if you set longchunksize to be less than long (the defaults are 80 each)
then you get multiple LOBREAD passes for each lob fetch, for example if you
set long 20000 and leave longchunksize to 80, and have LOBs of 4,000 bytes
you will see 51 (seems like one to many) calls to LOBREAD. The LOBREAD
calls can be very quick - which can lead to all sorts of unaccounted time
and granularity errors at all the different stages of monitoring. Possibly
the application has a similar pair of configuration options.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Larry Elkins [elkinsl@xxxxxxxxxxx]
Sent: 30 April 2013 23:10
To: 'Mark W. Farnham'; 'Oracle-L'
Subject: RE: LOB Operation and SQL*Net Message From Client and cursor #0
> So a lot of the time is spent on the line turn-around, and both tools
> count that latency against the other guy.
Correct, the guy using opnet is saying that turn from the app server layer
to the db and back is all on the DB, next to nothing on the network itself,
and not much with regards to client think time on the client. And of course
tracing is saying mostly sql*net message from client, the time to stuff the
data in the tcp buffer, go down the wire, client think time, and come back
up. And it's not my place to say his tool is wrong and 10046 is right ;-)
Maybe they both are right and it's in a virtualization layer that is hit
after the NIC. It's something we are considering, though I don't have the
skills or knowledge to dig into that, someone else will have to go there.
>
> Can you use a bigger packet?
> Can you try placing your client process directly on the dbserver?
Can't get the client code there, but taking the SQL statement in question,
the one driving all the elapsed time, I run SQL*Plus on the server, both
through SQL*Net and bypassing SQL*Net, array size 100 like they do, and "set
autotrace trace stat" to discard the rows and not eat time rendering them on
the screen, and the processing time drops to 1/10 what it was within the
application, with the actual DB time about the same. Unaccounted for time
was also similar, though percentage wise constituted a much larger slice of
total time.
When repeating with SQL*Plus on a desktop, the total timings came more in
line with what we see with the application, though the unaccounted for time
went up compared to SQL*Plus on the server. The fetch calls match up with
the array size, it's just those OPI level trips where we add a lot more
trips into the mix, and don't show up on the cursor's fetch count.
>
> 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.
Your comments did make me think about going back and looking at unaccounted
for time (something, maybe Tanel posting, about those OPI calls work not
being counted anywhere). And timing is the same without tracing.--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
