Performance Conundrum Selecting varchar2(2000) Column

From: Sam Bootsma <SamB_at_cpas.com>
Date: Tue, 29 Jan 2002 15:34:42 -0800
Message-ID: <F001.003FFC0C.20020129153244@fatcity.com>

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). Received on Tue Jan 29 2002 - 17:34:42 CST