Execution plan difference when executed from Oracle VS SSMS

Question

Execution plan difference when executed from Oracle VS SSMS

Ville-Pekka Vahteala

SSCrazy

Points: 2458
More actions
January 16, 2014 at 6:16 am

#287375

Hi.
We are having performance problems on our server. We have found out that execution plans (attached) are very different when query is executed from SSMS and Oracle.
Does anyone has had similar cases? Did you were able to fix it?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Ville-Pekka Vahteala (1/16/2014)
Hi.
We are having performance problems on our server. We have found out that execution plans (attached) are very different when query is executed from SSMS and Oracle.
Does anyone has had similar cases? Did you were able to fix it?

Any particular reason to use SSMS to connect to Oracle?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 2

PaulB-TheOneAndOnly (1/20/2014)
Ville-Pekka Vahteala (1/16/2014)
Hi.
We are having performance problems on our server. We have found out that execution plans (attached) are very different when query is executed from SSMS and Oracle.
Does anyone has had similar cases? Did you were able to fix it?
Any particular reason to use SSMS to connect to Oracle?

I could have written that more clearly. Query is executed from procedure in Oracle. So actually Oracle is connecting to sql server.

I had to use index hint to get execution plan work for our needs.

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 3

The usual suspects in this case are the connection properties (ansi_nulls, quoted_identifiers, etc.). Have a look at the connection properties for the connection in sys.dm_exec_sessions, or via profiler.

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 4

I was able to workaround the issue by forcing spatial index usage.

This problem is interesting because it was only one query which is using this dynamic cursor. All other queries from Oracle are using keyset cursor. I have seen the code and they look same on Oracle site. I was able reproduce this with sql server by setting cursor type to dynamic.