Consider the following scenario:
You configure a Linked Server to access an Oracle database as a remote source in Microsoft SQL Server 2012 or SQL Server 2014, and you use the "Oracle.OleDB" OleDB provider for that Linked Server.
You use a remote query that references an Oracle table, and that table contains one or more columns with Oracle's proprietary NUMBER type. This table also has a significant number of rows in it.
In this scenario, you may experience performance issues compared with the legacy Microsoft "MSDAORA" OleDB provider. This issue occurs because of the way the "Oracle.OleDB" provider reports the NUMBER data type to SQL Server.
To resolve this issue, apply this update and then enable trace flag TF9394. To enable trace flag TF9394, see the "More Information" section.
This issue is fixed in the following updates:
Cumulative Update 13 for SQL Server 2014
Cumulative Update 6 for SQL Server 2014 Service Pack 1 (SP1)
Cumulative Update 2 for SQL Server 2012 Service Pack 3 (SP3)
Cumulative Update 11 for SQL Server 2012 Service Pack 2 (SP2)
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
How to enable trace flag 9394
You can enable trace flag 9394 at startup or in a user session. This trace flag has either global-level or session-level effects. To enable trace flag 9394, use the DBCC TRACEON command or use –T 9394 as a startup parameter.
If DBCC TRACEON\TRACEOFF is used, this does not regenerate a new cached plan for stored procedures. There could be plans in the cache that were created without the trace flag.
For more information about how to enable or disable trace flags and about global and session-level trace flags, see the following topics in SQL Server Books Online:
Learn about the terminology that Microsoft uses to describe software updates.
The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.