0

I've got an Oracle server integrated with MS SQL as a linked server. Currently I'm working on the query optimization. I've found out that queries that written as following:

SELECT colName1, colName2, .. 
  FROM ORACLE.TBL_TBLENAME 
 WHERE something = @something

work very slowly. On the other hand, the same query written as:

EXECUTE ('SELECT colName1, colName2, .. 
            FROM TBL_TBLENAME 
           WHERE something :something',@something) at ORACLE

work much faster.

What I'm concerned about is the execution plan. For the first query Estimated Subtree Cost is 0.16, for the second it is 3.36. The second query performs a 'Remote scan'. I don't know whether this is good or not.

The query is supposed to run quite often (around 20 queries in 1 minute).

Ben
  • 51,770
  • 36
  • 127
  • 149
Georgy Smirnov
  • 391
  • 3
  • 10
  • How about sharing execution plans? – steve Nov 11 '12 at 18:29
  • Not sure how to embed execution plans here, but they are quite simple:` 1st) SELECT (Cost 0%) <- Compute Scalar (0%) <- Remote query (100%) ` 2nd) SELECT (0%) <- REMOTE SCAN (100%) – Georgy Smirnov Nov 11 '12 at 18:30
  • Just reading T. Kyte's book about Oracle Dev and Architecture. isn't `:something` (a bound variable), his number one improvement to make on queries, promising faster result times? Is `@something` the same? Or is it that you have cached the results after your first query, and now `:something` is the beneficiary of the cached result? (I know a little bit about databases, but not much about Oracle specifics, so all of this is all meant as an open question). Good luck to all. – shellter Nov 11 '12 at 22:21

1 Answers1

1

given you execution plan (and i'm an oracle guy not a sql server guy), it appears that the first one is doing a full table scan and filtering at the sql server end (compute scalar?), whereas the 2nd one is sumitting the filter to oracle and so much quicker.

are the stats up-to-date on the oracle table (perhaps it thinks there's only a few rows in the table so sql server is deciding its better to just fetch the whole table over and do the procesing locally?) and are there any histograms involved on "something"?

if the 2nd one is performing good for you though, is there really a problem?

DazzaL
  • 21,638
  • 3
  • 49
  • 57
  • It really looks like that (and Oracle admin told me that he has the same feeling), what I can't understand is why SQL server says that the second query is more "expensive", an wouldn't that lead to the overall sql server performance degradation (Oracle server - is main for business and got really good hardware, while SQL server is on the small virtual machine, with little expectations for more resources :( ). – Georgy Smirnov Nov 13 '12 at 06:39
  • in the Oracle world (pross the same in sql server) each plan is associated it cost. cost should not be used to decide if a sl is "expensive" or not, as its just an internal ranking algorithm to determine which plan "wins" at the time all plans are considered. if the second is responding fast (and you can check the plan oracle side in v$sql + v$sql_plan..and youd want to see an index lookup on something=:b1) then thats as good as it gets and you've done the best you can. ignore "cost": tune by IO and response time. – DazzaL Nov 13 '12 at 10:02