Sql-server – Linked Server to Oracle seems to be slow

linked-serveroracle-11gperformancesql server

we have a linked server between a SQL Server 2008 R2 instance and an Oracle 11.1.0.7 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.

Best Answer

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

Related Question