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.