0

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.
Improve this question
3
  • What are the actual queries you're running? You may find using OPENQUERY a better option if you're not already.
    – dwjv
    Commented Jan 12, 2016 at 10:57
  • It's an external supplier (the one who built the software we baught) who writes and manage the code for this. They used [LINKED]..SCHEMA.TABLE in the definition of a cursor after which there is a loop with fetches.
    – Jefferson B. Elias
    Commented Jan 12, 2016 at 11:15
  • This question on StackOverflow may help.
    – Slogmeister Extraordinaire
    Commented Mar 21, 2018 at 15:48

1 Answer 1

Reset to default
-2

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

Improve this answer
1
  • 1
    Any chance of getting a little more commentary here to help the next folks who come along?
    – jcolebrand ♦
    Commented Jan 30, 2017 at 22:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.