Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Optimizing your Oracle*net configuration

Oracle Tips by Burleson Consulting


Dec 16, 2015, Updated March 27, 2014 - Donald Burleson

Oracle databases are often shared across geographical areas, so it's imperative that the Oracle professional understand how database performance is affected by network communications. The Transparent Network Substrate (TNS), provided by Oracle, allows distributed communications between databases.

The TNS, which is a distributed protocol, allows for transparent database communications between remote systems. The TNS serves as an insulator between Oracle's logical data request and the physical communications between the remote servers. As such, the network administrator is able to control much of the network performance tuning. The Oracle administrator, then, has little control over the network settings that can affect overall database performance:


You can improve the performance of distributed transactions by using some important settings, several of which are discussed in this article. The init.ora parameters relate to distributed communications, while the TCP parameters, such as tcp.nodelay, can be used to change the packet-shipping mechanisms within the Oracle database.

I also discuss the parameters within the sqlnet.ora, tnsnames.ora, and protocol.ora files. You can use these tools to change the configuration and size of TCP packets, and they can have a profound impact on the underlying network transport layer to improve the throughput of all Oracle transactions.

Oracle*Net does not allow the Oracle administrator to tune Oracle network parameters to improve network performance. In fact, the majority of network traffic can't be tuned from within the Oracle environment. Oracle*Net is a layer in the OSI model that resides above the network-specific protocol stack. Almost all network tuning, then, is external to the Oracle environment.

In response to a data request, Oracle*Net gets the data and hands it over to the protocol stack for transmission. The protocol stack then creates a packet from this data and transmits it over the network. Oracle*Net's sole task is to pass data to the protocol stack, leaving little means for the DBA to improve network performance.

The DBA can, however, control the frequency and size of network packets. There exists in Oracle a wealth of tools to change packet frequency and size. A simple example involves changing the refresh interval for a snapshot to ship larger amounts at less frequent intervals.

Oracle*Net connections between servers can be tuned using several parameters. Keep in mind, though, that network tuning is outside the scope of Oracle, and a 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 parameter files:
 

  • protocol.ora fileótcp.nodelay
  • sqlnet.ora server fileóautomatic_ipc
  • sqlnet.ora client fileóbreak_poll_skip
  • tnsnames.ora fileóSDU, TDU
  • listener.ora fileóSDU, TDU, and queuesize


These tuning parameters will affect the performance of the Oracle*Net layer only. Let's examine them in detail and see how they can be adjusted to improve Oracle*Net throughput.

The tcp.nodelay parameter in protocol.ora

Oracle*Net, by default, waits until the buffer is full before transmitting data. Therefore, requests are not always sent immediately to their destinations. 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. Adding a protocol.ora file, and specifying a tcp.nodelay to stop buffer flushing delays, can remedy this problem.

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

             
 tcp.nodelay = yes

Specifying this parameter causes TCP buffering to be skipped so that every request is sent immediately. Keep in mind, however, that network traffic can increase due to smaller and more frequent packet transmission, therefore causing slowdowns in the network.

The parameter tcp.nodelay should be used only if TCP timeouts are encountered. Setting tcp.nodelay can cause a huge performance improvement in high-volume traffic between database servers.

The automatic_ipc parameter of sqlnet.ora

The automatic_ipc parameter bypasses the network layer, thereby speeding local connections to the database. 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 absolutely useless for Oracle*Net clients.

The automatic_ipc parameter should be used on the database server only when an Oracle*Net connection must be made to the local database. If local connections are not needed or required, set this parameter to off, improving the performance of all Oracle*Net clients.

SDU and TDU parameters in tnsnames.ora and listener.ora

The session data unit (SDU) and transport date unit (TDU) parameters are located in the tnsnames.ora and listener.ora files. SDU specifies the size of the packets to send over the network. Ideally, SDU should not surpass the size of the maximum transmission unit (MTU). MTU is a fixed value that depends on the actual network implementation used. Oracle recommends that SDU be set equal to MTU.
 


Note: Prior to release 7.3.3, both SDU and TDU were fixed at 2K and couldn't be changed.


The TDU is the default packet size used within Oracle*Net to group data together. The TDU parameter should ideally be a multiple of the SDU parameter. The default value for both SDU and TDU is 2,048, and the maximum value is 32,767 bytes.

The following guidelines apply for SDU and
TDU:

  • You should never set the SDU greater than TDU because you'll waste network resources by shipping wasted space in each packet.
     
  • If your users are connecting via dial-up modem lines, you may want to set SDU and TDU to smaller values because of the frequent resends that occur over modem connections.
     
  • 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 bytes.
     
  • If a Multi-Threaded Server (MTS) is used, you must also set the mts_dispatchers with the proper MTU TDU configuration.
     
  • For Streams replication and for using Oracle in a WAN environment, Oracle recommends that you increase the SDU to 32k.  Also, the Oracle docs note:

    To take advantage of an increased SDU for Streams propagation, the receiving side sqlnet.ora file must include the DEFAULT_SDU_SIZE parameter. 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. 

    For Streams, the SEND_BUF_SIZE and RECV_BUF_SIZE parameters in the listener.ora file increase the performance of propagation on your system.  Oracle internal testing of Oracle Data Guard on a WAN has demonstrated that the maximum setting of 32K (32768) performs best on a WAN. The primary gain in performance when setting the SDU is a result of the reduced number of calls to packet the data.

    In addition to setting the SDU parameter, network throughput can often be substantially improved by using the SQLNET.SEND_BUF_SIZE and SQLNET.RECV_BUF_SIZE Oracle Net parameters to increase the size of the network TCP send and receive I/O buffers.


The listing below shows an example of these parameters on a token ring network with an MTU of 4,202 bytes.

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))
      )


The SDU and TDU settings are a direct function of the connection speed between the hosts. For fast T1 lines, set SDU=TDU=MTU. For slower modem lines, experiment with smaller values of SDU and TDU.

Since Oracle8i, the database will automatically register instances in the listener.ora file unless you implement one of the following actions:

  • Disable automatic service registration. To do this, you must set the init.ora parameter local_listener to use a TCP port other than the one defined in your listener.ora file.
     
  • Implement the MTS (not recommended unless absolutely necessary) and define the mts_dispatchers in your init.ora file, like this:

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 Oracle*Net Administrator's Guide.


    The queuesize parameter in listener.ora

    The number of requests the listener can store while Oracle is working to establish a connection is determined by the undocumented queuesize parameter. This 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. Here's an example of this parameter in the listener.ora file:

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


    A disadvantage of this parameter is that it preallocates resources for anticipated requests, therefore using more system memory and resources. You may want to consider using MTS and prespawned Oracle connections if you have high-volume connections into a dedicated listener. Also, note that some versions of UNIX do not allow queues greater than five, and there are some restrictions of the MTS queue size.

    The break_poll_skip parameter of sqlnet.ora

    This client-only sqlnet.ora parameter indicates the number of packets to be skipped before checking for a user-initiated break. This parameter affects the amount of CPU resources used by the Oracle*Net client and functions only with servers that support in-band breaks. The default value is four, and the results of modifying the value are as follows:
     

    • The higher the break_poll_skip value is set, the less frequently the checking for CTRL-C occurs and the less CPU is consumed.
       
    • Conversely, the lower the value is set, the more frequently the CTRL-C checking is done and the more CPU is consumed.


    External network tuning

    In this brief discussion of network-related parameters, I've given you a sense of the scope and complexity of network tuning. It's important to understand that Oracle*Net is simply a layer in the OSI model lying above the network-specific protocol stack, and that virtually all network tuning is therefore external to Oracle.
     
     
    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.


     

     

  •  

    ��  
     
     
    Oracle Training at Sea
     
     
     
     
    oracle dba poster
     

     
    Follow us on Twitter 
     
    Oracle performance tuning software 
     
    Oracle Linux poster
     
     
     

     

    Burleson is the American Team

    Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

    Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

    Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

    and include the URL for the page.


                        









    Burleson Consulting

    The Oracle of Database Support

    Oracle Performance Tuning

    Remote DBA Services


     

    Copyright © 1996 -  2017

    All rights reserved by Burleson

    Oracle ® is the registered trademark of Oracle Corporation.

    Remote Emergency Support provided by Conversational