Home > Oracle, Performance > arraysize in sqlplus

arraysize in sqlplus


When you access the data remotely in sqlplus, SET ARRAYSIZE can improve the performance of data access.   What are the benefits we are getting  by changing the default value for ARRAYSIZE?  Following are the few benefits, by putting a higher value for arraysize.

I have used a table TEST with 10,36,416 records.

Faster execution The records were returned faster after setting a higher ARRAYSIZE.

With the default value 15 for the ARRAYSIZE, it took 1.49 minute to complete the execution. 

SQL> select * from test;

1036416 rows selected.

Elapsed: 00:01:49.86

But after setting a value to 5000, execution completed in less than 6 seconds

SQL> select * from test;

1036416 rows selected.

Elapsed: 00:00:05.84

So, why ARRAYSIZE making the execution faster?  See below,

Consistent gets:  When you use ARRAYSIZE 15, the default value, you are instructing sqlplus to to ask for only 15 records at a time.   So, the database server will prepare a TCP/IP packet containing 15 records though it can process more.   There could be more than 100 records in a block and the server is forced to process only 15 records at a time.  This is causing the database re-read the blocks (more Consistent gets) to prepare the packets of 15 records.  It is obvious from the ‘consistent gets’ statistics.

With ARRAYSIZE 15 it is 

          0  recursive calls
          0  db block gets
      82351  consistent gets
      14177  physical reads

While with ARRAYSIZE 5000, the number of consistent gets considerably low.

          0  recursive calls
          0  db block gets
      14385  consistent gets
      14177  physical reads

A higher consistent get will lead to CBC latch contention as well as increased CPU usage.  It is obvious from below stats.

Higher CPU usage:   Higher consistent get will directly push the CPU usage higher as shown below.

SQL> set linesize 132
SQL> col name for a30
SQL> select name,value,username,osuser,se.sql_hash_value
  2  from v$mystat st,v$statname sn,v$session se
  3  where st.STATISTIC# = sn.STATISTIC#
  4  and se.sid=st.sid
  5  and sn.name = ‘CPU used by this session’
  6  and se.username is not null
  7  order by value;

NAME                                            VALUE                USERNAME                       OSUSER                      SQL_HASH_VALUE
——————————        ———-          – —————————– —————————— ————–
CPU used by this session        1949                    THOMAS                           thomas.saviour                  1699502816

Elapsed: 00:00:00.03
SQL>
SQL> set arraysize 5000
SQL> set autotrace traceonly exp stat
SQL> select * from test;

1036416 rows selected.

Elapsed: 00:00:05.84

Execution Plan
———————————————————-
Plan hash value: 217508114

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |  1036K|    91M|  3948   (3)| 00:00:48 |
|   1 |  TABLE ACCESS FULL| TEST |  1036K|    91M|  3948   (3)| 00:00:48 |
————————————————————————–
Statistics
———————————————————-
          0  recursive calls
          0  db block gets
      14385  consistent gets
      14177  physical reads
          0  redo size
   46865978  bytes sent via SQL*Net to client
       1894  bytes received via SQL*Net from client
        209  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1036416  rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> set linesize 132
SQL> col name for a30
SQL> select name,value,username,osuser,se.sql_hash_value
  2  from v$mystat st,v$statname sn,v$session se
  3  where st.STATISTIC# = sn.STATISTIC#
  4  and se.sid=st.sid
  5  and sn.name = ‘CPU used by this session’
  6  and se.username is not null
  7  order by value;

NAME                                            VALUE            USERNAME                       OSUSER                      SQL_HASH_VALUE
——————————        ———-          —————————— —————————— ————–
CPU used by this session        2298              THOMAS                         thomas.saviour                  1699502816

With ARRAYSZIE 5000  it took 349 CPU cycles to read the table While it took 585 CPU cycles when the ARRAYSIZE is set to 15.

SQL> set linesize 132
SQL> col name for a30
SQL> select name,value,username,osuser,se.sql_hash_value
  2  from v$mystat st,v$statname sn,v$session se
  3  where st.STATISTIC# = sn.STATISTIC#
  4  and se.sid=st.sid
  5  and sn.name = ‘CPU used by this session’
  6  and se.username is not null
  7  order by value;

NAME                                VALUE USERNAME                       OSUSER                      SQL_HASH_VALUE
—————————— ———- —————————— —————————— ————–
CPU used by this session             2298 THOMAS                         thomas.saviour                  1699502816

Elapsed: 00:00:00.01
SQL>
SQL> set arraysize 15
SQL> set autotrace traceonly exp stat
SQL> select * from test;

1036416 rows selected.

Elapsed: 00:01:50.57

Execution Plan
———————————————————-
Plan hash value: 217508114

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |  1036K|    91M|  3948   (3)| 00:00:48 |
|   1 |  TABLE ACCESS FULL| TEST |  1036K|    91M|  3948   (3)| 00:00:48 |
————————————————————————–
Statistics
———————————————————-
          0  recursive calls
          0  db block gets
      82351  consistent gets
      14177  physical reads
          0  redo size
   50512153  bytes sent via SQL*Net to client
     483895  bytes received via SQL*Net from client
      69096  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1036416  rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> set linesize 132
SQL> col name for a30
SQL> select name,value,username,osuser,se.sql_hash_value
  2  from v$mystat st,v$statname sn,v$session se
  3  where st.STATISTIC# = sn.STATISTIC#
  4  and se.sid=st.sid
  5  and sn.name = ‘CPU used by this session’
  6  and se.username is not null
  7  order by value;

NAME                                VALUE USERNAME                       OSUSER                      SQL_HASH_VALUE
—————————— ———- —————————— —————————— ————–
CPU used by this session             2883 THOMAS                         thomas.saviour                  1699502816

Elapsed: 00:00:00.03

So, server is consuming more CPU just because client is set a low value for ARRAYSIZE.

Network impact : Server is preparing a packet of 15 records and sending to the client, while a packet can accomadate more data.  This will dramatically increase the SQL*Net roundtrips to/from client.  Not a good news, this will not only increase the service time but also clog your network.  Below test result shows a big difference in network round trips for the same amount  of data.

50512153  bytes sent via SQL*Net to client
     483895  bytes received via SQL*Net from client
      69096  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1036416  rows processed

While after setting ARRAYSIZE to 5000, the round trips come down to 209 from 69096, a drastic difference.

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
      14385  consistent gets
      14177  physical reads
          0  redo size
   46865978  bytes sent via SQL*Net to client
       1894  bytes received via SQL*Net from client
        209  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1036416  rows processed

If you trace 10046 level 12, you can see the behavioural difference when you set ARRAYSIZE to a biger value.  With ARRAYSIZE value 15, server is send 1 bytes packet size with lot of consisstent gets.

WAIT #8: nam=’SQL*Net message to client’ ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598632702
FETCH #8:c=0,e=92,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,tim=13836598632770
WAIT #8: nam=’SQL*Net message from client’ ela= 1443 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598634307
WAIT #8: nam=’SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598634374
FETCH #8:c=0,e=110,p=0,cr=2,cu=0,mis=0,r=15,dep=0,og=1,tim=13836598634466
WAIT #8: nam=’SQL*Net message from client’ ela= 1791 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598636311
WAIT #8: nam=’SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598636384
FETCH #8:c=0,e=92,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,tim=13836598636453
WAIT #8: nam=’SQL*Net message from client’ ela= 1233 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598637738
WAIT #8: nam=’SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598637799
FETCH #8:c=0,e=85,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,tim=13836598637869
WAIT #8: nam=’SQL*Net message from client’ ela= 1432 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598639353
WAIT #8: nam=’SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598639414
FETCH #8:c=0,e=84,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,tim=13836598639483
WAIT #8: nam=’SQL*Net message from client’ ela= 1366 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598640901
WAIT #8: nam=’SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598640975
FETCH #8:c=0,e=93,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,tim=13836598641044
WAIT #8: nam=’SQL*Net message from client’ ela= 1395 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598642492
WAIT #8: nam=’SQL*Net message to client’ ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598642554
FETCH #8:c=0,e=139,p=0,cr=2,cu=0,mis=0,r=15,dep=0,og=1,tim=13836598642677
WAIT #8: nam=’SQL*Net message from client’ ela= 1717 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598644449
WAIT #8: nam=’SQL*Net message to client’ ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598644518
FETCH #8:c=0,e=92,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,tim=13836598644587
WAIT #8: nam=’SQL*Net message from client’ ela= 1765 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598646403
WAIT #8: nam=’SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598646467
FETCH #8:c=0,e=86,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,tim=13836598646536
WAIT #8: nam=’SQL*Net message from client’ ela= 1363 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598647952
WAIT #8: nam=’SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598648013
FETCH #8:c=0,e=85,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,tim=13836598648082
WAIT #8: nam=’SQL*Net message from client’ ela= 1264 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598649396
WAIT #8: nam=’SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598649467
FETCH #8:c=0,e=93,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,tim=13836598649537
WAIT #8: nam=’SQL*Net message from client’ ela= 1398 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598650987
WAIT #8: nam=’SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598651051
FETCH #8:c=0,e=86,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,tim=13836598651121
WAIT #8: nam=’SQL*Net message from client’ ela= 1396 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598652568
WAIT #8: nam=’SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598652645
FETCH #8:c=0,e=118,p=0,cr=2,cu=0,mis=0,r=15,dep=0,og=1,tim=13836598652747
WAIT #8: nam=’SQL*Net message from client’ ela= 1328 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598654130
WAIT #8: nam=’SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=32515 tim=13836598654193

While with the 5000 as ARRAYSIZE, server is sending almost 2000 bytes for each trip with no CR read waits.

WAIT #9: nam=’SQL*Net more data to client’ ela= 29 driver id=1413697536 #bytes=2013 p3=0 obj#=32515 tim=13836753033852
WAIT #9: nam=’SQL*Net more data to client’ ela= 22 driver id=1413697536 #bytes=2000 p3=0 obj#=32515 tim=13836753034025
WAIT #9: nam=’SQL*Net more data to client’ ela= 37 driver id=1413697536 #bytes=1991 p3=0 obj#=32515 tim=13836753034235
WAIT #9: nam=’SQL*Net more data to client’ ela= 21 driver id=1413697536 #bytes=2000 p3=0 obj#=32515 tim=13836753034420
WAIT #9: nam=’SQL*Net more data to client’ ela= 20 driver id=1413697536 #bytes=2011 p3=0 obj#=32515 tim=13836753034577
WAIT #9: nam=’SQL*Net more data to client’ ela= 36 driver id=1413697536 #bytes=1989 p3=0 obj#=32515 tim=13836753034767
WAIT #9: nam=’SQL*Net more data to client’ ela= 23 driver id=1413697536 #bytes=2001 p3=0 obj#=32515 tim=13836753034967
WAIT #9: nam=’SQL*Net more data to client’ ela= 22 driver id=1413697536 #bytes=2005 p3=0 obj#=32515 tim=13836753035155
WAIT #9: nam=’SQL*Net more data to client’ ela= 21 driver id=1413697536 #bytes=2010 p3=0 obj#=32515 tim=13836753035336
WAIT #9: nam=’SQL*Net more data to client’ ela= 22 driver id=1413697536 #bytes=1988 p3=0 obj#=32515 tim=13836753035507
WAIT #9: nam=’SQL*Net more data to client’ ela= 21 driver id=1413697536 #bytes=2003 p3=0 obj#=32515 tim=13836753035684
WAIT #9: nam=’SQL*Net more data to client’ ela= 22 driver id=1413697536 #bytes=2003 p3=0 obj#=32515 tim=13836753035858
WAIT #9: nam=’SQL*Net more data to client’ ela= 22 driver id=1413697536 #bytes=1998 p3=0 obj#=32515 tim=13836753036038
WAIT #9: nam=’SQL*Net more data to client’ ela= 38 driver id=1413697536 #bytes=2004 p3=0 obj#=32515 tim=13836753036191
WAIT #9: nam=’SQL*Net more data to client’ ela= 35 driver id=1413697536 #bytes=1998 p3=0 obj#=32515 tim=13836753036362
WAIT #9: nam=’SQL*Net more data to client’ ela= 21 driver id=1413697536 #bytes=2002 p3=0 obj#=32515 tim=13836753036556
WAIT #9: nam=’SQL*Net more data to client’ ela= 36 driver id=1413697536 #bytes=2005 p3=0 obj#=32515 tim=13836753036725
WAIT #9: nam=’SQL*Net more data to client’ ela= 21 driver id=1413697536 #bytes=2014 p3=0 obj#=32515 tim=13836753036915
WAIT #9: nam=’SQL*Net more data to client’ ela= 21 driver id=1413697536 #bytes=1999 p3=0 obj#=32515 tim=13836753037072
WAIT #9: nam=’SQL*Net more data to client’ ela= 21 driver id=1413697536 #bytes=1984 p3=0 obj#=32515 tim=13836753037294
WAIT #9: nam=’SQL*Net more data to client’ ela= 22 driver id=1413697536 #bytes=2001 p3=0 obj#=32515 tim=13836753037472
WAIT #9: nam=’SQL*Net more data to client’ ela= 20 driver id=1413697536 #bytes=2001 p3=0 obj#=32515 tim=13836753037666
WAIT #9: nam=’SQL*Net more data to client’ ela= 22 driver id=1413697536 #bytes=2003 p3=0 obj#=32515 tim=13836753037865
WAIT #9: nam=’SQL*Net more data to client’ ela= 21 driver id=1413697536 #bytes=2005 p3=0 obj#=32515 tim=13836753038043
WAIT #9: nam=’SQL*Net more data to client’ ela= 22 driver id=1413697536 #bytes=1995 p3=0 obj#=32515 tim=13836753038231
WAIT #9: nam=’SQL*Net more data to client’ ela= 24 driver id=1413697536 #bytes=2001 p3=0 obj#=32515 tim=13836753038472
WAIT #9: nam=’SQL*Net more data to client’ ela= 21 driver id=1413697536 #bytes=2003 p3=0 obj#=32515 tim=13836753038638
WAIT #9: nam=’SQL*Net more data to client’ ela= 37 driver id=1413697536 #bytes=1999 p3=0 obj#=32515 tim=13836753038836
WAIT #9: nam=’SQL*Net more data to client’ ela= 21 driver id=1413697536 #bytes=2001 p3=0 obj#=32515 tim=13836753038997
WAIT #9: nam=’SQL*Net more data to client’ ela= 23 driver id=1413697536 #bytes=2003 p3=0 obj#=32515 tim=13836753039196
WAIT #9: nam=’SQL*Net more data to client’ ela= 22 driver id=1413697536 #bytes=2016 p3=0 obj#=32515 tim=13836753039376
WAIT #9: nam=’SQL*Net more data to client’ ela= 21 driver id=1413697536 #bytes=1997 p3=0 obj#=32515 tim=13836753039545
WAIT #9: nam=’SQL*Net more data to client’ ela= 21 driver id=1413697536 #bytes=1988 p3=0 obj#=32515 tim=13836753039754
WAIT #9: nam=’SQL*Net more data to client’ ela= 20 driver id=1413697536 #bytes=2004 p3=0 obj#=32515 tim=13836753039932
WAIT #9: nam=’SQL*Net more data to client’ ela= 59 driver id=1413697536 #bytes=1999 p3=0 obj#=32515 tim=13836753040134
WAIT #9: nam=’SQL*Net more data to client’ ela= 22 driver id=1413697536 #bytes=2002 p3=0 obj#=32515 tim=13836753040320
WAIT #9: nam=’SQL*Net more data to client’ ela= 22 driver id=1413697536 #bytes=2000 p3=0 obj#=32515 tim=13836753040483
WAIT #9: nam=’SQL*Net more data to client’ ela= 24 driver id=1413697536 #bytes=2002 p3=0 obj#=32515 tim=13836753040686
WAIT #9: nam=’SQL*Net more data to client’ ela= 34 driver id=1413697536 #bytes=2000 p3=0 obj#=32515 tim=13836753040873
WAIT #9: nam=’SQL*Net more data to client’ ela= 22 driver id=1413697536 #bytes=2002 p3=0 obj#=32515 tim=13836753041061
WAIT #9: nam=’SQL*Net more data to client’ ela= 190 driver id=1413697536 #bytes=2008 p3=0 obj#=32515 tim=13836753041392
WAIT #9: nam=’SQL*Net more data to client’ ela= 24 driver id=1413697536 #bytes=1994 p3=0 obj#=32515 tim=13836753041600
WAIT #9: nam=’SQL*Net more data to client’ ela= 23 driver id=1413697536 #bytes=2000 p3=0 obj#=32515 tim=13836753041802

So, applications can consume more server resources by keeping the default value for ARRAYSIZE in sqlplus.  If you are using .Net, JAVA, perl, OCI, Python and PHP  there are ways to manage the ARRAYSIZE, read the blog from TOAD experts.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: