| 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
When experiencing slow SQL*Net connections, there are several things that can be done to check and debug this situation. This document give the specific areas one can immediately check:
SQLNET.ORA:
-----------
TRACE_LEVEL_CLIENT =OFF
TRACE_LEVEL_SERVER =OFF
TNSPING.TRACE_LEVEL=OFF
Logging and tracing are deactivated both when the trace levels are
put to 'OFF' or '0'.
LISTENER.ORA:
-------------
TRACE_LEVEL_LISTENER=OFF
LOGGING_LISTENER=OFF
4) Remove the listener log files
If the listener logging mode remains active, the LISTENER.LOG file
continues to grow. Since the listener program keeps this file locked
and continues to write a line in this file for every connection made,
the sheer size of the file by itself can become a problem.
If this file becomes too large, rename it, so that a fresh start of
the SQL*Net listener canstart with a clean new file.
5) SQLNET.ORA: AUTOMATIC_IPC AUTOMATIC_IPC = { ON | OFF }
This parameter in the SQLNET.ORA file indicates weather SQL*Net
should first check to see if a local database with the same alias
definition exist. If so, the connection will be translated to a
local IPC connection, and will therefore bypass the network layers.
This is off course only useful on database servers, and is a
completely useless feature on SQL*Net client machines.
Even on databases, this feature should only be used in very specific
cases where a SQL*Net connection must be made to the local database.
If no local database connections are needed or required, put this
parameter to OFF.
6) SQLNET.ORA: NAMES.DIRECTORY_PATH NAMES.DIRECTORY_PATH = (ONAMES,TNSNAMES)
This parameter specifies the resolution path for TNS aliases. The
first entry in this parameter will be checked first, then the
second, ...
By default, starting from SQL*Net 2.3.3, an attempt will be made
to contact 5 Name Servers with the names 'nameserver1',
'nameserver2', etc... Only when these attempts time-out, the
TNSNAMES will be consulted.
If there is no Oracle*Names defined on the network, it is advisable
to specify this parameter in the SQLNET.ORA without the 'ONAMES'
value.
If there is no local TNSNAMES.ORA on the client machines, also
adjust this line in the SQLNET.ORA file.
7) SDU and TDU parameters
These parameter pairs (NVPairs - Named Value pairs) can be specified
in the TNSNAMES.ORA and LISTENER.ORA files.
SDU is the 'Session Data Unit', the size of the packets to send over
the network. Ideally, this size should not surpass the size of the
MTU (Maximum Transmission Unit). This MTU value is fixed and depends
on the actual network implementation used.
The TDU, the 'Transport Data Unit', is the default packet size used
within SQL*Net to group data together. Ideally, the TDU parameter
should be a multiple of the SDU parameter.
Practical examples:
* SDU=1024, TDU=1536:
SQL*Net will store up to 1536 bytes in a buffer and send this
on the network. The lower network layer however will split this
packet up into two physical packets of, 1024 and 512 bytes,
and send these to its destination.
* SDU=1514, TDU=1000:
SQL*Net will store up to 1000 bytes and then send these to the
lower network layer for distribution. This is a waste of
network resources, since the SDU can store an additional
514 bytes per request.
On standard Ethernet networks, the default MTU size is set to 1514
bytes. On standard token ring networks, the default MTU size is 4202.
To specify the SDU and TDU parameters, changes must be made to both
TNSNAMES.ORA and LISTENER.ORA to make these parameters visible.
TNSNAMES.ORA:
-------------
ORCL.WORLD =
(DESCRIPTION =
(SDU=1514)
(TDU=1514)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = fu.bar)
(PORT = 1521)
)
(CONNECT_DATA = (SID = ORCL))
)
LISTENER.ORA:
-------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 1514)
(TDU = 1514)
(SID_NAME = ORCL)
(GLOBAL_DBNAME = ORCL.WORLD)
)
)
These parameters can be best used when working with a specific
networking environment: Using modem lines, it might be advisable to
bring down the SDU and TDU lines, because of the frequent resends,
while on fiber or T3 lines, it might be advisable to increase the
numbers.
The default values for SDU and TDU are 2048, with the maximum value
possible being 32768.
8) PROTOCOL.ORA: tcp.no_delay
An TCP implementation, this additional SQL*Net file can be specified
to indicate no data buffering. This parameter can be used on both
the client and the server.
By default, SQL*Net waits before transmitting a request until the
SDU buffer is filled up. This can mean in some occasions that a
request is not send immediately to its destination.
By specifying the 'no_delay' parameter, this buffering is skipped
and every request is send straight away. A practical implication of
this is that the network traffic can increase due to the smaller
(and more frequent) network packets being transmitted between the
client and the server.
This parameter should only be used if TCP time-out are encountered.
9) LISTENER.ORA: QUEUESIZE
The undocumented QUEUESIZE parameter determines the number of
request the listener can store while he's working to establish
a connection.
If the number of incoming requests exceeds the size of the buffer,
the client requesting the connection will receive failures.
Ideally, the size of this buffer should be equal to the number of
expected simultaneous connections.
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = fu.bar)
(PORT = 1521)
(QUEUESIZE = 32)
)
)
This parameter is only used when the TCP/IP or DECNET protocols
are used.
10) SQLNET.ORA: BREAK_POLL_SKIP
A lesser SQL*Net known parameter. This value specifies the number
of packets to skip before checking for a user break.
Rules of thumb here:
- The higher the value, the less frequent CTRL-C checking, and the
less CPU overhead used.
- The lower the value, the more frequent CTRL-C checking, and the
more CPU overhead used.
The default for this parameter is 4. Parameter is only useful on the
client side of SQL*Net.
11) SQLNET.ORA: DISABLE_OOB
Out of band break checks can be disabled by adding this parameter
to the SQLNET.ORA file.
If for some specific reason the checks should not be performed, set
this parameter to 'ON'. By default, SQL*Net assumes 'OFF' for this
parameter.
12) Tracing: PROCESS.DAT and REGID.DAT
In release 7.3.2, the Oracle Server Tracing is enabled by default. A
practical implication of this is that every connection and every
request send over SQL*Net is logged in the Oracle Trace files
PROCESS.DAT and REGID.DAT. After prolonged use of the database,
these files can become enormous slowing down the connection time
dramatically.
Solutions here are to remove the trace files, or specify an
additional variable 'EPC_DISBLED=TRUE' in the runtime environment of
the database.
I hope this of any help!
Regards
"The Views expressed here are my own and not necessarily those of Oracle Corporation"
<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:29:14 CDT
 |
 |