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. SQL*Net 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.

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 messages.

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.

This 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 tnsping 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 log_directory_clientand trace_directory_client parameters, respectively. One can also define the log and trace file names with the log_directory_client and trace_directory_client parameters of the sqlnet.ora file.

 By 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 following parameters: log_directory_<lsnr>, log_file_<lsnr>, trace_directory_<lsnr> and trace_file_<lsnr>.

All these parameters should be configured in the listener.ora file. By default, server logs and traces are stored in listener.log and 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 trace_level_clientparameter of sqlnet.ora. At the server side, the trace level parameter is called trace_level_<lsnr> 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 control.

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 v_$sysstat view.

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 solved.

For more information on troubleshooting TNS SQL*Net connection issues, check HERE.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle 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 buy it for 30% off directly from the publisher.

---