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.
Best Answer
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.