Re: Pass through Query to Oracle DB
From: Roy Fine (rlfine_at_twt.obfuscate.net)
Date: 05/20/04
- Next message: Fred: "Re: DataSet with multiple tables - which one has changes?"
- Previous message: Tommy Malone: "Re: Update checking"
- In reply to: GeoffD: "Re: Pass through Query to Oracle DB"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 19 May 2004 20:36:26 -0400
GeoffD
One thing to consider - try turning on sql tracing - prior to executing the
SQL comand in question, execute the following comand:
ALTER SESSION SET SQL_TRACE=TRUE
run the SQL command in question, then look in the trace file (you can grep
the BACKGROUND_DUMP_DEST directory to find the file) - you will probably
want to find the trace path name then run TKPROF to get something more
meaningful.
Do this exercise once without the hints, and once with the hints, and look
at the two execution plans -- it will show whether or not the hint was
applied (i.e. different execution plans) - and in fact, you can see the
actual SQL statement that was executed (and whether or not the layers in the
provider/protocol stack did indeed add/take away from the original SQL
statement)..
If you have a PLAN table defined, you can use this SET AUTOTRACE ON and
SQL/Plus will show the execution plan and the execution statistics. If the
plan differs at all between the SQL/Plus and the ADO.Net execution, then we
must investigate further.
It is a bit of a pain, but we achieve 100% isolation by putting the whole
lot in a view - something like this:
> CREATE OR REPLACE VIEW V_GEOFFD_DATA AS
> SELECT/*+ INDEX(o idx_order_datetime) +/
> c.customerNo, c.CustomerName, o.orderNo, o.OrderDateTime
> FROM Customer C, Orders o
> WHERE C.CustomerNo=o.CustomerNo
> AND o.OrderDateTime between TRUNC(SYSDATE) and SYSDATE
My apologies if all of this is a bit pedantic - if you are clever enough to
be writing optimizer hints, you're well beyond sql trace, explain plans, and
creating views..... :)
I hope this helps...
regards
roy fine
"GeoffD" <anonymous@discussions.microsoft.com> wrote in message
news:3D6BD9C9-CBDA-403D-9C30-897CB357BE72@microsoft.com...
> Thanks, Roy.
>
> I've already specified a hinted SQL to Oracle DB.
> eg.
> ----------------
> SELECT/*+ INDEX(o idx_order_datetime) +/
> c.customerNo, c.CustomerName, o.orderNo, o.OrderDateTime
> FROM Customer C, Orders o
> WHERE C.CustomerNo=o.CustomerNo
> AND o.OrderDateTime between TRUNC(SYSDATE) and SYSDATE
> --------------------
>
> This SQL run very fast from a SQL Navigator tools, but when it was run
from an ADO.Net Command object, it took minutes to return the data. This
lead me to believe that the hinted part of the SQL has been stripped of as
ADO.net considers it to be comments.
> As in the early version of ADO, the Command object has few more
parameters.
>
>
- Next message: Fred: "Re: DataSet with multiple tables - which one has changes?"
- Previous message: Tommy Malone: "Re: Update checking"
- In reply to: GeoffD: "Re: Pass through Query to Oracle DB"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
