Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Table open times and rowsize discontinuity

Re: Table open times and rowsize discontinuity

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 13 Aug 2007 08:14:24 -0700
Message-ID: <1187018063.941793@bubbleator.drizzle.com>


dean wrote:

> On Aug 9, 11:28 am, DA Morgan <damor..._at_psoug.org> wrote:
>> sybra..._at_hccnet.nl wrote:
>>> On Wed, 08 Aug 2007 14:34:38 -0700, dean <deanbrow..._at_yahoo.com>
>>> wrote:
>>>> On Aug 8, 2:27 pm, DA Morgan <damor..._at_psoug.org> wrote:
>>>>> dean wrote:

>>>>>> Hello all,
>>>>>> We have a table T with the following structure:
>>>>>> Name Null? Type
>>>>>> ----------------------------- -------- --------------------
>>>>>> USER_I NOT NULL VARCHAR2(30)
>>>>>> FORM_I NOT NULL VARCHAR2(100)
>>>>>> CATEGORY_I NOT NULL VARCHAR2(100)
>>>>>> KEY_I NOT NULL VARCHAR2(100)
>>>>>> VALUE_X VARCHAR2(2000)
>>>>>> By varying the VALUE_X field (varchar2(N)) from N=100 to N=2000,
>>>>>> however, we can alter the time it takes to open this table either onto
>>>>>> a grid or to scroll through all records. We are using Borland's
>>>>>> TADOQuery here. Oracle is 9.2i or 10g, windows thick client over a
>>>>>> LAN.
>>>>>> All data was the same, however, since the longest actual VALUE_X field
>>>>>> was onyl 27 characters.
>>>>>> Times to open a TdbGrid on user_preference table:
>>>>>> VALUE_X field: Time to open:
>>>>>> varchar2(100): 10.44s
>>>>>> varchar2(200): 10.75s
>>>>>> varchar2(300): 10.91s
>>>>>> varchar2(400): 10.93s
>>>>>> varchar2(500): 11.21s
>>>>>> varchar2(600): 11.15s
>>>>>> varchar2(700): 28.44s <- steps up here
>>>>>> varchar2(800): 26.84s
>>>>>> varchar2(900): 26.59s
>>>>>> varchar2(1000): 26.36s
>>>>>> varchar2(1100): 26.78s
>>>>>> varchar2(1200): 26.42s
>>>>>> varchar2(1300): 26.78s
>>>>>> varchar2(1400): 28.27s
>>>>>> varchar2(1500): 28.96s
>>>>>> varchar2(1600): 28.34s
>>>>>> varchar2(1700): 27.64s
>>>>>> varchar2(1800): 28.5s
>>>>>> varchar2(1900): 28.13s
>>>>>> varchar2(2000): 28.36s
>>>>>> Why does this time step up at N = 700 characters? I have narrowed it
>>>>>> down to the ADO component, since an alternative component (ODAC) does
>>>>>> not report ANY differences, irrespective of the size of the field. SQL
>>>>>> + also reports the same number of bytes transfered and times to open,
>>>>>> irrespective of N.
>>>>>> Similar effect was noted in an alternative table (one with around 30
>>>>>> fields) at N = 600, so I am assuming the total rowsize is the property
>>>>>> to consider.
>>>>>> Thanks for any hint. We are considering removing such fields and using
>>>>>> a lookup table in such cases, since the data is sparsely populated.
>>>>>> Dean
>>>>> Look at array size and cache size settings. My guess is that it is in
>>>>> some manner related to ADO or the tool you are using (Delphi ?). It
>>>>> isn't something experienced in the database. Something you can confirm
>>>>> by running in SQL*Plus.
>>>>> Anything you ever see in a tool, that you don't see in SQL*Plus, IS
>>>>> the tool.
>>>>> --
>>>>> Daniel A. Morgan
>>>>> University of Washington
>>>>> damor..._at_x.washington.edu (replace x with u to respond)
>>>>> Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>>>>> - Show quoted text -
>>>> This is using a serverside cursor, so caching won't have any effect.
>>>> For the array fetching, the only parameter I can find in the ADO
>>>> documentation is to append FetchSize=N to the connection string that
>>>> gets passed to Oracle, but I am not sure if ADO is over-riding this,
>>>> since it has no effect.
>>> As soon as the number of records fetched times the record size exceeds
>>> sqlnet's SDU and/or your network's MTU, you will potentially see
>>> remarkable slowdowns.
>>> Just increasing the array fetch size to the ceiling doesn't help.
>>> Many years ago I conducted an investigation for an online banking
>>> system.
>>> It appeared that as soon as I increased sql*plus array size to
>>> anything above 10, performance collapsed. This was the net result of
>>> exceeding the SDU and the MTU.
>> Interesting. I normally set SDU on Oracle systems to 32K, as recommended
>> in the HA docs, rather than using the default 2K. Perhaps that is why I
>> haven't seen it.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -
> 
> I'd like to investigate this - are these settings on the Oracle
> server, or some part of the TCI/IP setup?

They can be in listener.ora or sqlnet.ora depending on how you define the need. For example:

  SID_LIST_DG_LISTENER =
   (SID_LIST =

     (SID_DESC =
       (SDU=32767)
       (GLOBAL_DBNAME = proda)
       (ORACLE_HOME = /app/oracle/product/10.1.0/db_1)
       (SID_NAME = proda)
     )

   ) LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = omega1.psoug.org)(PORT = 1521))
     )
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
     )

   )

or in sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) DEFAULT_SDU_SIZE=32767
TCP_NODELAY=YES I also typically add this as well:

     (SEND_BUF_SIZE=9375000)
     (RECV_BUF_SIZE=9375000)

in the listener.ora as it is also recommended by the MAA docs.
-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Aug 13 2007 - 10:14:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US