Oracle UNIX Checking Net8 Adapters Administration Oracle UNIX/Linux Tips by Burleson Consulting

Checking Net8 adapters in UNIX

UNIX has an adapter command that can be used to display all network adapters that are linked with your Oracle listener in UNIX.  To see how this command works, go to your $ORACLE_HOME\/bin directory and issue the UNIX command adapters tnslsnr.

root> cd $ORACLE_HOME/bin

root> adapters tnslsnr     

Net Protocol Adapters linked with tnslsnr are:

BEQ Protocol Adapter
IPC Protocol Adapter
TCP/IP Protocol Adapter
RAW Protocol Adapter

Net Naming Adapters linked with tnslsnr are:

Oracle TNS Naming Adapter
Oracle Naming Adapter

This listing will show us the direct linking between Oracle Net8 software and the UNIX protocol adapters.

Now that we have the general idea about remote connectivity in UNIX, let?s take a closer look at the internal mechanism of Oracle?s Transparent Network Substrate.

TNS and UNIX for Oracle

With Oracle databases shared across geographical areas, it is very important for the Oracle professional to recognize the importance of network communications on the performance of their databases. As you may know, Oracle provides for distributed communications between databases by using its Transparent Network Substrate (TNS).

The TNS is a distributed protocol that allows for transparent database communications between remote systems. The TNS acts as an insulator between Oracle?s logical request for data and the physical communications between the distributed servers. Because of this insulation between the Oracle logical data request and the internal workings of the network, much of the network performance tuning is in the hands of the network administrator. In other words, the Oracle administrator has very little direct control over the network configuration settings that can affect the overall performance of their database (see Figure 5-1)

Figure 2: Tuning the Oracle network

However, there are some important settings that can be used in order to improve the performance of distributed transactions. This chapter will take a careful look at the init.ora parameters that relate to distributed communications, and also take a look at some of the TCP parameters such as tcp.nodelay, which can be used in order to change the basic packet-shipping mechanisms within the Oracle database.

We will also take a look at different parameters within the sqlnet.ora, tnsnames.ora, and protocol.ora files, which can be used to change the size and configuration of the TCP packets. These tools can often have a profound impact on the behavior of the underlying network transport layer and improve throughput of all Oracle transactions.

A common misconception about Net8 is that you can the tune Oracle network parameters to realize performance gains across a network. With a few minor exceptions, all network traffic is outside the scope of Oracle and cannot be tuned from within the Oracle environment. Net8 is simply a layer in the OSI model and it resides above the network-specific protocol stack. In other words, virtually all network tuning is external to the Oracle environment. When a remote data request is initialed, Net8 will get the data for the packet and hand it over to the protocol stack for transmission. The protocol stack will then create a packet with this data and send it over the network. Because Net8 simply passes data to the protocol stack, there is very little Net8 can do to improve performance.

However, the DBA can control the size and frequency of the network packets. The Oracle DBA has a wealth of tools that can change the packet size and frequency that packets are sent over the network. For a simple example, the refresh interval for a snapshot can be changed to ship larger amounts over the network at a less frequent interval.

To understand how the Oracle DBA can configure Oracle for better network performance, we must first start with a brief overview of a distributed Oracle database. We will then explore the internals of Oracle Transparent Network Substrate (TNS), and then look at the tools that network administrators use to tune network traffic.

This chapter has the following sections that address network tuning issues:

* Optimizing Oracle Net8 configuration

* Other Oracle features that effect network performance

* Monitoring network performance using STATSPACK

* Tuning the distributed network

In each of these sections we will discuss the tuning options that are available to help the DBA manage and tune distributed Oracle communications. Let?s begin by looking at the configuration parameters that affect network performance.

Optimizing Oracle Net8 Configuration

There are several tuning parameters that will affect the performance of Net8 connections between servers. However, we must always remember that the tuning of the network is outside the scope of Oracle, and the services of a qualified network administrator should be used for tuning the network. The following parameter files contain settings that affect the size and frequency of packet shipping across the network:

* sqlnet.ora server file
* automatic_ipc
* sqlnet.ora client file
* break_poll_skip
* tnsnames.ora file
* SDU, TDU
* listener.ora file
* SDU TDU
* protocol.ora file
* tcp.nodelay

Remember, these are the only tuning parameters that will affect the performance of the Oracle Net8 layer. Let?s discuss these parameters and see how they can be adjusted to improve Net8 throughput.

The tcp.nodelay Parameter in protocol.ora

By default, Net8 waits before transmitting a request until the buffer is filled up. This can mean on some occasions that a request is not sent immediately to its destination. Most often, this behavior occurs when large amounts of data are streamed from one end to another, and Net8 waits until the buffer is full before transmitting the packet. To remedy this problem, you can add a protocol.ora file and specify tcp.nodelay to stop delays in the buffer flushing process.

For all TCP/IP implementations, the protocol.ora file can be specified to indicate no data buffering. This parameter can be used on both the client and the server. The statement in protocol.ora is:

tcp.nodelay = yes

By specifying this parameter, TCP buffering is skipped and every request is sent immediately. In some cases, setting this parameter can cause network slowdowns. The network traffic can increase due to the smaller (and more frequent) network packets being transmitted between the client and the server.

Oracle recommends that tcp.nodelay should only be used if TCP timeouts are encountered. However, in conditions of high-volume traffic between database servers, setting tcp.nodelay can have a huge improvement in performance.

The automatic_ipc parameter of sqlnet.ora

The automatic_ipc parameter speeds local connections to a database because it bypasses the network layer. If automatic_ipc=on, Net8 will first check to see if a local database with the same alias definition exists. If so, the connection will be translated to a local IPC connection and will therefore bypass the network layers. This is, of course, only useful on database servers, and is a completely useless feature on Net8 clients.

On the database server, the automatic_ipc parameter should only be used in cases where a Net8 connection must be made to the local database. If no local database connections are needed or required, put this parameter to off, and all Net8 clients should have this setting to improve performance.

SDU and TDU parameters in tnsnames.ora

The SDU and TDU parameters are placed in the tnsnames.ora and listener.ora files. SDU is the session data unit, and specifies 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. Oracle recommends that SDU should be set to MTU.

Note: Prior to release 7.3.3, both SDU and TDU where fixed at 2k and couldn?t be changed.

The TDU (transport data unit) is the default packet size used within Net8 to group data together. Ideally, the TDU parameter should be a multiple of the SDU parameter. The default values for SDU and TDU are 2,048, and the maximum value is 32,767 bytes.

The following guidelines apply for SDU and TDU:

* On fast network connections (T1 or T3 lines), you should set SDU and TDU equal to the MTU for your network. On standard Ethernet networks, the default MTU size is set to 1,514 bytes. On standard token ring networks, the default MTU size is 4,202.

* The SDU should never be set greater than TDU because you will waste network resources by shipping wasted space in each packet.

* If your users are connecting via modem lines, you may want to set SDU and TDU to smaller values because of the frequent resends that occur over modem lines.

* If the MultiThreaded Server (MTS) is used, you must also set the mts_dispatchers with the proper MTU TDU configuration.

Here is an example of these parameters on a token ring network with an MTU of 4,202:

Listener.ora
   SID_LIST_LISTENER =
      (SID_LIST =
         (SID_DESC =
            (SDU = 4202)
            (TDU = 4202)
            (SID_NAME = ORCL)
            (GLOBAL_DBNAME = ORCL.WORLD)
         )
      )
tnsnames.ora
   ORCL.WORLD =
      (DESCRIPTION =
         (SDU=4202)
         (TDU=4202)
         (ADDRESS =
            (PROTOCOL = TCP)
            (HOST = fu.bar)
            (PORT = 1521)
         )
         (CONNECT_DATA = (SID = ORCL))
      )

Again, we must remember that the SDU and TDU settings are a direct function of the connection speed between the hosts. If you have a fast T1 line, set SDU=TDU=MTU. For slower modem lines, you need to experiment with smaller values of SDU and TDU.

If you are using Oracle8i, the database will automatically register instances in the listener.ora file unless you take one of the following actions:

* Implement the Multi-threaded Server (MTS) and define the mts_dispatchers in your init.ora file:

MTS_DISPATCHERS="(DESCRIPTION=(SDU=8192)(TDU=8192)\
ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)(HOST=supsund3)))\

                    (DISPATCHERS=1)"

* Use service_name=global_dbname in the Connect_Data section of the tnsnames.ora file, where global_dbname is configured in listener.ora. Note that this setting will disable the use of Transparent Application Failover (TAF), which is not supported using global_dbname. For details, see ?Configuring Transparent Application Failover? in the Net8 Administrator's Guide.

* Do not use automatic service registration. To do this, you must set the init.ora parameter local_listener to use a different TCP Port than the one defined in your listener.ora file.

Next, let?s look at the queuesize parameter and see how it affects network performance.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.