Great Tips on Optimizing Oracle Network Configuration!!

TNS or Transparent Network Substrate is Oracle's networking architecture. Oracle typically relies on TNS to provide generic network connectivity to and between Oracle databases. TNS provides a uniform application interface to enable network applications to access the underlying network protocols transparently.

It is very critical for Oracle professional to understand the affect of network configuration on database performance. The network administrator is able to control much of the network performance tuning and hence Oracle administrator has little control over the network settings that can affect overall database performance.

Performance Improvement by Setting Parameters:

The parameters within the sqlnet.ora , tnsnames.ora , and protocol.ora files can be set to improve the performance of distributed transactions. These parameters can be used to change the configuration and size of TCP packets. Adjusting these parameters can have a profound impact on the underlying network transport layer to improve the throughput of all Oracle transactions.

Oracle Net:

Oracle Net is a layer in the OSI model that resides above the network-specific protocol stack. Oracle Net Services provides methods for understanding and resolving network problems through the use of log and trace files. Since Oracle Net does not allow to tune the underlying network layer and therefore majority of network traffic cannot be tuned from within the Oracle environment.

Controlling Packet Frequency and Size:

Oracle provides a number of tools to change packet frequency and size. This enables Oracle database administrators to control the frequency and size of network packets. For example you can change the refresh interval for a snapshot to ship larger amounts at less frequent intervals.

Tuning Oracle Net connections:

Oracle Net connections between servers can be tuned using several parameters however only qualified network administrator should be consulted for tuning the network. The frequency and size of packet shipping across the network can be affected by using settings contained in the following below files. These tuning parameters will affect only the performance of the Oracle Net layer.

Controlling Buffer Flushing Delays:

The requests are not always sent immediately to their destinations as by default Oracle Net waits until the buffer is filled before transmitting data. This is most common when large amounts of data are streamed from one end to another, and Oracle Net does not transmit the packet until the buffer is full. However this problem can be solved if you add a protocol.ora file and specify a tcp.nodelay to stop buffer flushing delays.

tcp.nodelay parameter:

• Setting tcp.nodelay can cause a huge improvement in performance when there is high-volume traffic between database servers.

• tcp.nodelay parameter can be used on both the client and server.

• The tcp.nodelay parameter should be used only if TCP timeouts are encountered.

• You can specify tcp.nodelay = yes to indicate no data buffering for all TCP/IP implementations.

• Specifying tcp.nodelay = yes causes TCP buffering to be skipped so that every request is sent immediately. However network traffic can increase due to smaller and more frequent packet transmission causing slowdowns in the network.

Bypassing the Network Layer:

The automatic_ipc parameter speeds local connections to the database by bypassing the network layer.

automatic_ipc parameter:

• When automatic_ipc=on , Oracle Net checks to see if a local database is defined by the same alias. If so, network layers are bypassed as the connection is translated directly to the local IPC connections. This is useful on database servers, but it's absolutely useless for Oracle Net clients.

• The automatic_ipc parameter should be used only on the database server when an Oracle Net connection must be made to the local database.

• If local connections are not needed or required then all Oracle Net clients can improve performance by setting automatic_ipc= off;

Data Units:

The session data unit (SDU) and transport date unit (TDU) parameters are located in the tnsnames.ora and listener.ora files.

Increasing SDU for Streams propagation:

Include DEFAULT_SDU_SIZE parameter in the receiving side of sqlnet.ora file in order to take advantage of an increased SDU for Streams propagation. The receiving side listener.ora file must indicate the SDU change for the system identifier (SID). The sending side tnsnames.ora file connect string must also include the SDU modification for the particular service. 

Increasing Propagation Performance:

The performance of propagation on your system can be increased by SEND_BUF_SIZE and RECV_BUF_SIZE parameters in the listener.ora file. Network throughput can be significantly improved by using the SQLNET.SEND_BUF_SIZE and SQLNET.RECV_BUF_SIZE parameters to increase the size of the network TCP send and receive I/O buffers.

Determining number of requests:

You can determine the number of requests the listener can store while Oracle is working to establish a connection. This is determined by the undocumented queuesize parameter.

queuesize parameter:

• The queuesize parameter is used only for very high-volume databases, where the listener spawns thousands of connections per hour.

• The number of expected simultaneous connections should be equal to the size of the queuesize parameter.

  LISTENER =
       (ADDRESS_LIST =
             (ADDRESS =
               (PROTOCOL = TCP)
               (HOST = marvin)
               (PORT = 1521)
              (QUEUESIZE = 32)
             )
       )

• A disadvantage of queuesize parameter is that it uses more system memory and resources by pre-allocating resources for anticipated requests.

• You can use MTS and pre-spawned Oracle connections if you have high-volume connections into a dedicated listener. Some versions of UNIX do not allow queues greater than five and there are some restrictions of the MTS queue size.

Conclusion:

To conclude I would say that an Oracle professional must fully understand and optimize Oracle Net parameters as they can have a great impact on the performance of distributed systems. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Great Tips on Oracle Real-Time SQL Monitoring!!
   Add Flexibility to your database – Use Database Abstraction Layer
   Common Mistakes in Oracle Recovery, Interesting facts!
   Inside Oracle Temporary Tablespace!!