tracing slow listener connections- for the new oracle dba





you are a new oracle dba and you want to find out why connecting to your database from other servers is very slow. you want to see if the listener is the reason. Below article explains about how to trace the listener connections and how to troubleshoot issues with the listener.

Subject:

How to Diagnose Slow TNS Listener / Connection Performance
 

Doc ID:

557416.1

Type:

HOWTO
 

Modified Date :

18-MAR-2009

Status:

PUBLISHED

In this Document
Goal
Solution


Applies to:

Oracle Net Services – Version: 9.2.0.1.0
Information in this document applies to any platform.
Connections to database are slow or intermittently fail

Goal

How to diagnose slow TNS listener issues or slow connections. Often ORA-12541 and ORA-12535 errors can be due to stressed or busy TNS listener.

Solution

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. Shared server information can be found in Note 1005259.6 Shared Server (MTS) Diagnostics
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 to request

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 10240
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 / Straceof the listener process Note 110888.1How to Trace Unix System Calls

Pstack <PID_of_listener>
truss -aefdDo <out_file_name>-p <PID_of_listener>
strace -frT -o <out_file_name> -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.

Solutions

If the listener is stress or overwhelmed, then the follow can help

1. Increase the QUEUESIZE of the listener, ensure TCPIP setting reviewed.Note 67983.1Oracle Net Performance Tuning

2. Add additional Listeners and client load balance between them.Note 557946.1How to Create Additional TNS listeners and Load Balance Connections Between them

3. If the connection model is dedicated, switch to Shared Server.Note 1005259.6Shared Server (MTS) Diagnostics

Known Problems
Note 785742.1 TNSListener Leaking Memory Using Dedicated Server
Note 561429.1 Connections To 11g TNS Listener are Slow.
Note 340091.1 Intermittent TNS Listener Hang, New Child Listener Process Forked
Note 472181.1 Non-Paged Pool Memory and Handle Count Leak While Using 10.2 Listener for 10.1 and 9i Databases
Note 557397.1 Listener Leaking Memory And High CPU Utilization on Oracle 9.2.0.8:
Note 368951.1 Dedicated Connections Slow Down to 64bit 10gR1 TNS listener

 

Keywords

INTERMITTENT ; HANGING ; LISTENER.ORA ; MTS ; SHARED~SERVER ; QUEUESIZE ; SLOW~CONNECTION ; PERFORMANCE ;


Help us improve our service. Please email us your comments for this document. .

   

Top of Form

Bottom of Form

 

 

Copyright © 2006, Oracle. All rights reserved.

Legal Notices  and Terms of Use | Privacy Statement

 

Bottom of Form

 

Author: admin