Questions to ask
1. Is the problem intermittent or happening all the time? 2. When did the problem start to happen and what was changed around the time the first slow down was seen? 3. How is the problem resolved, ie kill listener process or sorts itself out, etc? 4. Is the connection model dedicated or shared server. (Formally known as MTS).Ensure both connection models are tested. This might provide a workaround for the customer. 5. When the problem is happening :
Telnet and other utilities that make a three way handshake to the server where the listener is running, are they fast or slow? Do all clients have the problem? Bequeath connection are slow or fast? ie sqlplus username/password on the server. If bequeath is slow, then the issue may well be with the RDBMS or operating system.
Diagnostics.
1. Oracle Net level 16 listener cyclic listener and Oracle Net client trace. Listener tracing, add to LISTENER.ORA file :
TRACE_LEVEL_<listenername> = 16 TRACE_FILE_<listenername> = <FILE NAME> TRACE_DIRECTORY_<listenername> = <DIRECTORY> TRACE_TIMESTAMP_<listenername> = ON TRACE_FILELEN_<listenername> = <SIZE in KB> eg 20480 TRACE_FILENO_<listenername> = <NUMBER> eg 10
Re-start the listener. Cyclic tracing will ensure your can control the size and amount of trace produced. The TRACE_FILELEN parameter is for the size of a trace file. The TRACE_FILENO parameter is the number of traces per process.
Client tracing. Add to a clients SQLNET.ORA file :
TRACE_LEVEL_CLIENT = 16 TRACE_FILE_CLIENT = <FILE NAME> TRACE_DIRECTORY_CLIENT = <DIRECTORY> TRACE_TIMESTAMP_CLIENT = ON
Enable client trace just when the problem starts, for a single client.Check when the time gap in the client trace is seen. What stage of the connection handshake is the slow down reported ? What is the listener trace doing at this point ? Check the listener trace for the fork process.
2. Collect Listener log covering time of the slow down and a few hours before. Count established connections and divide by time to find an average connection rate
3. Run RDA at the time of the slow down. Note 314422.1Remote Diagnostic Agent (RDA) 4 – Getting Started
4. O/S watcher information leading up to the time of the slow down Note 301137.1OS Watcher User Guide
5. Pstack / Truss / Strace / Tusc of the listener process Note 110888.1How to Trace Unix System Calls
Pstack <PID_of_listener>
Sun : truss -aefdDo /tmp/truss-lsnr.log -p <PID_of_listener> Linux : strace -frT -o /tmp/strace-lsnr.log -p <PID_of_listener> HP : tusc -aef -o /tmp/tusc-lsnr.log -T “%H:%M:%S” -p <PID_of_listener>
Is possible run the command a couple of times when the listener is slow/hung, to confirm if the listener is in fact hung or running slow.
Possible Solutions
If the listener is stress or overwhelmed with connections, then the follow can help.
1. Increase the QUEUESIZE of the listener and ensure TCPIP settings are reviewed.Before increasing the value of QUEUESIZE, check the default value for and review the listener.log for the incoming connection rate. If the connection rate is not over the default value for QUEUESIZE and there is no backlog for establishing connections, increase here will not help.
2. Add additional Listener(s) and client load balance connections between the listeners.
If a second listener is not an option, attempt to setup a second address for the listener in the address list section. Example:
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = key1)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sample.com)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sample.com)(PORT = 1522)) ) )
3. If the connection model is dedicated, switch to Shared Server. Connections being handed off to dispatchers means the TNS listener does not have to spawn shadow processes for dedicated connections. If the connection model is shared server, ensure correct values are being used for dispatchers and shared severs, for the amount of connections. Confirmed there is no contention for these.
Tuning options for dispatchers problems include adding more dispatchers, using connection pooling and session multiplexing.Tuning options for shared server issues include reviewing the LARGE_POOL and SHARED_POOL performance. Both are covered in detail via the documentation Oracle Database Performance Tuning Guide
And in Note 1005259.6Shared Server (MTS) Diagnostics.
4.If the client connections are generated on the server where the database is running, consider using IPC protocol instead of TCPIP.
5.Check the LISTENER.LOG file size. Ensure its not very large. Connections need to write to the file for auditing and if the O/S is having issues with this, it can slow down connection. With 11g check ADR is not having issues with writing of files
Example of Client trace, waiting on TNS listener answer
o Trace starts here >>
[17-APR-2010 15:15:00:336]
o Start of the connection handshake and the client sends connect packet to TNS listener.NSPTCN is the connect packet
[17-APR-2010 15:15:00:370] nscon: doing connect handshake… [17-APR-2010 15:15:00:370] nscon: sending NSPTCN packet
o Time gap is then see between connect and resend packet. So the client is waiting to hear back from the TNS listener.
[17-APR-2010 15:15:00:371] nsprecv: reading from transport… [17-APR-2010 15:15:00:371] nttrd: entry [17-APR-2010 15:15:05:725] nttrd: socket 9 had bytes read=8 [17-APR-2010 15:15:05:725] nttrd: exit [17-APR-2010 15:15:05:725] nsprecv: 8 bytes from transport [17-APR-2010 15:15:05:726] nsprecv: tlen=8, plen=8, type=11 [17-APR-2010 15:15:05:726] nsprecv: packet dump [17-APR-2010 15:15:05:726] nsprecv: 00 08 00 00 0B 00 00 00 |……..| [17-APR-2010 15:15:05:726] nsprecv: normal exit [17-APR-2010 15:15:05:726] nscon: got NSPTRS packet
NSPTRS is resend packet, depending on connection model, version and platform, this can be a redirect packet (NSPTRD). In this case, we now need to investigate what the TNS listener is doing. More than likley the bequeath of the shadow process is the the problem.
6. Check the listener log at the time the performance issues are occurring and make certain there are not a lot of * ping * entries in the log. For example, if you see multiple single line entries per SECOND occurring, this will cause an abnormal load on the listener. These too need to be logged and time spent by the listener on them.
Example extract from a listener log: … 21-JUN-2011 22:59:14 * ping * 0 21-JUN-2011 22:59:14 * ping * 0 21-JUN-2011 22:59:14 * ping * 0 21-JUN-2011 22:59:14 * ping * 0 21-JUN-2011 22:59:14 * ping * 0 21-JUN-2011 22:59:14 * ping * 0 21-JUN-2011 22:59:14 * ping * 0 21-JUN-2011 22:59:14 * ping * 0 21-JUN-2011 22:59:14 * ping * 0 21-JUN-2011 22:59:14 * ping * 0 —> Another 89 lines 21-JUN-2011 22:59:14 * ping * 0 …
Such * ping * message flooding is normally due to a program or application that is meant to monitor the listener’s UP / DOWN status but is not configured correctly. There are some third party apps as well as Oracle’s Intelligent Agent. Make certain the Application or Program settings are pinging the listener not more than a few every minute.
Bình luận