ODBC Performance Oracle

Reply
Contributor
Posts: 21

ODBC Performance Oracle

Hi,

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?

Christoph

Super User
Posts: 5,257

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
Contributor
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.

Thanks.

Christoph

Regular Contributor
Posts: 213

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>;

Example:

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

Hope this helps,

Ahmed

Ask a Question
Discussion stats
  • 3 replies
  • 427 views
  • 3 likes
  • 3 in conversation