12-11-2012 04:43 AM
I have some issues when I try to connect to our Oracle database via odbc.
I am working with SAS 9.1.3 SP4. We installed a 11g R2 Oracle client. When I connect to our 10g database the query takes 5 minutes and selects about 700.000 observations. When I connect to our 11g database the query needs hours to execute. I aborted the query after 1 hour and SAS processed just 100.000 observations. The settings of the two odbc connections are the same.
One difference that the 11g database is at a different location. But we monitored the network traffic but this doesn't seem to be our bottleneck. Our DBA also checked the load on our databases and there is nothing to worry about it.
Are there some known issues in connecting oracle in that constellation?
12-14-2012 07:58 AM
After I did some more tests we noticed that it is a odbc driver problem. I noticed a worse performance in excel but not that bad like in SAS. So I thought it could be a SAS problem. Still strange is that the same driver works well with our 10g database.With a 11g database we need some special settings in the driver.
12-14-2012 03:04 PM
In particular, pay attention to the READBUFF Libname Option. You may need to explicitly specify the option with higher value.
LIBNAME libref ODBC <SAS/ACCESS-connection-options> <SAS/ACCESS-LIBNAME-options>;
LIBNAME libref ODBC <SAS/ACCESS-connection-options> READBUFF=50000;
Hope this helps,