SQL Server OpenQuery() behaving differently then a direct query from TOAD

Question

The following query works efficiently when run directly against Oracle 11 using TOAD (with native Oracle drivers)

select ... from ... where ...
and srvg_ocd in (
  select ocd
   from rptofc
  where eff_endt = to_date('12/31/9999','mm/dd/yyyy')
    and rgn_nm = 'Boston'
) ...
;

The exact same query "never" returns if passed from SQL Server 2008 to the same Oracle database via openquery(). SQL Server has a link to the Oracle database using an Oracle Provider OLE DB driver.

select * from openquery( servername, '
  select ... from ... where ...
  and srvg_ocd in (
    select ocd
     from rptofc
    where eff_endt = to_date(''12/31/9999'',''mm/dd/yyyy'')
      and rgn_nm = ''Boston''
  ) ...
');

The query doesn't return in a reasonable amount of time, and the user kills the query. I don't know if it would eventually return with the correct result.

This result where the direct TOAD query works efficiently and the openquery() version "never" returns is reproducible.

A small modification to the openquery() gives the correct efficient result: Change eff_endt to trunc(eff_endt).

That is well and good, but it doesn't seem like the change should be necessary.

openquery() is supposed to be pass through, so how can there be a difference between the TOAD and openquery() behavior?

The reason we care is because we frequently develop complex queries with TOAD directly accessing Oracle. Once we have the query functioning and optimized, we convert it to an openquery() string for use in a SQL Server application. It is extremely aggravating to have a query suddenly fail with openquery() when we know it worked as a direct query. Then we have to search for a work-around through trial and error.

I would like to see the Oracle trace files for the two scenarios, but the Oracle server is within another organization, and we are not getting cooperation from the Oracle DBAs.

Does anyone know of any driver, or TOAD, or ??? issues that could account for the discrepancy? Is there any way to eliminate the problem such that both methods always give the same result?

Answer 1

I know you asked this a while ago but I just came across your question.

I agree, they should be the same. Obviously there is a difference. We need to find out where the difference is.

I am thinking out loud as I type...

What happens if you specify just a few column instead of select * from openquery?

How many rows are supposed to be returned?

What if, in the oracle select, you limit the returned rows?

How quickly does the openquery timeout?

Are TOAD and SS on the same machine? Are you RDPing into the SS and running toad from there?

Are they using the same drivers? including bit? (32/64) version?

Are they using the same account on oracle?

It is interesting that using the trunc() makes a difference. I assume [eff_endt] is one of the returned fields?

I am wondering if SS is getting all the rows back but it is choking on doing the date conversions. The date type in oracle may need to be converted to a ss date type before ss shows it to you.

What if you insert the rows from the openquery into a table where the date field is just a (n)varchar. I am thinking ss might just dump the date it is getting back from oracle into that text field without trying to convert it.

something like:

insert into mytable(f1,f2,f3,datetimeX)
select f1,f2,f3,datetimeX from openquery( servername, '
  select f1,f2,f3,datetimeX from ... where ...
  and srvg_ocd in (
    select ocd
     from rptofc
    where eff_endt = to_date(''12/31/9999'',''mm/dd/yyyy'')
      and rgn_nm = ''Boston''
  ) ...
');

What if toad or ss is modifying the query statement before sending it to oracle. You could fire up wireshark and see what toad and ss are actually sending.

I would be very curious if you get this resolved. I link ss to oracle often and have not run into this issue.

Answer 2

Here are basic things you can check for to see what the database is doing after it receives the query. First, check that the execution plans are the same in TOAD as when the query runs using openquery. You could plan the query yourself in TOAD using:

explain plan set statement_id = 'openquery_test' for <your query here>;

select *
from table(dbms_xplan.display(statement_id => 'openquery_test';

then have someone initiate the query using openquery() and have someone with permissions to view v$ tables to run:

select sql_id from v$session where username = '<user running the query>';

(If there's more than one connection with the same user, you'll have to find an additional attribute to isolate the row representing the session running the query.)

select *
from table(dbms_xplan.display_cursor('<value from query above'));

If those look the same then I'd move on to checking database waits and see what it's stuck on.

select se.username
     , sw.event
     , sw.p1text
     , sw.p2text
     , sw.p3text
     , sw.wait_time_micro/1000000 as seconds_in_wait
     , sw.state
     , sw.time_since_last_wait_micro/1000000 as seconds_since_last_wait
  from v$session se
       inner join
       v$session_wait sw
          on se.sid = sw.sid
 where se.username = '<user running the query>'

;

(again, if there's more than one session with the same username, you'll need another attribute to whittle it down to the one you're interested in.)

If the plans are different, then you need to find out why, or if they're the same, look into what it's waiting on (e.g. SQL*Net message to client ?) and why.

Answer 3

I noticed a difference using OLEDB through MS Access (2013) connecting to Oracle 10g & 11g tables, in that it did not always recognize indexes or primary keys on the Oracle tables properly. The same query through an MS Access 2000 database (using odbc) worked fine / had no problem with indexes & keys. The only way I found to fix the OLEDB version was to include all of the key fields in the SELECT -- which was not a satisfying answer, but it's all I could find. This might be an option to try through SSMS / OpenQuery(...) as well.

Besides that... you can try some alternatives to OPENQUERY, such as:

• 4-part names: SELECT ... FROM Server..Schema.Table
• Execute AT: EXEC ('select...') at linked server

But as for why the OLEDB provider works differently than the native Oracle Provider -- the providers are not identical, and the native provider would be more likely to pave-over Oracle quirks than the more generic OLEDB provider would.