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/asktom/f?p=100:11:0::::P11_QUESTION_ID:880343948514
(old: 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.

****************************************************************************************************

https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=73823.1
SQL: Simple Array Fetch
has a good example of OCI program to do array fetch.

****************************************************************************************************

ODBC Driver Configuration (odbcad32.exe):

Oracle ODBC:
Oracle 9iR2:  Prefetch Count: 10  <-- This is the one you must increase!!
Oracle 10gR2: Fetch Buffer Size: 64000
Oracle 11gR1: Fetch Buffer Size: 64000

Microsoft ODBC for Oracle:
2.575.1117: Buffer Size: 65535
2.576.3959: Buffer Size: 65535

****************************************************************************************************

Crystal Reports:
Think out of box!
> The only thing I've run into before with Oracle & Crystal is that
> Crystal will (by default, I think) pull large amounts of data from
> Oracle to the client, only to aggregate it on the client side, e.g. to
> provide a report on sales totals by customer.
> I checked my notes and the exact name of the option is "Perform Grouping
> on Server", so just google for that and "crystal reports" and/or
> "business objects" and you'll get lots of hits, for example, here is a
> presentation that talks about it starting on slide 9:
> 
> http://www.businessobjects.com/pdf/dev_zone/Unchugging_CR_Michitsch.pdf
> 
> Regards,
> Brandon

(I remember a case where the Crystal Reports user asked DBAs for help with
performance. He was advised to bump up ODBC prefetch size. I think he changed
it to 10000, and the report was generated in a few minutes instead of 2 hours.
Looking at my notes about ODBC prefecth size, he must be using pre-10g Oracle's
ODBC driver.)

****************************************************************************************************

Tom Kyte "Effective Oracle by Design", p.575
SetDefaultRowPrefetch in Java

Also, setDefaultBatchValue

****************************************************************************************************

In Python using cx_Oracle, it's arraysize, e.g.

import cx_Oracle
con = cx_Oracle.connect('...
cur = con.cursor()
cur.arraysize = 100
cur.execute('select * from...

****************************************************************************************************

Array Size in Different Development Tools (Source: Guy Harrison, "Oracle SQL High-Performance Tuning",
Prentice Hall, 1997, Appendix C)

Powerbuilder: "BLOCK setting within the DbParm", default: 100

Oracle Forms: "transparently implements array processing... size of the array is set to the number of 
rows displayed plus 3 additional rows, but can be adjusted using the "Records Buffered" property for 
the block".

OCI: "To issue array DML, you must use the OEXN call instead of the OXEC call. To fetch an array of 
data you should use OFEN in place of OFETCH."

****************************************************************************************************

Arraysize and Consistent Gets

http://www.itpub.net/thread-242144-1-1.html
(old: http://www.itpub.net/showthread.php?s=&postid=1620814)

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