The default setting for SAS/Access to Oracle is 250 buffers. A READBUFF specifies the number of rows to read into SAS’ buffer. READBUFF can have a dramatic impact on performance. Here is an example of how you can test what a good READBUFF setting might be.
First I create three libnames with different READBUFF settings
libname ora1 oracle user=sh pass=sh path=ora10g READBUFF=250;
libname ora2 oracle user=sh pass=sh path=ora10g READBUFF=1000;
libname ora3 oracle user=sh pass=sh path=ora10g READBUFF=10000;
Next I identify a relatively large table in terms of column width and row count. For this example I have a table called INFORMS2009 which contains 79 columns and 1M records.
Finally, I make three runs with a datastep program to extract data
data test1;
set ora1.INFMS2009;
run;
data test2;
set ora2.INFMS2009;
run;
data test3;
set ora3.INFMS2009;
run;
And review the results:
NOTE: There were 1006114 observations read from the data set ORA1.INFMS2009.
NOTE: The data set WORK.TEST1 has 1006114 observations and 79 variables.
NOTE: DATA statement used (Total process time):
real time 1:06.90
cpu time 20.01 seconds
NOTE: There were 1006114 observations read from the data set ORA2.INFMS2009.
NOTE: The data set WORK.TEST2 has 1006114 observations and 79 variables.
NOTE: DATA statement used (Total process time):
real time 40.70 seconds
cpu time 21.54 seconds
NOTE: There were 1006114 observations read from the data set ORA3.INFMS2009.
NOTE: The data set WORK.TEST3 has 1006114 observations and 79 variables.
NOTE: DATA statement used (Total process time):
real time 33.18 seconds
cpu time 20.90 seconds
How about 1m 6 seconds vs. 33 seconds! that is a 63% improvement in performance. Note your millage may vary, also like candy too much of a good thing can be bad too.
For example jacking up the READBUFF to 20k can actually result in worse performance than the default setting.
libname ora4 oracle user=sh pass=XX path=ora10g READBUFF=20000;
data test4;
set ora4.INFMS2009;
run;
NOTE: There were 1006114 observations read from the data set ORA4.INFMS2009.
NOTE: The data set WORK.TEST4 has 1006114 observations and 79 variables.
NOTE: DATA statement used (Total process time):
real time 1:11.90
cpu time 23.01 seconds
for more information on READBUFF and SAS/Access to Oracle check out
http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001342369.htm