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.

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:

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

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