Row Prefeching（行预取）

行预取(Row Prefetching)，当客户端从数据库获取数据的时候，可以一行一行的读取，也可以同时读取多行。[@more@]

行预取(Row Prefetching)，当客户端从数据库获取数据的时候，可以一行一行的读取，也可以同时读取多行。每次客户端请求从数据库返回1条记录的时候，会预取多条记录并将它们存储在客户端的内存中，这样可以减少数据库调用读取数据的次数，降低网络传输次数，提高效率。

SQL*PLUS中，通过系统变量arraysize管理行预取行数。默认值是15.

行预取对数据访问产生的逻辑读影响是非常大的，看下面的例子。

SYS@lipei> select num_rows,blocks,round(num_rows/blocks) as rows_per_block

2 from dba_tables

3 where table_name='EMP' and owner='SYS';

NUM_ROWS BLOCKS ROWS_PER_BLOCK

---------- ---------- --------------

20000 229 87

SYS@lipei> set autotrace traceonly

SYS@lipei> select*From emp;

20000 rows selected.

Elapsed: 00:00:02.49

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 20000 | 1406K| 61 (4)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 20000 | 1406K| 61 (4)| 00:00:01 |

--------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

10166 consistent gets

0 physical reads

0 redo size

2856543 bytes sent via SQL*Net to client

110374 bytes received via SQL*Net from client

10001 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

20000 rows processed

逻辑读大约是行数的一半。

SYS@lipei> set arraysize 200

SYS@lipei> select*From emp;

20000 rows selected.

Elapsed: 00:00:00.13

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 20000 | 1406K| 61 (4)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 20000 | 1406K| 61 (4)| 00:00:01 |

--------------------------------------------------------------------------

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

321 consistent gets

0 physical reads

0 redo size

1569543 bytes sent via SQL*Net to client

1474 bytes received via SQL*Net from client

101 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

20000 rows processed

逻辑读大约和block的数量相近。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25586587/viewspace-1052716/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25586587/viewspace-1052716/