SQL*Net diagnostics tips
Oracle Database Tips by Donald BurlesonAugust 7, 2015, Updated December 3, 2015
Network diagnostics and performance tuning are
important issues to consider for savvy administrators managing
complex distributed Oracle installations. Oracle has many powerful
features for SQL*Net diagnostics, such as logging and tracing
mechanisms, explanation tools, and statistics collection. To
optimize network performance, administrators should accurately tune
Oracle SQL*Net services. There are many external and internal
factors influencing SQL*Net performance. We will consider how to
tune SQL*Net buffer size, how to speed up connections for dedicated
and multi-threaded servers, what is listeners load balancing, and we
will learn new
SQL*Net features affecting diagnostics and
performance. We use the SQL*Net term to describe versions starting with SQL*Net 2.3.x. We will also use
Oracle utility names for UNIX systems.
SQL*Net is a transparent network interface provided for
Oracle applications to connect to Oracle DBMS server software. Oracle
SQL*Net hides the complexity of Underlying Network Protocol (UNP) and takes
care of managing connections over distributed heterogeneous networks.
Consider the protocol stack in detail. UNP consists of upper layer protocol
(ULP) and lower layer protocol (LLP). To better understand the overall
picture, take a look at the protocol stack in terms of the Open System
Interconnection (OSI) model (see Exhibit 1).
Oracle applications can be Oracle Forms, SQL*Plus, etc.
software consists of several major components: Oracle Call Interface/User
Program Interface (for client side), Oracle Program Interface (for server
side), Two Task Common, Transparent Network Substrate, and Oracle Protocol
Adapters. The most popular ULPs are TCP/IP, and DECnet, IPX/SPX; and common
LLPs are FDDI, Ethernet, Token Ring, and ARCNET. The physical layer deals
with hardware sending and receiving data on a carrier.
Before starting the analysis, let's define what factors
affect network performance and select evaluation criteria. We considered
network stack and saw that SQL*Net performance is strongly influenced by
underlying UNP parameters. On the other hand, SQL*Net performance is
affected by upper-level Oracle software characteristics like administrator's
choice on using multi-threaded server versus dedicated server, number of
active server processes, time required to scan large tnsnames.ora files,
level of SQL*Net tracing configured on client side and server side, security
mechanisms used to authenticate connections, encrypt data being transferred,
and checksumming models. And, of course, SQL*Net performance is influenced
by external network factors like topology, throughput, domain name service
configuration, etc. It is important to remember that the basic
characteristics of SQL*Net performance for processing Oracle transactions
are connect time and query time. We will see how to reduce network traffic
and make data transfer fast and reliable by synchronizing UNP parameters
with SQL*Net parameters.
Let's highlight some of the network problems related to
SQL*Net software. Among these are: broken connections, bad load-balancing,
too long response times at peak workloads, and automatic adjustment of
Oracle server software causing excessive resources utilization. If an error
arises, it affects many network components and error codes spread like
wildfire through the protocol stack. It is difficult to locate the true
reason causing the network problem; but fortunately, there are tools to help
with SQL*Net diagnostics.
To reduce administrator headaches
because of network problems, Oracle offers tracing and logging features of
SQL*Net and Trace Assistant tool to explain the trace codes. Tracing can
provide very detailed information and it should be turned on to solve
serious problems, that cannot be resolved by analyzing standard log
To locate what network component causes the problem, one should
systematically run checks to reduce the search scope. First, check if the
server node is accessible from the client machine. Then, make sure on the
server side that the database is up and running.
Third, check if the client
has the appropriate network adapter installed. After that, run the Listener
Control utility and see if listener required by client is running and can
handle requests for target database. A good way to make sure that Oracle can
establish client/server connection is tnsping utility. This utility takes the TNS service name as a first
parameter, and the number of attempts as a second (optional) parameter.
utility returns response time if it manages to establish the connection. The
tnsping utility can also be used
to measure network throughput between client and server. If
fails, check the configuration files syntax. If a problem persists, you can
use logging and tracing mechanisms to get in-depth information.
Let's show how to turn on the
SQL*Net logging and tracing. On the client side, one can define the
directory where to put log and trace files in the
sqlnet.ora file using the
respectively. One can also define the log and trace file names with the
of the sqlnet.ora file.
default, trace on UNIX systems is written to
$ORACLE_HOME/network/trace/sqlnet.trc, and log is written to
sqlnet.log file under the same directory.
On the server side, one can define log and trace destinations with the
All these parameters should be
configured in the listener.ora
file. By default, server logs and traces are stored in
listener.trc files under the
$ORACLE_HOME/network/trace directory. Note that, by default, logging is
turned on and one can change this behavior with the
logging_<lsnr> parameter of the
listener.ora file. One can switch
between different trace standards on the client side using the
At the server side, the trace level parameter is called
and it should be included into the
listener.ora. Valid trace levels are: OFF, USER, ADMIN and SUPPORT for Oracle8.
Now let's discuss how to analyze network problem using
log messages. First, scan the tail of SQL*Net log file and detect what is
the latest error number caused by the application. It usually corresponds to
the last line of log file. Remember that log files can grow to very large
sizes, which makes it difficult to read. One should carefully filter the log
information. For example, on UNIX systems, one can see the last 40 log lines
issuing a tail -40 <log file name> command.
The last non-empty message in
the error stack usually indicates actual problem cause. If log analysis does
not help to remove the problem, turn on the tracing at the desired level and
repeat the sequence of events triggering the error. Trace information is
generated according to the network layers participating in the data
transfer. Do not forget to turn off the tracing once the problem is solved.
Excessive tracing may slow down the overall network performance.
The mission-critical component of SQL*Net software is the
listener. Let's take a closer look at most common listener error codes and
workarounds for the corresponding problems. The ORA-12541 error "No
listener," which appears at some client's logs while the listener is up and
running, denotes that incoming connection requests are received too quickly
and listener cannot handle it.
To avoid this problem, increase the
queuesize parameter at
listener.ora file, restart the
listener, and see if the new value is sufficient. Another listener- related
error is ORA-12224 "TNS: No listener." It indicates that connection cannot
be completed because listener is not running. One can try to bypass the
problem by verifying if the client connects to the same address the listener
actually uses, and that version compatibility is OK.
One of the most
frequent listener errors is ORA-12545 "TNS: Name lookup failure." This error
indicates that the client cannot make contact with remote node. Check the
correctness of the ADDRESS keyword syntax on the server and client. Next,
make sure that the listener process on the remote node has been started. Log
on to the server and run the Listener Control utility. Within this utility,
issue a STATUS <lsnr> command; and if there are no listeners, run it with
the START command.
Common practice among administrators is to copy service
descriptions on a client machine when target database changes or one more
database should be added to those listed in the
tnsnames.ora file. This can raise
the ORA-12154 error "TNS: Could not resolve service name." Upon receiving
this error, check if the service name supplied for connection exists in the
configuration file and if all its parameters are correct. To avoid such
problems, the administrator should take care of configuration files version
When turning on SQL*Net tracing,
Oracle writes errors and warnings along with internal parameters and hex
packet representations to trace files, which are not that easy to read and
analyze. For better presentation and explanation of trace messages you can
use Trace Assistant. Run Trace Assistant from the operating system
environment issuing a trcevalcommand. Trace Assistant can evaluate SQL*Net packets; it can also
present summary and detailed reports. Summary shows the total number of sent
and received packets, and detailed report displays a bit-level picture.
Valuable command-line options for Trace Assistant include:
summary connectivity information (c)
detailed connectivity information (d)
detailed TTC information (t)
summary Two-Task Common information (u)
SQL commands (q)
which should be used together with (u) and
overall trace statistics (s).
Command syntax changed slightly with the introduction of SQL*Net 2.3.x.
To monitor network traffic carried by SQL*Net without
resource-intensive tracing, you can use a third-party network analyzer that
will collect statistics on SQL*Net listener ports. Another way is to use
native Oracle statistics that can be accessed using the
There are six major parameters related to SQL*Net traffic
statistics; these are number of bytes sent to client and database link via
SQL*Net, number of bytes received from client and database link via SQL*Net,
total number of SQL*Net roundtrips between client and database link. You can
read current values by issuing the following SQL statement:
SELECT name, value FROM v_$sysstat
WHERE name LIKE '%SQL*Net%';
To avoid manual typing and get a better picture of network traffic
dynamics you can decide to design a data schema for cumulative
statistics, write a statistics collection procedure, and schedule it for
regular execution. The entire process is described below in the
"Optimizing Connect Time with MTS" section where a similar problem is
For more information on troubleshooting TNS SQL*Net connection
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.