Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

 

Oracle Network Tuning

Oracle databases are often shared across dispersed geographical locations, so it is imperative that the Oracle professional comprehend how database performance is affected by network communications.  In response to this, Oracle provides the Transparent Network Substrate (TNS), which allows distributed communications between databases.

 

As a distributed protocol, the TNS allows for transparent database communications between remote systems.  The TNS serves the physical communications between the remote servers and acts as an insulator between Oracle's logical data requests. This allows the network administrator to control much of the network performance tuning; however, it also subsequently leaves the Oracle administrator little control over the network settings that can affect overall database performance as shown in Figure 12.16.

 

Figure 12-16: The Oracle*Net architecture

 

By using some network parameter settings, several of which are presented in this chapter, the performance of distributed transactions can be improved. This review of the network tuning parameters should indicate that 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 such as size and frequency of packet transmission.

 

The parameters within the sqlnet.ora, tnsnames.ora, and protocol.ora files can also be used to change the configuration and size of TCP packets. The setting for these parameters can have a profound impact on the underlying network transport layer’s ability to improve the throughput of all Oracle transactions.

 

Oracle*Net is a layer in the OSI model that resides above the network specific protocol stack. Oracle*Net prohibits the Oracle administrator from tuning Oracle network parameters to improve network performance.

 

Oracle*Net takes the data and gives it to the protocol stack for transmission in response to a data request.  The protocol stack then creates a packet from this data and transmits it over the network.  Passing data to the protocol stack is Oracle*Net's sole task so there is little allowance for the DBA to improve network performance.

 

The DBA does have the ability to control the frequency and size of network packets. Oracle offers a number of tools that are used to change packet frequency and size.  Changing the refresh interval for a snapshot to ship larger amounts of data at less frequent intervals is a simple example.

 

Using several parameters, Oracle*Net connections between servers can be tuned; however, network tuning is outside the scope of Oracle and a qualified network administrator should be consulted for tuning the network.  By using settings contained in the following parameter files, the frequency and size of packets shipping across the network can be impacted:

 

FILE

PARAMETER

protocol.ora

tcp.nodelay  

sqlnet.ora  

automatic_ipc

sqlnet.ora

break_poll_skip

tnsnames.ora

SDU and TDU  

listener.ora

SDU  , TDU  , and queuesize

Table 12.2:  Oracle network parameter locations

 

While limited in power, the settings for these parameters can still make a huge difference in the performance of distributed Oracle databases.  A closer look at these parameters is warranted.

 

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

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

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

 

   

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


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks