Oracle Optimizer mode

[mehul_3]

As I may know, after analyzing tables;indexes and setting optimizer mode is CHOOSE,Oracle chooses best optimal path to execute a sql-query.
Is it necessary to tune manually sql-query if oracle itself tune internally choosing cost based optimization.

Waiting for reply

Regards,
Mehul

[Jean-Luc Oudart · Accepted solution]

Mehul,

The cost based optimization is as goodas it good be but knows nothing about your application(s), therefore sometimes you may have "strange" behaviour and you may have to tune the SQL itself (hints) to use a different approach.

Also, statsistics should be analysed regularly and again the caluclated path may differ at some stage due to the changes in statistics (either calculated or estimated).

see attachment

Regards
Jean-Luc

[Hein van den Heuvel]

With all stats in place, and the cost base optimizer (CBO) active through the mode=choose setting Oracle tends to 'get it right', but it may still be necessary to hand tune query, for example to put in an index hint. The more recent the Oracle version, the more right it is likely to get.
What version are you using?

IMHO the working assumption should be that the CBO is getting it right and only investigate if and when there is a sign of excessive cost. Some indication in statspack, or some response time requirement in the application not being met.
Trust but verify!

fwiw,
Hein.