SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OraOLEDB.Oracle vs. MSDAORA, SQL 32 bit, 64 bit


OraOLEDB.Oracle vs. MSDAORA, SQL 32 bit, 64 bit

Author
Message
scogeb
scogeb
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 420
After upgrading to SQL 2008 R2 on a new 64 bit server, linked queries are running 3 to 4 times as long. On the old 32 bit SQL 2005 system using MSDAORA, the below query takes 7 seconds. On the new 64 bit SQL 2008 R2 system using OraOLEDB.Oracle, it takes 23 seconds.

All of the searches I've done and articles I've read so far, suggest that this slow down is normal. Considering I just upgraded everything and everything else on the server is 2 to 3 times faster, this bothers me. Does anyone here have a solution or idea on how to deal with this?

SELECT *
FROM OPENQUERY(TEST,'Select ku_id, TOT_DATE from SIIY where TOT_DATE > 12252012')
Jo Pattyn
Jo Pattyn
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3567 Visits: 9938
What version of oracle oledb did you install?
Latest can be found here

Apparently there is a networksetting "TCP window scaling" that might be the culprit:
See Oracle support document Oledb and Odbc 64 Bit On Windows 2008 and Windows Vista Have Bad Performances [ID 1264980.1]
or Intermittent Slow Oracle Net from Windows 7/Vista/2008 machines

Not a solution, but what time takes
Select ku_id, TOT_DATE from SIIY where TOT_DATE > 12252012 on the oracle side?
Is TOT_DATE a date or a number? If date I would expect where TOT_DATE > to_date('12252012','MMDDYYYY')

in openquery you have to double the '
*added version of oledb, networksetting TCP Windows Scaling
scogeb
scogeb
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 420
Thanks Jo, I have 11.2.0 installed. TOT_DATE is actually a number. Do you have a link to that Oracle support file? I don't have an account with them and even after I created one, I still can't find it or don't have access. It looks very relavant though.
Jo Pattyn
Jo Pattyn
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3567 Visits: 9938
I can't copy or link the oracle support doc as you need an active account.
Check the blog above, it contains the command below

netsh interface tcp set global autotuninglevel=disabled

Other possible culprits: TCP chimney ofloading (see reference Server 2008 Slow Logon Network Access – TCP Offload & TCP Autotuning )
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6987 Visits: 4639
scogeb (10/23/2012)
I don't have an account with them and even after I created one, I still can't find it or don't have access.


Account has to be with Metalink. Oracle's knowledge base and customer support system. If you have Oracle (legally) you have the right to have an account. Once you have your Metalink account just search for the ID Jo has listed.

_____________________________________
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.
scogeb
scogeb
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 420
Thanks all. I did get the article. It looks like a reboot is required after setting the interface so I will have to wait and test during the next approved outage.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search