1

I have a SQL Server (2014 SP2) with a linked server to an Oracle 11 database.

I have a very simple select statement to an Oracle view which I know should return around 140k rows. But here's the thing, when I run it in SQL Server I immediately see records in the results window, but all of a sudden, the query hangs when it has so far returned only around 2000 rows and it just sits there forever doing nothing else. Sometimes, another few thousands rows appear before it again hangs.

The wait state on the query is on OLEDB which is to be expected. I'm thinking some sort of Network bottleneck...?

Sometimes I do get an error after a while: Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle"

Before anyone suggests it, 'allow in process' is ticked on the provider settings.

I'm looking for ideas to troubleshoot this at either the SQL Server side or the Oracle side to check that it's not a database config issue.

  • In Oracle, no. The view returns all 140K rows very quickly. – Molenpad Jun 10 '17 at 12:44
  • What version of the Oracle client is installed on the SQL Server? I've seen many random issues in 11.1.X client drivers and suggest upgrading the driver to 11.2.0.4 if you've not already done so. – John Eisbrener Jun 12 '17 at 13:57
  • The Oracle client is 12.1.0 – Molenpad Jun 12 '17 at 14:50
2

Just for anyone facing this mysterious issue in the future...

This particular issue was resolved by removing packet inspection rules on TCP Port 1521 (Oracle default) within the VMWare NSX. This was causing a bottleneck of data coming out of our Oracle infrastructure (IBM AIX) and back into our NSX infrastructure.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

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