(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)
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):
CREATE TABLE T6 (
C1 VARCHAR2(10),
C2 VARCHAR2(100),
PRIMARY KEY (C1));
INSERT INTO T6 NOLOGGING
SELECT
'A'||TO_CHAR(ROWNUM,'0000000'),
RPAD(DBMS_RANDOM.STRING('A',2),100,'A')
FROM
DUAL
CONNECT BY
LEVEL<=1000000;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T6',CASCADE=>TRUE)
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:
SET TIMING ON SET AUTOTRACE TRACEONLY STATISTICS SET ARRAYSIZE 10 SELECT /*+ USE_NL(T61 T62 T63 T64 T65 T66 T67 T68) */ T61.C1, T61.C2, T62.C1, T62.C2, T63.C1, T63.C2, T64.C1, T64.C2, T65.C1, T65.C2, T66.C1, T66.C2, T67.C1, T67.C2, T68.C1, T68.C2 FROM T6 T61, T6 T62, T6 T63, T6 T64, T6 T65, T6 T66, T6 T67, T6 T68 WHERE T61.C1=T62.C1 AND T61.C1=T63.C1 AND T61.C1=T64.C1 AND T61.C1=T65.C1 AND T61.C1=T66.C1 AND T61.C1=T67.C1 AND T61.C1=T68.C1 AND T61.C1 BETWEEN 'A 0001000' AND 'A 1000000'; host sleep 5 SET ARRAYSIZE 50 SELECT /*+ USE_NL(T61 T62 T63 T64 T65 T66 T67 T68) */ T61.C1, T61.C2, T62.C1, T62.C2, T63.C1, T63.C2, T64.C1, T64.C2, T65.C1, T65.C2, T66.C1, T66.C2, T67.C1, T67.C2, T68.C1, T68.C2 FROM T6 T61, T6 T62, T6 T63, T6 T64, T6 T65, T6 T66, T6 T67, T6 T68 WHERE T61.C1=T62.C1 AND T61.C1=T63.C1 AND T61.C1=T64.C1 AND T61.C1=T65.C1 AND T61.C1=T66.C1 AND T61.C1=T67.C1 AND T61.C1=T68.C1 AND T61.C1 BETWEEN 'A 0001000' AND 'A 1000000'; host sleep 5 SET ARRAYSIZE 100 SELECT /*+ USE_NL(T61 T62 T63 T64 T65 T66 T67 T68) */ T61.C1, T61.C2, T62.C1, T62.C2, T63.C1, T63.C2, T64.C1, T64.C2, T65.C1, T65.C2, T66.C1, T66.C2, T67.C1, T67.C2, T68.C1, T68.C2 FROM T6 T61, T6 T62, T6 T63, T6 T64, T6 T65, T6 T66, T6 T67, T6 T68 WHERE T61.C1=T62.C1 AND T61.C1=T63.C1 AND T61.C1=T64.C1 AND T61.C1=T65.C1 AND T61.C1=T66.C1 AND T61.C1=T67.C1 AND T61.C1=T68.C1 AND T61.C1 BETWEEN 'A 0001000' AND 'A 1000000'; host sleep 5 SET ARRAYSIZE 500 SELECT /*+ USE_NL(T61 T62 T63 T64 T65 T66 T67 T68) */ T61.C1, T61.C2, T62.C1, T62.C2, T63.C1, T63.C2, T64.C1, T64.C2, T65.C1, T65.C2, T66.C1, T66.C2, T67.C1, T67.C2, T68.C1, T68.C2 FROM T6 T61, T6 T62, T6 T63, T6 T64, T6 T65, T6 T66, T6 T67, T6 T68 WHERE T61.C1=T62.C1 AND T61.C1=T63.C1 AND T61.C1=T64.C1 AND T61.C1=T65.C1 AND T61.C1=T66.C1 AND T61.C1=T67.C1 AND T61.C1=T68.C1 AND T61.C1 BETWEEN 'A 0001000' AND 'A 1000000'; host sleep 5 SET ARRAYSIZE 1000 SELECT /*+ USE_NL(T61 T62 T63 T64 T65 T66 T67 T68) */ T61.C1, T61.C2, T62.C1, T62.C2, T63.C1, T63.C2, T64.C1, T64.C2, T65.C1, T65.C2, T66.C1, T66.C2, T67.C1, T67.C2, T68.C1, T68.C2 FROM T6 T61, T6 T62, T6 T63, T6 T64, T6 T65, T6 T66, T6 T67, T6 T68 WHERE T61.C1=T62.C1 AND T61.C1=T63.C1 AND T61.C1=T64.C1 AND T61.C1=T65.C1 AND T61.C1=T66.C1 AND T61.C1=T67.C1 AND T61.C1=T68.C1 AND T61.C1 BETWEEN 'A 0001000' AND 'A 1000000'; host sleep 5 SET ARRAYSIZE 5000 SELECT /*+ USE_NL(T61 T62 T63 T64 T65 T66 T67 T68) */ T61.C1, T61.C2, T62.C1, T62.C2, T63.C1, T63.C2, T64.C1, T64.C2, T65.C1, T65.C2, T66.C1, T66.C2, T67.C1, T67.C2, T68.C1, T68.C2 FROM T6 T61, T6 T62, T6 T63, T6 T64, T6 T65, T6 T66, T6 T67, T6 T68 WHERE T61.C1=T62.C1 AND T61.C1=T63.C1 AND T61.C1=T64.C1 AND T61.C1=T65.C1 AND T61.C1=T66.C1 AND T61.C1=T67.C1 AND T61.C1=T68.C1 AND T61.C1 BETWEEN 'A 0001000' AND 'A 1000000';
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).
Table T6:
SQL> SET ARRAYSIZE 10 Elapsed: 00:04:49.97 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 112906 consistent gets 0 physical reads 0 redo size 896904186 bytes sent via SQL*Net to client 1099259 bytes received via SQL*Net from client 99902 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 50 Elapsed: 00:02:48.73 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 35563 consistent gets 0 physical reads 0 redo size 889871426 bytes sent via SQL*Net to client 220139 bytes received via SQL*Net from client 19982 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 100 Elapsed: 00:02:43.94 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 25896 consistent gets 0 physical reads 0 redo size 888992556 bytes sent via SQL*Net to client 110249 bytes received via SQL*Net from client 9992 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 500 Elapsed: 00:02:34.81 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 18162 consistent gets 0 physical reads 0 redo size 888291260 bytes sent via SQL*Net to client 22337 bytes received via SQL*Net from client 2000 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 1000 Elapsed: 00:02:33.64 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17195 consistent gets 0 physical reads 0 redo size 888205848 bytes sent via SQL*Net to client 11348 bytes received via SQL*Net from client 1001 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 5000 Elapsed: 00:02:32.59 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 16421 consistent gets 0 physical reads 0 redo size 888153854 bytes sent via SQL*Net to client 2548 bytes received via SQL*Net from client 201 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed
At an array fetch size of 10 the client is receiving about 855.35MB, and at an array fetch size of 5,000 the client is receiving about 847.01MB.
Let’s contrast the above with the test for table T5.
Table T5:
SQL> SET ARRAYSIZE 10 Elapsed: 00:02:01.55 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 112906 consistent gets 0 physical reads 0 redo size 94508146 bytes sent via SQL*Net to client 1099259 bytes received via SQL*Net from client 99902 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 50 Elapsed: 00:00:56.37 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 35564 consistent gets 0 physical reads 0 redo size 87635026 bytes sent via SQL*Net to client 220139 bytes received via SQL*Net from client 19982 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 100 Elapsed: 00:00:40.77 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 25896 consistent gets 0 physical reads 0 redo size 86775886 bytes sent via SQL*Net to client 110249 bytes received via SQL*Net from client 9992 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 500 Elapsed: 00:00:27.31 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 18162 consistent gets 0 physical reads 0 redo size 86088574 bytes sent via SQL*Net to client 22337 bytes received via SQL*Net from client 2000 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 1000 Elapsed: 00:00:27.03 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17195 consistent gets 0 physical reads 0 redo size 86002660 bytes sent via SQL*Net to client 11348 bytes received via SQL*Net from client 1001 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 5000 Elapsed: 00:00:24.64 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 16421 consistent gets 0 physical reads 0 redo size 85933880 bytes sent via SQL*Net to client 2548 bytes received via SQL*Net from client 201 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed
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):
Table T6:
SQL> SET ARRAYSIZE 10 Elapsed: 00:02:09.54 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 112906 consistent gets 0 physical reads 0 redo size 896904186 bytes sent via SQL*Net to client 1099260 bytes received via SQL*Net from client 99902 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 50 Elapsed: 00:01:33.00 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 35563 consistent gets 0 physical reads 0 redo size 889871426 bytes sent via SQL*Net to client 220140 bytes received via SQL*Net from client 19982 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 100 Elapsed: 00:01:29.23 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 25896 consistent gets 0 physical reads 0 redo size 888992556 bytes sent via SQL*Net to client 110250 bytes received via SQL*Net from client 9992 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 500 Elapsed: 00:01:25.08 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 18162 consistent gets 0 physical reads 0 redo size 888291260 bytes sent via SQL*Net to client 22338 bytes received via SQL*Net from client 2000 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 1000 Elapsed: 00:01:24.84 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17195 consistent gets 0 physical reads 0 redo size 888205848 bytes sent via SQL*Net to client 11349 bytes received via SQL*Net from client 1001 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 5000 Elapsed: 00:01:24.83 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 16421 consistent gets 0 physical reads 0 redo size 888153854 bytes sent via SQL*Net to client 2549 bytes received via SQL*Net from client 201 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed
Table T5:
SQL> SET ARRAYSIZE 10 Elapsed: 00:00:56.28 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 112906 consistent gets 0 physical reads 0 redo size 94508146 bytes sent via SQL*Net to client 1099260 bytes received via SQL*Net from client 99902 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 50 Elapsed: 00:00:28.36 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 35564 consistent gets 0 physical reads 0 redo size 87635026 bytes sent via SQL*Net to client 220140 bytes received via SQL*Net from client 19982 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 100 Elapsed: 00:00:24.19 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 25896 consistent gets 0 physical reads 0 redo size 86775886 bytes sent via SQL*Net to client 110250 bytes received via SQL*Net from client 9992 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 500 Elapsed: 00:00:18.37 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 18162 consistent gets 0 physical reads 0 redo size 86088574 bytes sent via SQL*Net to client 22338 bytes received via SQL*Net from client 2000 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 1000 Elapsed: 00:00:17.95 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17195 consistent gets 0 physical reads 0 redo size 86002660 bytes sent via SQL*Net to client 11349 bytes received via SQL*Net from client 1001 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 5000 Elapsed: 00:00:17.67 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 16421 consistent gets 0 physical reads 0 redo size 85933880 bytes sent via SQL*Net to client 2549 bytes received via SQL*Net from client 201 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed
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?
Table T6:
SQL> SET ARRAYSIZE 10 Elapsed: 00:00:56.29 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 112906 consistent gets 0 physical reads 0 redo size 896904186 bytes sent via SQL*Net to client 1099260 bytes received via SQL*Net from client 99902 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 50 Elapsed: 00:00:30.12 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 35563 consistent gets 0 physical reads 0 redo size 889871426 bytes sent via SQL*Net to client 220140 bytes received via SQL*Net from client 19982 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 100 Elapsed: 00:00:27.75 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 25896 consistent gets 0 physical reads 0 redo size 888992556 bytes sent via SQL*Net to client 110250 bytes received via SQL*Net from client 9992 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 500 Elapsed: 00:00:26.21 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 18162 consistent gets 0 physical reads 0 redo size 888291260 bytes sent via SQL*Net to client 22338 bytes received via SQL*Net from client 2000 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 1000 Elapsed: 00:00:25.87 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17195 consistent gets 0 physical reads 0 redo size 888205848 bytes sent via SQL*Net to client 11349 bytes received via SQL*Net from client 1001 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 5000 Elapsed: 00:00:26.71 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 16421 consistent gets 0 physical reads 0 redo size 888153854 bytes sent via SQL*Net to client 2549 bytes received via SQL*Net from client 201 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed
The above showed that the gigabit connection helped significantly. The gigabit performance results for table T5 follow.
Table T5:
SQL> SET ARRAYSIZE 10 Elapsed: 00:00:47.17 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 112906 consistent gets 0 physical reads 0 redo size 94508146 bytes sent via SQL*Net to client 1099260 bytes received via SQL*Net from client 99902 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 50 Elapsed: 00:00:19.45 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 35564 consistent gets 0 physical reads 0 redo size 87635026 bytes sent via SQL*Net to client 220140 bytes received via SQL*Net from client 19982 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 100 Elapsed: 00:00:16.28 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 25896 consistent gets 0 physical reads 0 redo size 86775886 bytes sent via SQL*Net to client 110250 bytes received via SQL*Net from client 9992 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 500 Elapsed: 00:00:12.98 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 18162 consistent gets 0 physical reads 0 redo size 86088574 bytes sent via SQL*Net to client 22338 bytes received via SQL*Net from client 2000 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 1000 Elapsed: 00:00:12.68 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17195 consistent gets 0 physical reads 0 redo size 86002660 bytes sent via SQL*Net to client 11349 bytes received via SQL*Net from client 1001 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 5000 Elapsed: 00:00:13.29 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 16421 consistent gets 0 physical reads 0 redo size 85933880 bytes sent via SQL*Net to client 2549 bytes received via SQL*Net from client 201 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed
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?
Table T6:
SQL> SET ARRAYSIZE 10 Elapsed: 00:00:21.73 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 112906 consistent gets 0 physical reads 0 redo size 906694810 bytes sent via SQL*Net to client 1099420 bytes received via SQL*Net from client 99902 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 50 Elapsed: 00:00:14.92 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 35563 consistent gets 0 physical reads 0 redo size 891829890 bytes sent via SQL*Net to client 220300 bytes received via SQL*Net from client 19982 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 100 Elapsed: 00:00:14.25 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 25896 consistent gets 0 physical reads 0 redo size 889972000 bytes sent via SQL*Net to client 110410 bytes received via SQL*Net from client 9992 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 500 Elapsed: 00:00:13.47 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 18162 consistent gets 0 physical reads 0 redo size 888487488 bytes sent via SQL*Net to client 22498 bytes received via SQL*Net from client 2000 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 1000 Elapsed: 00:00:13.43 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17195 consistent gets 0 physical reads 0 redo size 888304174 bytes sent via SQL*Net to client 11509 bytes received via SQL*Net from client 1001 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 5000 Elapsed: 00:00:13.46 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 16421 consistent gets 0 physical reads 0 redo size 888173808 bytes sent via SQL*Net to client 2709 bytes received via SQL*Net from client 201 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed
—
Table T5:
SQL> SET ARRAYSIZE 10 Elapsed: 00:00:15.60 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 112906 consistent gets 0 physical reads 0 redo size 104198871 bytes sent via SQL*Net to client 1099420 bytes received via SQL*Net from client 99902 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 50 Elapsed: 00:00:09.40 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 35564 consistent gets 0 physical reads 0 redo size 89573511 bytes sent via SQL*Net to client 220300 bytes received via SQL*Net from client 19982 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 100 Elapsed: 00:00:10.06 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 25896 consistent gets 0 physical reads 0 redo size 87745341 bytes sent via SQL*Net to client 110410 bytes received via SQL*Net from client 9992 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 500 Elapsed: 00:00:07.14 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 18162 consistent gets 0 physical reads 0 redo size 86282805 bytes sent via SQL*Net to client 22498 bytes received via SQL*Net from client 2000 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 1000 Elapsed: 00:00:06.77 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17195 consistent gets 0 physical reads 0 redo size 86099988 bytes sent via SQL*Net to client 11509 bytes received via SQL*Net from client 1001 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed --- SQL> SET ARRAYSIZE 5000 Elapsed: 00:00:06.66 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 16421 consistent gets 0 physical reads 0 redo size 85953635 bytes sent via SQL*Net to client 2709 bytes received via SQL*Net from client 201 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999001 rows processed
Running the test directly on the server had a significant impact on the execution time.
—
Summarizing the performance results:
802.11G – Laptop and wireless access point set to 802.11G (54Mb/s)
802.11N – Laptop and wireless access point set to 802.11N speed
Netbook N – Netbook with Atom N450 CPU and 802.11N speed
100Mb/s – Laptop with wired 100Mb/s network connection
1000Mb/s – Desktop with wired gigabit network connection
On Server – Executed directly on the server’s console (no network)
—
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.
Charles Hooper's Oracle Notes 
Recent Comments