Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Tests
The following sections describe the test conditions and methodology.
Test Conditions
LAN and WAN.
Defaulted parameters by varying values of parameters such as SDU, TDU, and array size.
Set TDU to 1024 and changed SDU to 1 kilobyte, 2 kilobytes, 4 kilobytes, and 8 kilobytes.
Set TDU to 2048 and changed SDU to 1 kilobyte, 2 kilobytes, 4 kilobytes, and 8 kilobytes.
Changed the array_size parameter to 1, 2, 5, 10, 25, 50, 100, 150, and 1000
Client application—SQL*Plus 3.3.2.0.2
Client-side—SQL*Net 2.3.2.1.4; server-side—SQL*Net 2.3.2.1.0.
Server operating system—Solaris™ 2.5 operating environment
Client operating system—Windows NT 4.0
RDBMS version 7.3.2.1.0
Created three tables:
stats1 (4096 rows, 20 bytes/row, VARCHAR2)
stats2 (4096 rows, 100 bytes/row, VARCHAR2)
eoug (410 rows, LONG).
Disabled screen output with set termout off;
Enabled timing with set timing on;
Turned on SQL tracing with alter session set sql_trace=true;
Network protocols—TCP/IP, SPX/IPX, and DECNet.
Testing on local area network (LAN)—client/server on the same subnet.
Testing on wide area network (WAN)—client/server on a different subnet.
Repeated each test three times and averaged the results.
Used Ethernet as the LLP for all tests.
Test Methodology
Since the size of the SDU parameter is negotiated between the server and the client, the size of the parameter on the server side was set to 8 kilobytes and did not change as the client SDU was varied from 1 kilobyte, 2 kilobytes, 4 kilobytes and 8 kilobytes.
Initiate the following command from the client and capture the transmitting time (tcνs) from client to server using the LAN analyzer.
$ sqlplus system/manager@test1; SQL>
Fetch/retrieve data from the server table using the following command.:
SQL>select * from stats1;
Capture the data transmission from server to client and the transmitting time (tsνc) using a LAN analyzer. The total connect time is as follows:
total_connect_time = tc_s + ts_c
On the client, the array size and TDU parameters are constants and the SDU parameter is changed to 1 kilobyte, 2 kilobyte, 4 kilobyte, and 8 kilobyte. The following section describes the results obtained.
Results/Observations
When the TDU and array size parameter were set to default values, the following results were observed with respect to connect time and packet size.
Changing the SDU parameter significantly improved connect time (tsνc). This improvement can be attributed to the facts that the amount of data being retrieved remained constant, but the size of the packets was varied.
TABLE 2 Table Stats2 Row size = 100, Batch size = 1500 bytes, TDU = default size, Array size = 15 (default), and client SDU
)
TABLE 3 Table Stats2 Row size = 100, Batch size = 1500 bytes, TDU = 1024, Array size = 15 (default), and client SDU
)
TABLE 4 Table Stats2 Row Size = 100, Batch size = 1500 bytes, TDU = 2048, Array size = 15 (default), and client SDU
)
Based on the preceding three performance curves, the best performance is at a TDU size of 2048 bytes. Also, further increments of SDU after 2 kilobytes have negligible effect on the connect time.
The preceding three graphs provide a performance comparison between the three network protocols in a client/server environment. By calibrating all the variables discussed in this paper, it was observed that the protocol that contains the largest buffer size provides the best performance. Protocols SPX/IPX and DECNet maximum buffer sizes are 576 bytes and TCP/IP is 1500 bytes. The SDU parameter is always greater than SPX/IPX and DECNet buffer sizes, so changing SDU parameter did not improve connect time.
It can also be observed from the curve in TABLE 3 that when SDU is much greater than TDU, no performance gains are realized as the TDU becomes a bottleneck, causing numerous smaller packets to be generated. Because these connection-oriented protocols generate a corresponding number of ACK packets, the network traffic increases proportionally.
TABLE 5 Table Stats1 Row size = 20, Batch size = 300 bytes, TDU = default size, Array size = 15 (default), and client
)
TABLE 6 Table Stats1 Row size = 20, Batch size = 300 bytes, TDU = 1024, Array size = 15 (default), and client SDU
)
TABLE 7 Table Stats1 Row size = 20, Batch size = 300 bytes, TDU = 2048, Array size = 15 (default), and client SDU
)
According to the preceding test results, the batch size was 300 bytes (300 << SDU) and not enough data filled the SQL*Net and/or the UNP buffers. Therefore, the connect time was consistent for each protocol and the performance of each protocol matched closely.
Relationship Between Long Data Type and SDU
Packet size = 131 bytes Datatype LONG
)
According to the preceding test results, if datatype is LONG, synchronizing buffer sizes has no effect on performance. Also, changing SDU, TDU, or array size has no effect on packet sizes. The packet size was approximately 131 bytes. This may be the reason why the tuning parameters have no effect. Further studies are necessary to investigate this. Repeating the same test for IPX/SPX and DECNet protocols produced similar results.
TABLE 8 Query Time
)
When retrieving an arbitrary number of rows, a smaller array_size parameter reduces fetch time but increases overhead. A larger array_size parameter increases fetch time but reduces overhead. Overall, a larger array size produces better results.