Re: Performance Conundrum Selecting varchar2(2000) Column

From: <Peter.McLarty_at_mincom.com>
Date: Tue, 29 Jan 2002 19:58:37 -0800
Message-ID: <F001.003FFE73.20020129194518@fatcity.com>

This is almost certainly a Network related issue. The network is misconfigured for "your application", I repeat "your application". This is not that your application is bad in any way, its just that their network doesn't like it.
What I suspect is happening is that you may be doing is crossing routers or other devices that are not being friendly to your packets. A good network engineer is able to locate and correct this for you. It could be fragmenting or a network issue that your application is exposing because it uses the network differently than previous applications the client has or is using.

Cheers

Peter McLarty               E-mail: Peter.Mclarty_at_mincom.com
Technical Consultant        WWW: http://www.mincom.com
APAC Technical Services     Phone: +61 (0)7 3303 3461
Brisbane,  Australia        Mobile: +61 (0)402 094 238
                            Facsimile: +61 (0)7 3303 3048
=================================================

A great pleasure in life is doing what people say you cannot do.

Walter Bagehot (1826-1877 British Economist)
Mincom "The People, The Experience, The Vision"

Sam Bootsma <SamB_at_cpas.com>
Sent by: root_at_fatcity.com
30/01/2002 09:32 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Fax to: 
        Subject:        Performance Conundrum Selecting varchar2(2000) Column

Hello,

Most of our clients can run our application with very good performance, on both UNIX and Windows platforms. However, ...

One of our clients has installed our application on a HP 11 UNIX box and is
encountering intermittent, but severe slowdowns in certain areas of our application. We took a laptop to our client site, plugged it into their network, and after some investigation were able to narrow down the slow performance to a single varchar2(2000) column of one table. We performed additional tests as follows:

Within our application, we queried the table, EXCLUDING the varchar2(2000) column. Response was very fast.
Within our application, we queried ONLY the varchar2(2000) column. Response was very slow.
Within our application, we queried ONLY the first 12 characters of the varchar2(2000) column. Response was very fast.
Within our application, we queried the entire varchar2(2000) column, but only a few rows. Response was very fast.
From SqlPlus, we queried the entire table. Response time was very fast.
Our client tested loading Oracle 817 on a NT box, and running the same application, the performance was good.
When we reduced array fetching to a lower number, the individual delays were shorter, but total delay was as long or longer.

Additional information on our application:

1. Runs on Oracle 81723
2. Uses OCI calls.  No ODBC. 
3. Uses array fetching (512 rows). 
4. In the past, we had tested our application on a HP UNIX box (in-house),

and performance was good.

Could the problem have to do with the way the OS is configured to bundle packets together???? Maybe the packet sizes are configured very small???? I am stabbing at possible reasons. Any suggestions on where the source of the problem may lie is appreciated.

Thank-you!

Sam Bootsma, OCP
Technical Support Analyst
CPAS Systems Inc.
samb_at_cpas.com
http://www.cpas.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Sam Bootsma
INET: SamB_at_cpas.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Peter.McLarty_at_mincom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message