SQL*Net tuning can be done in many ways and it should be dealt-with lot of caution!!
Some of the low risk performance techniques are mentioned below.
The post mainly aims at reducing SQL*Net round trip time.
SQL*Net round trip can be reduced using the following performance tuning options:
1. Setting appropriate SDU Size
2. Use of ArraySize
3. Setting of SEND/RECV Buffers
4. Setting tcp.nodelay
5. Setting QUEUESIZE
Setting SDU Size:
SDU:
SDU is a buffer that Oracle Net uses to place data before transmitting it across the network. Oracle Net sends the data in the buffer either when requested or when it is full.
The idea of SDU is to resize the network packets sent over network, thereby accommodating more data at once and eventually reducing number of trips.
Steps needed to implement
1. Increase SDU if it hasn’t been tuned already. The value ranges from 512 to 32767 bytes
SDU needs to be configured at both Client and the Server. SDU can be configured for particular service using tnsnames.ora and listener.ora as explained below:
To configure the client, set the SDU size in the following places:
sqlnet.ora File
For global configuration on the client side, configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file:
DEFAULT_SDU_SIZE=32767
Connect Descriptors
For a particular connect descriptor, you can override the current settings in the client side sqlnet.ora file. In a connect descriptor, you specify the SDU parameter for a description.
sales.us.acme.com=
(DESCRIPTION=
(SDU=11280)
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com))
)
SDU size applies to all Oracle Net protocols.
To configure the database server, set the SDU size in the following places:
sqlnet.ora File
Configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file:
DEFAULT_SDU_SIZE=32767
If using shared server processes, set the SDU size in the DISPATCHERS parameter as follows:
DISPATCHERS="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp))(SDU=8192))"
If using dedicated server processes for a database that is registered with the listener through static configuration in the listener.ora file, you can override the current setting in sqlnet.ora:
SID_LIST_listener_name=
(SID_LIST=
(SID_DESC=
(SDU=8192)
(SID_NAME=sales)))
2. Set the value of DEFAULT_SDU_SIZE in sqlnet.ora . If the DEFAULT_SDU_SIZE parameter is not configured in the sqlnet.ora file, then the default SDU for the client and a dedicated server is 2048 bytes, while for a shared server the default SDU is 32767 bytes
More to come...
Some of the low risk performance techniques are mentioned below.
The post mainly aims at reducing SQL*Net round trip time.
SQL*Net round trip can be reduced using the following performance tuning options:
1. Setting appropriate SDU Size
2. Use of ArraySize
3. Setting of SEND/RECV Buffers
4. Setting tcp.nodelay
5. Setting QUEUESIZE
Setting SDU Size:
SDU:
SDU is a buffer that Oracle Net uses to place data before transmitting it across the network. Oracle Net sends the data in the buffer either when requested or when it is full.
The idea of SDU is to resize the network packets sent over network, thereby accommodating more data at once and eventually reducing number of trips.
Steps needed to implement
1. Increase SDU if it hasn’t been tuned already. The value ranges from 512 to 32767 bytes
SDU needs to be configured at both Client and the Server. SDU can be configured for particular service using tnsnames.ora and listener.ora as explained below:
To configure the client, set the SDU size in the following places:
sqlnet.ora File
For global configuration on the client side, configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file:
DEFAULT_SDU_SIZE=32767
Connect Descriptors
For a particular connect descriptor, you can override the current settings in the client side sqlnet.ora file. In a connect descriptor, you specify the SDU parameter for a description.
sales.us.acme.com=
(DESCRIPTION=
(SDU=11280)
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com))
)
SDU size applies to all Oracle Net protocols.
To configure the database server, set the SDU size in the following places:
sqlnet.ora File
Configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file:
DEFAULT_SDU_SIZE=32767
If using shared server processes, set the SDU size in the DISPATCHERS parameter as follows:
DISPATCHERS="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp))(SDU=8192))"
If using dedicated server processes for a database that is registered with the listener through static configuration in the listener.ora file, you can override the current setting in sqlnet.ora:
SID_LIST_listener_name=
(SID_LIST=
(SID_DESC=
(SDU=8192)
(SID_NAME=sales)))
2. Set the value of DEFAULT_SDU_SIZE in sqlnet.ora . If the DEFAULT_SDU_SIZE parameter is not configured in the sqlnet.ora file, then the default SDU for the client and a dedicated server is 2048 bytes, while for a shared server the default SDU is 32767 bytes
More to come...
