Database Reference
In-Depth Information
Row Prefetching
From a performance point of view, you should always avoid row-based processing. For example, when a client
retrieves data from a database, it can do it row by row or, better yet, by retrieving several rows at the same time. This
technique, known as row prefetching , is fully described in Chapter 15. For the moment, let's just look at its impact
on the number of logical reads. Simply put, a logical read is counted each time the database engine accesses a
block. With a full table scan, there are two extremes. If row prefetching is set to 1, approximately one logical read per
returned row is performed. If row prefetching is set to a number greater than the number of rows stored in each table's
block, the number of logical reads is close to the number of the table's blocks. The following excerpt of the output
generated by the row_prefetching.sql script shows this behavior. In the first execution, with row prefetching set to
2 (the choice of this value is explained in the “Row Prefetching” section in Chapter 15), the number of logical reads
(5,388) is about half of the number of rows (10,000). In the second execution, because the number of prefetched rows
(100) is higher than the average number of rows per block (25), the number of logical reads (488) is about the same as
the number of blocks (401):
SQL> SELECT num_rows, blocks, round(num_rows/blocks) AS rows_per_block
2 FROM user_tables
3 WHERE table_name = 'T';
NUM_ROWS BLOCKS ROWS_PER_BLOCK
-------- ------ --------------
10000 401 25
SQL> set arraysize 2
SQL> SELECT * FROM t;
-----------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 5388 |
| 1 | TABLE ACCESS FULL| T | 10000 | 5388 |
-----------------------------------------------------
SQL> set arraysize 100
-----------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 488 |
| 1 | TABLE ACCESS FULL| T | 10000 | 488 |
-----------------------------------------------------
In SQL*plus, you manage the number of prefetched rows through the arraysize system variable. the default
value is 15.
Note
Given the dependency of the number of logical reads on row prefetching, whenever you execute a SQL statement
for testing purposes in a tool such as SQL*Plus, you should carefully set row prefetching like the application does. In
 
 
Search WWH ::




Custom Search