Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Table open times and rowsize discontinuity
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:
>>>>> 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) )
(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)
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Mon Aug 13 2007 - 10:14:24 CDT