ODBC Performance Oracle

Posts: 21

ODBC Performance Oracle


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?


Super User
Posts: 5,876

Re: ODBC Performance Oracle

Since you are using ODBC, try to test with other client application than SAS, to see if the same behavior is repeated outside SAS.

Data never sleeps
Posts: 21

Re: ODBC Performance Oracle

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.



Super Contributor
Posts: 279

Re: ODBC Performance Oracle

Hi Christoph,

Check the SAS/ACCESS to ODBC or SAS/ACCESS to Relational Databases User Guide.

In particular, pay attention to the READBUFF Libname Option. You may need to explicitly specify the option with higher value.

Typical Syntax:

LIBNAME libref ODBC <SAS/ACCESS-connection-options> <SAS/ACCESS-LIBNAME-options>;


LIBNAME libref ODBC <SAS/ACCESS-connection-options> READBUFF=50000;

Hope this helps,


Ask a Question
Discussion stats
  • 3 replies
  • 3 in conversation