topic Re: Oracle Optimizer mode in Operating System - HP-UX

Oracle Optimizer mode

mehul_3 — Thu, 08 Sep 2005 04:36:50 GMT

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

Re: Oracle Optimizer mode

Jean-Luc Oudart — Thu, 08 Sep 2005 04:54:43 GMT

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

Re: Oracle Optimizer mode

Hein van den Heuvel — Thu, 08 Sep 2005 06:48:21 GMT

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.