BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Optimizing Oracle NET Configuration

Oracle Tips by Burleson Consulting

There are several tuning parameters that will affect the performance of Oracle NET 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 NET layer. Let's discuss these parameters and see how they can be adjusted to improve Oracle NET throughput.

The tcp.nodelay Parameter in protocol.ora

By default, Oracle NET 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 Oracle NET 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, Oracle NET 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 Oracle NET clients.

On the database server, the automatic_ipc parameter should only be used in cases where an Oracle NET connection must be made to the local database. If no local database connections are needed or required, put this parameter to off, and all Oracle NET 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.

The TDU (transport data unit) is the default packet size used within Oracle NET 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 Multi-Threaded 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.

For Oracle version prior to 9i, the database automatically registered instances in the listener.ora file unless you took 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 Oracle NET 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.


This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter