Array Processing, SQL*Net RoundTrips and consistent gets

When fetching large datasets (even if the SQLPlus Client is on the *same* server as the Database), it is important to use the right ARRAYSIZE.

Here I demonstrate the impact of ARRAYSIZE on
a. Total volume of bytes transferred from the Server to the Client
b. Number of SQL*Net round-trips
c. Number of 'consistent gets' (and, even, a slightly higher CPU usage for the higher logical reads, but which isn't presented by SET AUTOTRACE )

I shall be querying a table with 1.6million rows. (This has the same structure and multiple copies of the contents of DBA_OBJECTS). I avoid using the index -- I am fetching ALL rows and ALL columns.

ora10204>sqlplus hemant/hemant

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 5 23:26:25 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> REM Start with the default arrasize
SQL> show arraysize
arraysize 15
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> select /*+ FULL (st) */ * from source_table st;

1620512 rows selected.


Statistics
----------------------------------------------------------
   1  recursive calls
   0  db block gets
128823  consistent gets
11211  physical reads
 116  redo size
83292501  bytes sent via SQL*Net to client
1188866  bytes received via SQL*Net from client
108036  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
1620512  rows processed

SQL> set timing on
SQL> select /*+ FULL (st) */ * from source_table st;

1620512 rows selected.

Elapsed: 00:00:42.22

Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
128822  consistent gets
11436  physical reads
   0  redo size
83292501  bytes sent via SQL*Net to client
1188866  bytes received via SQL*Net from client
108036  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
1620512  rows processed

SQL>   

This was 83.292million bytes sent by the server to my SQLPlus client via SQLNet in 108,036 round-trips. The query did 128,822 'consistent gets'.

Now, I switch to an ARRAYSIZE of 100. Thus, the SQLPlus client will "ask" Oracle for 100 rows at each fetch.

SQL> REM Now I set a larger arraysize
SQL> SET ARRAYSIZE 100
SQL> l
1* select /*+ FULL (st) */ * from source_table st
SQL> /

1620512 rows selected.

Elapsed: 00:00:16.73

Statistics
----------------------------------------------------------
    0  recursive calls
    0  db block gets
38238  consistent gets
11434  physical reads
    0  redo size
66487794  bytes sent via SQL*Net to client
178747  bytes received via SQL*Net from client
16207  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
1620512  rows processed

SQL> l
1* select /*+ FULL (st) */ * from source_table st
SQL> /

1620512 rows selected.

Elapsed: 00:00:16.68

Statistics
----------------------------------------------------------
    0  recursive calls
    0  db block gets
38238  consistent gets
11414  physical reads
    0  redo size
66487794  bytes sent via SQL*Net to client
178747  bytes received via SQL*Net from client
16207  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
1620512  rows processed

SQL>  

Now, the server has to send 66.488million bytes to me in 16,207 round-trips. Since my ARRAYSIZE has increased 7 fold, the number of round-trips has dropped to almost 1/7th. More rows are being sent by the DB server in each SQL*Net packet. Even 'consistent gets' has dropped from 128,822 to 38,238. This is because fewer Fetch calls (which we would have seen in a tkprof of trace file with SQL_TRACE enabled) have had to be executed, and the number of blocks being re-read has declined. Execution time has also improved.

What about the other extreme ? An ARRAYSIZE of 2 ?

SQL> REM Now I set a very small arraysize
SQL> SET ARRAYSIZE 2
SQL> select /*+ FULL (st) */ * from source_table st
2  /

1620512 rows selected.

Elapsed: 00:03:10.08

Statistics
----------------------------------------------------------
     0  recursive calls
     0  db block gets
821428  consistent gets
 11721  physical reads
     0  redo size
211798944  bytes sent via SQL*Net to client
8913297  bytes received via SQL*Net from client
810257  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
1620512  rows processed

SQL> l
1* select /*+ FULL (st) */ * from source_table st
SQL> /

1620512 rows selected.

Elapsed: 00:03:46.49

Statistics
----------------------------------------------------------
     0  recursive calls
     0  db block gets
821428  consistent gets
 11466  physical reads
     0  redo size
211798944  bytes sent via SQL*Net to client
8913297  bytes received via SQL*Net from client
810257  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
1620512  rows processed


SQL> 

With a very small ARRAYSIZE, the server sent 211.799MB in 810,257 round-trips ! The number of 'consistent gets' has jumped to 821,428 !

So we have :

ARRAYSIZE     MB sent     RoundTrips     consistent    query
                                       gets          time
2            211.799     810,257        821,428       190sec
15            83.292     108,036        128,822        42sec
100           66.488      16,207        38,238         17sec         

For some other tests with PAGESIZE and LINESIZE, see my previous post "Tuning Very Large SELECTs in SQLPlus"