Toad World® Forums

SQL Optimizer only gives Alternate with "no CPU costing" hint.


Many SQL statements I put in the SQLOptimizer only come up with an alternate that is only a smidge better than the original. And the only thing different is that the alternate has an added “no CPU costing” hint. I read that its better to use fewer hints or no hints at all with Oracle 10g (and beyond). So why does the SQLOptimizer continually add the “no CPU costing” hint as the alternative?



The improvement of a SQL is depending on the complexity of the original SQL syntax, for simple and quick SQL statements, most database optimizers can handle it very well, so the chance to get improvement is low, unless there is an obvious problem in your SQL.
You can also increase the Optimize Intelligence Level to investagate more alternatives.
NO_CPU_Costing hint is used to tell Oracle to use IO costing only, since some SQL statements query plan will be changed by 10g new CPU costing method, if it caused problem to your SQL statements, it is better to use no CPU costing method. Oracle provide Hints for experience users to influence their SQL to solve performance problem, if it is problem, you should use it, if it is not a problem or the improvement is little, you should consider not to use it. But there is another good side effect of using Hints in your SQL, since a hint to a SQL will normally fix the query plan of the SQL; it makes your SQL run more stable from time to time.