May 1, 2013 at 11:17 am
Gang,
I'm completely perplexed on this one. I am running a query against an Oracle 11g database. It looks something like this:
select * from openquery(oradb, "select col1, col2, col3....col15 from ora.Table where rownum <= 100000")
The query is going against a linked server using the Oracle 11.2 client download. The purpose of the query is to pull in the first 100,000 records from a particular table. The data is about 15MB worth of data. When I run the query on two of my SQL Servers - let's call them A1 and A2 and both running 2008 R2, the query takes about 20 seconds.
Here is where it gets wierd.
When I run it on my new server - call it B1 also running 2008 R2, it takes EXACTLY 3 minutes and 36 seconds, everytime. The query executes immediately but the data transfer is incredibly slow.
We monitored the server and the network performance is only about 40,000 B/sec.
If we run the query in SQL Plus (Oracle client), it runs in 20 seconds - just like to other SQL Servers. THe network performance is well over 500,000 B/sec. That level of performance is the same on servers A1 and A2.
I'm leaning that SQL Server is throttling this query for some reason. My networking and Oracle teams looked at it and said Oracle & Network are not the issue - I agree since the SQL Plus query runs in 20 seconds.
Has anyone else seen this on SQL Server? Are there some networking settings in SQL Server that would cause it to not spike out the network?
I'm completely lost on this one.
Dennis
May 1, 2013 at 1:01 pm
What are the data types of the columns? Wondering if you're not suffering from a collation issue. A litmus test for this would be one of the instances may churn up a ton more CPU than you'd expect. Just a guess as I've never used openquery before.
May 1, 2013 at 1:07 pm
Based on what you've described, I'd agree it's not the Oracle server.
Has anyone checked the settings on the NIC in the new server, or possibly the port on the switch? Possibly one of those is throttled?
Also verify you're using the same version of the openQuery.
Other than that, I can't think of anything.
Jason
May 1, 2013 at 2:23 pm
Thanks for the responses.
Regarding the data types - they are:
1.) char
2.) varchar2
3.) date
4.) number
We tried a mixture of columns in our openquery and if we bring it down to around 5-6 columns, the queries goes incredibly fast. We isolated each data type and couldn't find any particular data type to slow it down.
Regarding the switch / NICs:
Yes, we investigated both. We pretty much ruled out the switch / NICs when the query executes fine in SQL Plus. If it were a switch / NIC thing, it would be with any application (I would think). Also, we hard plugged the server directly into the same switch as the Oracle box and still no improvement. The NICs are patched up to the latest patches as well.
Additional thoughts:
We tried saving the query as a SQL script and we ran the query in SQLCMD. The results were the same but it runs and stops, runs and stops, etc. It looks like we get chunks of data returned at a time - maybe 100 rows or so. It is consistent with what we see when we query in management studio.
The thing that has me completely thrown for a loop is that it is incredibly fast on skinny tables (less than 6 columns) but slow on fat tables. If we limit our columns to less than 6 or so (regardless of which columns we choose) it runs lightning fast and SQLCMD streams it on the screen. Once we add more columns (again, regardless of data type), it runs and stops, runs and stops, etc.
We are going to put in a call with Microsoft support to get them to look at it but any thoughts you may have would still be beneficial.
Thanks for helping a few thought cycles for me.
Dennis
May 1, 2013 at 3:15 pm
Hopefully MS finds something.
Oracle Net services tuning (buffers).
Have you checked that offload/chimney on the nic is off? Can cause troubles.
May 1, 2013 at 4:08 pm
What driver are you using to connect to the Oracle server?
October 15, 2013 at 7:29 am
ANY progress on this issue?
We are seeing similar performance issues when connecting to oracle from sql server using the oracle oledb driver from oracle. Did NETMON traces as well and can see a 130ms delay between requests, which is odd. It takes 40 seconds to pull just 30k records from oracle on a 1Gbit connection to a local oracle server. The SAME query using oracle SQL developer returns in 8 seconds (which I still think is slow).
ANY ideas on this one.
January 30, 2017 at 3:24 pm
ktl - Tuesday, October 15, 2013 7:29 AMANY progress on this issue?We are seeing similar performance issues when connecting to oracle from sql server using the oracle oledb driver from oracle. Did NETMON traces as well and can see a 130ms delay between requests, which is odd. It takes 40 seconds to pull just 30k records from oracle on a 1Gbit connection to a local oracle server. The SAME query using oracle SQL developer returns in 8 seconds (which I still think is slow).ANY ideas on this one.
You must change the Incompatable Database Engine Server Collation into Linked Server Options. Set Value to True
January 30, 2017 at 3:26 pm
ktl - Tuesday, October 15, 2013 7:29 AMANY progress on this issue?We are seeing similar performance issues when connecting to oracle from sql server using the oracle oledb driver from oracle. Did NETMON traces as well and can see a 130ms delay between requests, which is odd. It takes 40 seconds to pull just 30k records from oracle on a 1Gbit connection to a local oracle server. The SAME query using oracle SQL developer returns in 8 seconds (which I still think is slow).ANY ideas on this one.
You must change the Incompatable Database Engine Server Collation into Linked Server Options. Set Value to True
June 18, 2017 at 12:56 pm
My apologies for never following up and closing out the issue. Here is what happened 4+ years ago.
We received a new server and installed SQL Server on it. When transferring large files, the throughput on the network would vary greatly. When we queried a particular Oracle database using OpenQuery, the performance was horrendous.
We assumed two issues:
1 - Collation issue - which was pretty much thrown out immediately as other SQL 2008 R2's didn't have the issue
2 - Networking issue - Our network administration and server support was outsourced. The outsourced firm said there was no issue with the server that they could identify.
Finally - we opened a ticket with Microsoft to get resolution. First thing Microsoft did was check out NICs. Lo and behold...two of the NICs were bad. I'm going to assume that the network folks never looked at the server because the other assumption is they were just grossly incompetent and that is something I never want to assume...
So if others are having this issue - take a good hard look at your NICs. It is likely networking related.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply