Network Monitoring Experimentations 6

14 05 2010

May 14, 2010

(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.


Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 141 other followers

%d bloggers like this: