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