Network Monitoring Experimentations 6

The last Network Monitoring Experimentations article encountered an unexpected snag – a SQL*Net optimization decreased the average number of bytes needed to transmit each row from about 889 bytes to roughly 86 bytes. While the SQL*Net optimization is great from a performance standpoint (see the summary at the end of this article), it caused problems for some of the intended analysis in the previous article. So, for a proper comparison, we will slightly change the inserted data when creating table T6 so that column C2 stores unique values (two random letters followed by 98 letter A characters):

We will take the script used in the previous Network Monitoring blog article and modify it to point to the new table T6, and then append the original script onto the end of the modified script. The modified portion of the script that accesses table T6 looks like this:

Just to keep things interesting, the laptop used in the previous article is now connected through an 802.11N wireless connection (gigabit uplink to the server), rather than an 802.11G wireless connection. So, what is the output of the new combined script? Once again, the server is running Oracle Database 11.2.0.1 and the client is a dual core 2.0GHz Core 2 Duo laptop (the client CPU may be a critical factor).

At an array fetch size of 10, for table T5 the client is receiving about 90.13MB (855.35MB for table T6), and at an array fetch size of 5,000 the client is receiving about 81.95MB (847.01MB for table T6). Incidentally, the client’s CPU utilization fluctuated between 50% and 99% utilization, indicating that the client’s dual core CPU may have played a limiting factor in the transfer speed.

—-

You must be wondering what kind of an impact the 802.11N wireless had on the overall transfer time. During several of the transfers the Windows Vista Resource Meter indicated that the transfer speed was between 45Mb/s and 48Mb/s with the maximum network transfer capacity indicated as 33% utilized (this upper limit of maximum network transfer capacity did not change when connected to an 802.11G network).

The following execution statistics show the output when the same laptop was connected to the network on a 100Mb/s connection (the maximum supported by the laptop):

The above output indicates that the lower latency 100Mb/s wired connection did decrease the transfer time so that the transfer of result set from table T6 (no SQL*Net compression) – the array fetch setting of 10 required roughly the same amount of time as the transfer of the result set from table T5 (with SQL*Net compression) when the laptop was connected wirelessly. The CPU utilization of the client also dropped slightly – possibly because the laptop no longer needed to perform the WPA2 encryption/decryption of the network packets (the wireless network is protected by WPA2 encryption).

—-

But what about a gigabit connection – would that help? Unfortunately, the laptop does not support gigabit network speeds. With the wired connection topping out at about 83Mb/s to 87Mb/s and the CPU utilization running as high as it was, could we actually achieve a faster transfer speed? To find out, we will switch to a slower desktop computer that is running a 3.8GHz Pentium 4 CPU with hyperthreading enabled. This client, like the server, has a gigabit network card and probably supports CPU offloading of the TCP/IP checksums. So, how well will this second client perform with our test script?

The above shows that the older client computer with the gigabit network card continued to decrease the transfer time up to the array fetch size of 1,000, and then the time increased slightly with the jump to the array fetch size of 5,000.

Just how fast was the transfer speed on the slower client computer? I monitored the performance using a program called NetPerSec which was developed by PC Magazine several years ago. Keep an eye on the right side of the graphs for the current transfer speeds.

ARRAYSIZE 10 – roughly 135.3Mb/s receive, 3.4Mb/s send:

ARRAYSIZE 100 – roughly 275.0Mb/s receive, 5.8Mb/s send:

ARRAYSIZE 500 – roughly 295.6Mb/s receive, 5.8Mb/s send:

ARRAYSIZE 1000 – roughly 295.9Mb/s receive, 5.8Mb/s send:

ARRAYSIZE 5000 – roughly 294.9Mb/s receive, 5.7Mb/s send:

—

Will it make any difference if we completely eliminate the network – running the SQL statement directly on the server?

The Windows Vista client has a built in Resource Monitor that helps when it is necessary to associate CPU usage, disk performance, network transfer speed, and memory utilization. The following screen capture was generated during the 802.11G transfer test with the array fetch size set to 500:

—

The Windows 7 client also has a built in Resource Monitor that helps when it is necessary to associate CPU usage, disk performance, network transfer speed, and memory utilization. The following screen capture was generated during the Netbook N transfer test with the array fetch size set to 500 – notice the CPU usage for the System Interrupts is shown as consuming 30% of the available CPU time. However, because the netbook has a single core hyperthreaded CPU, the actual utilization for System Interrupts might actually be twice as high (60%) due to hyperthreading simulating the existence of a second CPU:

—

While the above tests may have limited value for your specific project, the tests do show the potential impact of SQL*Net compression, the performance impact of different network communication links, and the potential impact the network communication has on the client’s CPU utilization.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: