RE: LOB Operation and SQL*Net Message From Client and cursor #0
Date: Tue, 30 Apr 2013 17:52:25 -0500
Message-id: <002c01ce45f5$622f3c90$268db5b0$_at_net>
I don't know if the opnet tool others have used to measure the different tiers and network layer can get real detailed since another group handles that aspect. But it was clear from the 10051 the 100's of thousands, a bit over a million I think, round trips, were nearly all on cursor 0 and tied back to LOB operations.
This is a vendor application. The LOB is an NCLOB for a column name COMMENTS. So I can see where the vendor could want to exceed 4000 characters, and thus go the LOB route. But the funny part is in discussions with the power user doing the test runs for us to diagnose things, the front end limits the field to 255 characters. And there is only value that size, most are much smaller.
So maybe there is a switch of some kind in the application configuration, in this particular installation, limiting it to 255 characters, or maybe the vendor is looking forward to one day having it unlimited. But based on the front end not allowing more than 255, and no values exceeding that, much less 4000 characters, an NCLOB isn't really needed, at least for how it is being used in this particular instance. I'm sure the vendor has good reason(s) for the NCLOB, I could certainly see someone making that choice if they don't want constraints, eventually, on the size of the COMMENTS. Just have to be aware of how LOBS add a whole new dimension to things.
Doing a test, converting the column to a char compared to an SQL statement leaving it as a LOB, it completes very quickly as all the special handling for a LOB, fetching it, etc, is no longer needed. And I believe C++ is the language used. Don't know much about it. I was asked to look into the DB side the other day to see if they are bottlenecking there, and if so, if there was anything we could do.
Larry G. Elkins
elkinsl_at_verizon.net
Cell: 214.695.8605
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sriram Kumar
> Sent: Tuesday, April 30, 2013 10:01 AM
> To: elkinsl_at_verizon.net
> Cc: Oracle-L
> Subject: Re: LOB Operation and SQL*Net Message From Client and cursor #0
>
> Hi,
> Whats the typical size of the LOBs and what is the client tier (Java?).
> have you used wireshark or a sniffing tool to see how the typical LOB segment is shipped to DB server?
>
> Best Regards
>
> Sriram
-- http://www.freelists.org/webpage/oracle-lReceived on Wed May 01 2013 - 00:52:25 CEST