Example Analysis of the Influence of Oracle Arraysize Setting on Logical Read

When executing an SQL query, in order to obtain satisfied data, the query completes a series of steps such as parsing, binding, executing and extracting data in this process, all of which are executed separately, and the data rows that meet the conditions must be returned to the application by the database; For any result set of any size, it is very likely that the rows of data that need to be returned are not passed to the application during a round trip call!

During each call, the number of back and forth paths between the database and the client will affect the total response time hierarchically. Except for the step of extracting data (FETCH), other steps (parsing, binding and executing) are only executed once, which is also necessary. Oracle needs to obtain all the data results that meet the query conditions to perform multiple extraction operations.

With regard to the mechanism of extraction operation, One FETCH call will access one or more data blocks in the buffer cache. When one data block is accessed each time, Oracle will fetch data rows from this block and return them to the client in a loop. Here, the number of rows returned in a loop is Arraysize (column size), and Arraysize indicates the possible number of data rows transmitted in the network loop in a fetch operation.

By analyzing the mechanism of Arraysize, we can know that Arraysize has a basic influence on logical reading. If the size of Arraysize is correspondingly increased in application, the number of rows obtained from data blocks will be correspondingly increased compared with the previous settings. Under the same number of rows, the number of accesses to data blocks will naturally decrease, and logical reading will be correspondingly reduced; The same is true of the actual situation.

--  View the current Arraysize
SQL> show arraysize
arraysize 15

SQL> set autotrace traceonly statistics
SQL> select * from dba_objects;

72457 rows selected.

Statistics
----------------------------------------------------------
     0 recursive calls
     0 db block gets
    7675 consistent gets
     5 physical reads
     0 redo size
  3463453 bytes sent via SQL*Net to client
   53549 bytes received via SQL*Net from client
    4832 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
   72457 rows processed

--  Settings Arraysize For 50
SQL> set arraysize 50
SQL> /

72457 rows selected.

Statistics
----------------------------------------------------------
     15 recursive calls
     0 db block gets
    3618 consistent gets
     0 physical reads
     0 redo size
  3034066 bytes sent via SQL*Net to client
   16358 bytes received via SQL*Net from client
    1451 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
   72457 rows processed

In the above test, I took SQL*Plus as an example, selected the query accessing dba_objects table, and the result set of SQL query was the same. At this time, I increased the value of Arraysize from the default of 15 to 50, and I saw a very obvious change:

1. The number of logical readings is greatly reduced from 7675 to 3618.
2. The number of network round trips has dropped from 4832 to only 1451.

It should be noted that these changes have nothing to do with the SQL statement itself, and the size of Arraysize needs to be realized by programming. In this example, the command SET ARRAYSIZE in SQL*Plus environment is used. If it is an Java application, it can be realized by setting Connection. SetdefaultRowPrefetch (n).