Analysis of the Impact of Oracle Arraysize settings on logical read instances, and analysis of arraysize instances

Analysis of the Impact of Oracle Arraysize settings on logical read instances, and analysis of arraysize instances

When executing an SQL query, the query completes a series of steps, including parsing, binding, executing, and extracting data, in order to obtain the expected data, rows that meet the conditions must be returned by the database to the application. For result sets of any size, the rows to be returned may not be passed to the application during a round-trip call!

During each call, the number of round-trip loops between the database and the client affects the total response time at a certain level. In addition to the data extraction (FETCH) step, the other steps (parsing, binding, and execution) it is also necessary that Oracle obtain all the data results that meet the query conditions to perform multiple extraction operations.

For the extraction mechanism, a FETCH call will access one or more data blocks in the buffer cache. Each time a data block is accessed, oracle extracts data rows from the block and returns them to the client in a loop. Here, the number of rows returned at a time is Arraysize (column size ), arraysize indicates the number of possible data rows transmitted by an extraction operation in the network loop.

By analyzing the Arraysize mechanism, we can know that Arraysize has a basic impact on logical read. If the Arraysize size is increased in the application, the number of rows retrieved from the data block increases accordingly. When the number of rows is the same, the number of data blocks accessed decreases naturally, and logical reads decrease accordingly.

-- View the current ArraysizeSQL> show arraysizearraysize 15SQL> set autotrace traceonly statisticsSQL> select * from dba_objects; 72457 rows selected. statistics defaults 0 recursive cballs 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 -- set Arraysize to 50 SQL> set arraysize 50SQL>/72457 rows selected. statistics limit 15 recursive cballs 0 db block gets 3618 consistent gets 0 physical reads 0 redo size 3034066 bytes sent via SQL * Net to client 16358 bytes encoded ed 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 used SQL * Plus as an example to select the dba_objects Table Access query. The SQL query result set is the same, in this case, the value of Arraysize is increased from the default value of 15 to 50, and obvious changes are displayed:

1. The number of logical reads is greatly reduced from 7675 to 3618.
2. The number of network round-trips dropped from the previous 4832 to only 1451.

Note that these changes are irrelevant to the SQL statement itself. Arraysize needs to be programmed. In this example, use the set arraysize command in the SQL * Plus environment. If it is a Java application, you can set Connection. setdefaultRowPrefetch (n.