http://www.itpub.net/showthread.php?s=&postid=1620814

You proved me wrong on one point. I thought arraysize was strictly the number of rows the server process reads out of buffer cache (or disk) at a time. Looks like it's more complicated than that. The documentation says "[arraysize] Sets the number of rows--called a batch--that SQL*Plus will fetch from the database at one time". I guess they need to add the word "approximate" to it.

I don't know your Oracle version. On my 9.0.1.3.1 on Windows XP using 8k db_block_size and creating a table like yours (create table t (a char(2000)); insert into t select 'a' from dba_objects where rownum < 101; select * from t;), I get this result:

arraysize - consistent gets
1 - 72
2 - 72
3 - 72
4 - 56
5 - 52
6 - 56
7 - 49
8 - 48
9 - 50
10 - 46
11 - 45
12 - 48

Yours apparently is different:

1 - 71
2 - 71
3 - 67
4 - 55
5 - 51

By the way, my old thinking was 100 consistent reads for 100 rows, not 300, regardless how many times the block is read. I still believe with improper array size, most blocks are read more than once. Consistent gets are the count of reads on buffer cache, not PGA. There must be some prefetch in sqlplus behind the scenes, as you said. But that doesn't change the definition of consistent gets.

Yong Huang




http://metalink.oracle.com/metalink/plsql/ml2_documents.showFOR?p_id=263236.995
Oracle Forms : Block Property - Array 
Oracle Discoverer : Options - Query Governer - number of records 
Oracle Reports : Preferences - Runtime - Array (in KB) 
SQL*Plus : SET ARRAYSIZE


http://asktom.oracle.com/pls/ask/f?p=4950:8:3341804672157758663::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:880343948514,
Array size by default is 15 in plus
10 in JDBC
2 in pro*c
1 in OCI
??? in odbc (no idea, never use it)
...
(sqlplus is JUST an oci application)
...
If you full scan a table T that has N blocks and R rows and you use an array 
fetch size of A, we will typically perform the following number of consistent 
gets:

N + R/A
...
A FACTOR in consistent gets is arraysize.

ARRAYSIZE does not determine consistent gets.