Optimize Oracle network settings

Oracle databases are widely used in different regions. Therefore, professional Oracle staff must understand how network connections affect database performance. The TNS provided by Oracle allows communication in each database.

The TNS server is considered as the insulator in the Oracle Logical Data request and the server between the remote server. Similarly, network administrators have the ability to control network performance tuning, But Oracle administrators do not have the right to control network settings that affect database performance.

The following important settings can be used to change the performance of Distributed Transaction elements. Including sqlnet. ora, tnsnames. ora, and protocol. parameters in the ora file, which can be used to change settings and the size of the TCP packet, and adjusting these parameters can affect the fundamental network transport layer to change the throughput of the Oracle transaction element.

As mentioned earlier, Oracle does not allow Oracle professionals to adjust the fundamental network transport layer, and most network traffic cannot be changed in Oracle environments. Remember, the Oracle network is a network layer in the OSI model, which exists on the network protocol stack.

However, the frequency and size of the network information package can be controlled by Oracle DBA. Oracle has a large number of tools to change the frequency and size of information packages.

The Oracle network connection between servers can be tuned using several parameters. However, you must remember that network tuning must be outside Oracle and a qualified network administrator must have been negotiated before the network can be tuned. The frequency and size of the information package can be affected by the settings of the following parameter files.

• Sqlnet. ora Server File -- automatic_ipc Parameter

• Sqlnet. ora client file-break_poll_skip Parameter

• Tnsnames. ora and listener. ora file-SDU and TDU Parameters

• Protocol. ora file-tcp. nodelay Parameter

These tuning parameters only affect the performance of the Oracle network layer. Let's explain them in detail and see how they are adjusted to change the Oracle network throughput.

Before data transmission, the Oracle network waits until the buffer is filled up by default. Therefore, requirements cannot be immediately sent to their destinations. This is common when a large amount of data is sent from one place to another. The Oracle network cannot transmit information packets until the buffer is full. Adding a protocol. ora file and specifying a tcp. nodelay stop buffer delay can fix this defect.

The protocol. ora file can be specified to describe all TCP/IP implementations without data buffering. This parameter can be used on the client and server. The protocol. ora statement is as follows:

Tcp. nodelay = yes

By specifying this parameter, the TCP buffer is skipped so that each request can be quickly sent. However, please remember that the network traffic increases due to smaller and more frequent transfer of information packets, which will reduce the network speed.

Tcp. nodelay

The tcp. nodelay parameter is used only when TCP encounters a timeout setting. Setting tcp. nodelay when there is a large amount of traffic between database servers can cause a huge change in performance.

The automatic_ip parameter in the sqlnet. ora File

The automatic_ipc parameter goes back to the network layer, so it can accelerate local connection to the database. When automatic_ipc = on, the Oracle network checks whether the local database is defined as the same. If yes, the network layer is avoided when the connection is directly converted to the local ICP connection. This is very useful on database servers, but it is useless for Oracle network users.

When the Oracle network connection must be treated as a local database, the automatic_ipc parameter should only be used on the database server. If you do not need a local connection, set this parameter to off. By using this setting, all Oracle networks can improve their performance.

SDU and tdu in the tnsnames. ora File

The SDU and TDU parameters are located in the tnsnames. ora and listener. ora files. SDU specifies the size of the information packet sent to the network. Theoretically, SDU should not exceed the maximum MTU size. MTU depends on a real network implementation. It is a constant value. The position of SDU in Oracle is the same as that in MTU.

In the Oracle network, the size of the TDU information package is a default value. Ideally, the TDU parameter should be defined as a double of the SDU parameter. The default values of SDU and TDU are 2048 and the maximum value is 32767 bytes.

The following guidelines for using SDU and TDU:

• SDU should never be larger than TDU, because you will waste network resources.

• If your users access the Internet through the modem, you should set the SDU and TDU values to a smaller value, because frequent resends often occur on the modem line.

• In a broadband connection, you should set the value of your network SDU and TDU to the same size as that of MTU. In the standard Ethernet, the default MTU size is 1514 bytes. In the standard token ring network, the default MTU size is 4202.

• If MTS is used, you must use the appropriate MTU and TDU settings to set mts_dispatchers.

Between Hosts, SDU and TDU are the direct functions of the connection speed. For example, T1, set SDU = TDU = MTU. It is best to use SDU and TDU with a small value for the slow-speed modem.

Queuesize parameter in the listener. ora File

When Oracle is establishing a connection, the connection is determined by the queuesize parameter. This parameter is only used by large-capacity databases. The size of the connection that is expected to occur at the same time must be equal to that of the queuesize parameter. Here is an example of using this parameter in the listener.org file:

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

One disadvantage of this parameter is that it will allocate parameters for the expected request, so it will use more memory and resources. If you need to use a large-capacity connection, you should consider using MTS. At the same time, note that some UNIX versions do not allow more than 5 queues, and there are also restrictions on the MTS queue size.

Performance

Although the traffic of most information packages has been tuned, correct Oracle network parameter settings will have a huge impact on the performance of the distributed system. Oracle professionals must fully understand the performance and optimization of these important parameters.

(