question

thecoop avatar image
thecoop asked

Oracle v SQL Server CBO

After coming from a SQL Server background, the oracle query optimizer seems to be much less 'sacrosanct' than on SQL Server. On SS, it is very much discouraged that you use query hints (it is practically unsupported), whereas on oracle it is expected that you will use hints to get the performance you need.

Is this due to the SQL Server optimizer and statistics being much better than Oracle, or is it due to the differences in culture - Oracle is much more 'hands on' than SQL Server, which is practically a black box for your data?

optimizerculture
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
KillerDBA avatar image
KillerDBA answered

Maybe the principal reason is that the Oracle optimizer isn't as good.

On SQL Server, when we've resorted to index hinting it's part of a journey of discovery and the ultimate discovery is always the same: there was something wrong with the initial query. After we fix whatever was really wrong with the initial query (bad join, for example), index hinting is never necessary.

I don't think we've used index hinting in any production query since SQL Server 6.5.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

HillbillyToad avatar image
HillbillyToad answered

Oracle has some catching up to do. What version of oracle are you running? The Cost Based Optimizer is fairly new and really only became viable in 9iR2. The 11gR2 optimizer is WAY better than it was before.

That being said, there's nothing wrong with a HINT in Oracle. It gives you an alternative to override the Optimizer...go search Metalink awhile and you'll see where Oracle reccommends using Hints to solve performance issues.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.