Problem Statement:
The select operation is too slow compared with expected value.
Session Data Unit(SDU):
Under typical database configuration, Oracle Net encapsulates data into buffers the size of the session data unit (SDU) before sending the data across the network. Oracle Net sends each buffer when it is filled, flushed, or when an application tries to read data.
The SDU size you choose should be 70 bytes larger than the predominant message size, as long as the maximum SDU size is not exceeded. The SDU size can range from 512 bytes to 32767 bytes. 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.
The actual SDU size used is negotiated between the client and the server at connect time and will be the smaller of the client and server values.
SEND_BUF_SIZE and RECV_BUF_SIZE
Reliable network protocols like TCP/IP buffer data into send and receive buffers while sending and receiving to or from lower and upper layer protocols. The sizes of these buffers affect network performance, as these buffer sizes influence flow control decisions.
Other performance related setting:
1. DEFAULT_SDU_SIZE and SDU in specific client/server instance
2. SEND_BUF_SIZE
3. RECV_BUF_SIZE
4. NET_ASYNC_IO
5. SDP_ASYNC_IO
Testing cases about RECV_BUF_SIZE:
Following using a sample to verify the parameter RECV_BUF_SIZE:
create table tab(
text1 char(50) not null,
text2 char(50),
primary key(text1)
);
Table was provisioned with 10000 data rows.
Using sqlplus to execute "select * from tab" in a client.
time sqlplus $DBLOGIN >/dev/null <
select * from tab;
quit;
EOF
(Client and server reside on different Linux labs, using TCP/IP connection)
Following table is test result
+-------------------------+------------------------------------------------------------+
|RECV_BUF_SIZE | TIME |
| | |
| 1024 | real 0m0.70s/user 0m0.13s/sys 0m0.12s
| 512 | real 0m1.00s/user 0m0.15s/sys 0m0.13s
| 256 | real 0m1.76s/user 0m0.14s/sys 0m0.15s
| 240 | real 0m4.80s/user 0m0.14s/sys 0m0.18s
| 239 | real 0m3.49s/user 0m0.16s/sys 0m0.15s
| 238 | real 0m4.01s/user 0m0.15s/sys 0m0.18s
| 237 | real 0m2.90s/user 0m0.15s/sys 0m0.15s
| 236 | real 2m57.76s/user 0m0.21s/sys 0m0.18s
| 235 | real 3m0.24s/user 0m0.20s/sys 0m0.21s
| 230 | real 2m58.03suser 0m0.23s/sys 0m0.16s
| 200 | real 3m9.49s/user 0m0.19s/sys 0m0.20s
--------------------------+-------------------------------------------------------------+
I don't know why there is such a big gap when RECV_BUF_SIZE equals to 236, or 237?
HELP, HELP !!!!