1

Scenario: We currently have a SQL 2008 R2 server (with Oracle 10g Client) using OPENQUERY to return data from an Oracle 10g database.

Problem: We would like to upgrade to SQL Server 2014 SP1 (with Oracle 12c Client) and still pull data from the Oracle 10g server. We have run some test on returning the data using OPENQUERY and the results are less then satifactory. A simple select * from a table went from 9seconds to 54 seconds!

Testing: We created a test SQL Server 2008 R2 server but this time put Oracle 12c client on it. The select * query ran in 26seconds this time but still alot longer then the 9 from the original server.

Question: Could there be a setting that isn't set on the new servers that would affect the speed this much? If so does anyone have any suggestions?

Note: I believe using a 12c client to connect to a 10g server is supported correct?

Thanks in advance.

Improve this question

2 Answers 2

Reset to default
1

As Oracle support note "Client / Server Interoperability Support Matrix for Different Oracle Versions" ( Doc ID 207303.1 ), Oracle Client 12.1 does not support connections with Oracle DB servers 10.1 and below.

Connections with Oracle DB 10.2 are still supported, but not recommended.

The recommended option its to use Oracle 10.2 client.

Improve this answer
0

So for some reason setting the processor affinity makes all the difference. The steps to fix this issue are below.

  1. Using SSMS, go to Instance properties, the select "Processors" page

  2. You will notice both "Automatically set processor affinity mask for all processors" and "Automatically set I/O affinity mask for all processors" will be selected by default

  3. Uncheck both these settings

  4. And then in grid below select "Processor Affinity" for all processor checkbox (DO NOT check I/O affinity) and test query.

  5. If the issue persists, restart the SQL Service and test the query again

It fixed the speed issue we were having with our Oracle queries on SQL Server 2014. We did also find that they would run faster without SP1

Hope this helps

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.