Re: ORACLE ON A WAN

From: Roman Starek <starek_at_pluto.spsselib.hiedu.cz>
Date: 15 Jun 2001 16:34:14 GMT
Message-ID: <9gdde6$dkn$1@crax.cesnet.cz>

andy <news_at_info-line.it> wrote:
> We are using Oracle database installed on a NT machine, the version is
> 7.3.4.
> The clients are connected to the db Oracle by a large band connection
> (about 1 Mb). They use TCP-IP protocol over the network.
> Programs are builted in Delphi 2 and need BDE (Borland Database
> Engine) version 5.1.1. In the BDE interface the parameter NET PROTOCOL
> is set to TNS.

> We have analyzed traffic networks on the WAN and there is a lot of
> small packets (request of client and respond of the server).
> Performance of Delphi programs are not so good. Performance in a LAN
> are very good, obviously.

> The question is: in Oracle can i control network packets size? What
> about SDU, listener Queue size and TCP.NODELAY?

> Thanks in advance.

> Andy

Here is some answers from experts_exchange.com

There is much to study. Here is a quick summary. There are 3 types of connections that are relevant (with TNSNAMES.ORA examples):

TCP/IP - the usual stuff that all the yucky GUI assistants create for you, that works fine on winduhs clients, but creates loopback tcp traffic when used on the same box. You can look at netstat -a and watch the packet count on lo0 to see if this is happening to you. Important point to note, default SDU/TDU=2048 which splits/shortens every other packet on typical SDU=1500 ethernet; change SDU to 1500 or multiple of 1500 such as 9000 to avoid this discrepancy.

myhost_orcl.world=
(DESCRIPTION=(SDU=9000)(TDU=9000)
(ADDRESS = (PROTOCOL = TCP)(HOST=myhost)(PORT=1521)) (CONNECT_DATA=(SID=ORCL))
)

IPC - Inter Process Communications is supposed to work better than TCP loopback connection.

mysid_ipc.world =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = IPCKEY1)) (CONNECT_DATA = (SID = ORCL))
)

BEQ - Bequeath Protocol. The best way performance wise.

Beq-local.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =

  (PROTOCOL = BEQ)
  (PROGRAM = oracle80)
  (ARGV0 = oracle80ORCL)
  (ARGS =

'(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))' )
)
)
(CONNECT_DATA = (SID = ORCL))
)

Now my question is... are you saying SQL*NET is taking 70-80% of the CPU, or 70-80% of the elapsed time? On my bstat/estat reports I usually see SQL*NET communications as one of the largest time consumers but...

Communications between tasks (via whatever method) is usually idle wait time, fairly insignificant CPU wise, but only significant in terms of wall clock time, so there isn't much benefit to the system to tune idle waits... it doesn't free up resources for other tasks.

It would help to have some examples of the time breakdown for the program you are running. How long does it run, and how much time exactly is consumed by SQL*NET and how did you determine that?

ANd next one

All of above is true. I would like to add more issues.

Another topic to keep on mind is to use array interface in OCI apps. Wrong setting cause heavy load in SQLNet connections due many roundtrips fro every SELECT statement. Some applications give you possibility of changing that parameter to desired values.

Next topic is to control network layer. It sould be done by network admin especially in largerer network enviroments. This is issue if another net apps are working well.

Starous Received on Fri Jun 15 2001 - 11:34:14 CDT