Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execution plan difference when executed from Oracle VS SSMS Expand / Collapse
Author
Message
Posted Thursday, January 16, 2014 6:16 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 63, Visits: 1,912
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?


  Post Attachments 
ViewSpecialFeatureLineCombined_Anonymized.sqlplan (10 views, 81.65 KB)
Post #1531527
Posted Monday, January 20, 2014 12:15 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1532768
Posted Monday, January 20, 2014 2:18 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 63, Visits: 1,912
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.
Post #1532786
Posted Thursday, January 30, 2014 2:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 133, Visits: 250
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.
Post #1536561
Posted Monday, February 03, 2014 3:15 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 63, Visits: 1,912
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.
Post #1537231
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse