Linked Server to Oracle seems to be slow

Posted on

Question :

we have a linked server between a SQL Server 2008 R2 instance and an Oracle instance using OraOLEDB.Oracle Provider.

The enabled options for provider are:

  • Dynamic parameter
  • Nested queries
  • Allow inprocess
  • Supports “Like” Operator

The options of the linked server are:

@optname=N'collation compatible', @optvalue=N'false'
@optname=N'data access', @optvalue=N'true'
@optname=N'dist', @optvalue=N'false'
@optname=N'pub', @optvalue=N'false'
@optname=N'rpc', @optvalue=N'true'
@optname=N'rpc out', @optvalue=N'true'
@optname=N'sub', @optvalue=N'false'
@optname=N'connect timeout', @optvalue=N'0'
@optname=N'collation name', @optvalue=null
@optname=N'lazy schema validation', @optvalue=N'false'
@optname=N'query timeout', @optvalue=N'0'
@optname=N'use remote collation', @optvalue=N'true'
@optname=N'remote proc transaction promotion', @optvalue=N'true'

The processes using this linked server just take orders from Oracle, do what it has to do and then sends back to Oracle informations about what have been done.

According to the end users, it seems to be slow.

How can I …

  1. Assess that the configuration is done appropriately?
  2. How can we make sure that performance is good ? Monitoring queries that goes through it, etc.

Answer :

You must change the Incompatable Database Engine Server Collation into Linked Server Options. Set Value to True

Leave a Reply

Your email address will not be published. Required fields are marked *