| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Slow listener connections in Windows NT
Tuning SQL*Net is sometimes necessary to speed up applications running over
a network.
This Note describes what can be done from a SQL*Net and an Application
perspective.
The most important thing to note here is that most common networking
performance
issues are not SQL*Net related, but more about how the Application is
written and
how the network infrastructure is set up. This note will touch on some
non-Oracle-related issues later.
Tuning SQL*Net revolves around the concept of decreasing the number of
packets we
put on to the network. This can be done in two ways:
You can speed up an application on the network by optimizing the amount of
records
the application fetches at once. In order to show how we can speed them
up, sniffing
technology has been used to illustrate these facts.
In this example SQLPLUS is the application and a SELECT * FROM BIGEMP is
issued.
The BIGEMP table consists of 4928 rows.
In SQLPLUS the amount of records fetched at once is set using ARRAYSIZE.
If the default ARRAYSIZE of 15 in SQLPLUS is used, and the SELECT is
issued, the
sniffer trace generated shows that 256 packets are sent and received
between client and server. This SELECT took 16 seconds.
Now if the ARRAYSIZE in SQLPLUS is set to 49
SQLPLUS> SET ARRAYSIZE 49 and the SELECT is issued, the sniffer trace shows that 154 packets were put on the network, taking 13 seconds.
So by changing the ARRAYSIZE, performance has improved by 3 seconds, by reducing the amount of packets on the network by 102.
To explain why this happens I'll use a fun example. Take for example, you
have to
send 100 people on a bus from A to B. If the bus holds 20 people and 20
people get
on the bus at a time, you will have to make 5 trips. If only 10 people get
on the
bus and the bus leaves with 10 seats free, then we have to make 10 trips,
which will
take longer. So the more free space you leave the more trips you have to
do and the
time taken to get everyone from A to B increases.
In applying this to TCP, it is important to understand how TCP/IP works.
By default
the maximum amount of actual 'data', when you have finished with headers
etc., that
you can send in a TCP packet is 1460 bytes. In our example when ARRAYSIZE
is set
to the default 15, the sniffer shows that we ask for 15 rows at a time and
in nearly
all cases you waste around 500 bytes of free space in each packet. If
these free
500 bytes can be utilized, we can fill this TCP packet and overall fewer
packets
will be sent and received. In the first case the application asks for 15
rows, so
we do a fetch and get one packet of data back with 15 rows in and 500
bytes of empty
space, then we do another fetch and get another packet of 15 rows of data
with another
500 bytes of free space, etc. When the ARRAYSIZE is set to 49, we ask for
49 rows
and in this case, we fill the first TCP packet fully, and then immediately
get another
data packet back with the rest of the rows in, and this almost fills the
second data packet up.
So if you can tune an application by setting a more appropriate ARRAY
fetch size
then it is possible to increase the applications performance. Setting the
appropriate
ARRAY fetch size is determined by trial and error and by using a sniffer.
The above example is appropriate for DML and DDL.
In many cases changing the ARRAY fetch size is not an option because many
applications
are not developed in-house and are provided by a 3rd party vendor. A high
percentage
of increasing the performance of applications on a network depends on how
well it
is has been written to perform in conjunction with the protocol that sits
underneath it.
2.Setting the SDU size
From a SQL*Net perspective, the only thing you can tune is the SDU size.
SDU stands
for Session Data Unit. SDU is a buffer at the Network Session (NS) layer
from which
SQL*Net packets are passed via TDU (Transport Data Unit) at the Network
Transport
(NT) layer, to the underlying protocol. By default in version 2.3, SDU is
set to 4K. It changes to 2K in Net*8.
Below is a real life example where a customer's application performance was improved by tuning the SDU size.
A company had a large Global Information System written in OCI, where
information
was initially stored in flat files and then passed via the OCI application
into Oracle
8.0.4. The application read the flat file in fixed chunks and then
inserted them
via Net*8 into the database. The data was read from the flat files in 4K
blocks,
but when passed via OCI over Net*8 the amount of data passed over the line
equated to 2900 bytes.
With the default SDU size of 2K, each block of data was handled as follows:
OCI App ----> Net*8 2900 bytes passed
NS ----> NT 2048 bytes (as we are using a 2K
SDU size)
TCP Layer 1460 bytes - packet 1
588 bytes - packet 2
NS ----> NT 852 bytes
TCP Layer 852 bytes - packet 3
If we set SDU to 4K, each block will be handled as follows:
OCI App ----> Net*8 2900 bytes passed
NS ----> NT 2900 bytes (as we are using 4K SDU
size)
TCP Layer 1460 bytes - packet 1
1450 bytes - packet 2
So for each 4K block the OCI application passes to Net*8, we send 2 TCP
packets instead
of 3 by changing the SDU size. Ignoring Acknowledgment packets (Ace's),
given that
each flat file was 30MB in size, there would be 7680 X 4K blocks of data
to send.
With SDU at 2K we send 23040 packets of data. With SDU set at 4K we send
15360 packets, a saving of 7680 data packets.
It could be argued that the same amount of data is being passed over the
wire, and
it should take the same amount of time given a fixed bandwidth. However
each packet
has Data Link Control (DLC), TCP, and IP header information which take
extra bandwidth.
For example 7680 extra packets will use 405K of extra header data.
Secondly, each
packet will take a finite amount of time to pass through the Network
Interface Card
(NIC), routers etc, all of which will add time to the overall transaction.
Also,
if we do include Ace's in the equation, packets on the network are lower
with SDU set to 4K.
To understand this it is necessary to understand how Ace's work. In the
above example,
if we use 2K SDU size then you can see we send 3 data packets. The way TCP
works
is that if we send a data packet and if that is the end of transmission,
we will
get an ACK back. If we send some data and it fills two packets, an ACK
will be sent
on receipt of 2 consecutive TCP segments. If we send 3 data packets and
the transmission
ends, we get an ACK for the first two data packets and an ACK for the
third packet.
Therefore with an SDU of 2K we will send 3 data packets and therefore get
2 ACK packets per 2900 bytes of data.
With an SDU of 4K we only send 2 packets per 2900 bytes therefore only
send 1 ACK.
So with an SDU of 2K we send 15360 Ace's and with an SDU of 4K we will see
7680 ACK packets, a saving of 7680 ACK packets.
So as you can see by setting SDU appropriately you can have a marked improvement in network and application performance.
To set SDU size, you have to edit your TNSNAMES.ORA and also the LISTENER.ORA on the server you want to connect to, as follows:
#TNSNAMES.ORA
ukp9930.world =
(DESCRIPTION =
(SDU=4096) <----------- SDU can be set from 2K up to
32K
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = ukp9930)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = rep1)
)
#LISTENER.ORA
LISTENER =
(ADDRESS_LIST =
(ADDRESS=
(COMMUNITY= TCP.world)
(PROTOCOL= TCP)
(Host= UKP9930)
(Port= 1521)
)
(SID_DESC =
(SDU=4096) <---------- SDU has to match the setting in
(SID_NAME = ORCL) tnsnames otherwise it will negotiate
to
) the lowest of the two sizes
)
Again getting the best SDU setting comes down to a bit of maths.
Please note that it is only possible to set the SDU size if SQL*Net is 2.3 or higher on both the client and the server.
This is all you can do from an Application or SQL*Net perspective. However
if you
get this right, you can then look at other things which may be decreasing
performance on your WAN.
3 Tuning the components of your network.
As mentioned earlier, as well as tuning the application, the majority of
performance
issues over the network comes down to the components of the network
itself. If you
experience performance issues you should check the following:
Through the sniffer trace these potential problems should be identified,
and it is
really down to the customers network analysts to check all this out.
<dgh_consulting_at_my-deja.com> wrote in message news:7mv3dc$q4l$1_at_nnrp1.deja.com...
> Hi! We have been doing some volume testing of a new client application, > the back-end of which is Oracle 8.0.5 on Windows NT 4.0 (SP3). We are > using Net8 and TCP/IP protocol. The clients are using SQL*Net Client > v2.3.4.0.4 (might seem strange, but they use multiple Oracle-based > applications, some of which are incompatible with Net8). > > I am using a test harness which spawns a number of plus33 (or plus80) > sessions, each of which connects to the back-end database and executes > a stored procedure which simulates the update activity which will be > performed by the new app. > > My problem is that although the back-end server doesn't seem too > troubled (disk queues, CPU and memory usage all within acceptable > bounds), I can't get the listener to connect the SQL*Plus sessions at > quicker than 1 per second. > > I believe that using the listener pre-spawned connections option is of > little use in Windows NT, as these are not recreated after a user has > disconnected (unlike in UNIX, for example). > > Oracle Support suggested setting USED_SHARED_SOCKET = TRUE in the > registry to improve performance a little, but I note from an article in > Metalink that this should NOT be used when running Oracle FailSafe > (which we are using). Note that I have recreated the test in a non- > clustered environment and still get the same result, so OFS isn't the > problem. > > I have also tested with and without Oracle Names Server, and get the > same result in both instances (appox. 1 connection per second). This is > a worry, because if 100 users wanted to connect at once, the 100th > would take over a minute and a half to connect. Although 100 connect > requests at once might seem unlikely, in this case it would be possible > because the app connects, does its stuff and then disconnects every > time the user clicks a button. I am told that it was designed in this > way for resilience! > > Help! I have logged the problem with both Oracle and Microsoft. Does > anyone know which of these is to blame? I suspect that NT might not be > able to handle the socket requests quickly enough. I tried getting into > SMS Remote Control on the back-end server while a test was running, and > had problems. I guess that this could back up my suspicion, but I'm > still not sure. I have adjusted the listener QUEUESIZE, but this only > affects the number of connect requests that are allowed to queue up - > it has nothing to do with the speed of connections. > > Sorry this is such a bit post. I'd be VERY grateful for any help on the > subject. > > Thanks v. much, > > Dave Henderson. > > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.Received on Mon Jul 19 1999 - 10:32:38 CDT
 |
 |