FIX: Slow performance when you query numeric data types from an Oracle database

Symptoms
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.
Resolution
To resolve this issue, apply this update and then enable trace flag TF9394. To enable trace flag TF9394, see the "More Information" section.

Cumulative update information

This issue is fixed in the following updates:
Recommendation: Install the latest cumulative update for SQL Server

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:
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
More information
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:
References
Learn about the terminology that Microsoft uses to describe software updates.

Third-party information disclaimer

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.

Properties

Article ID: 3138659 - Last Review: 05/31/2016 06:51:00 - Revision: 5.0

Microsoft SQL Server 2012 Service Pack 3, Microsoft SQL Server 2012 Service Pack 2, Microsoft SQL Server 2014 Service Pack 1, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Standard

  • kbqfe kbfix kbexpertiseinter kbsurveynew KB3138659
Feedback