Sir, Can you please highlight the usage of No_CPU_Costing hint. My system is a large Retail database and in many places we have huge queries. So optimization of queries is the topmost priority in my job. Many times I wonder in my queries if I would have used the hint No_CPU_Costing hint, my plan cost got lowered drastically and in certain cases, even the execution time is very less compared with the original. But till now I resisted myself in using this particular hint, since I don't have a clear idea whether this can be used extensively. Can you please advice about its usage. Thanks, K.Vidhyasagar email@example.com
Until one of our truely knowlegable members can reply you can look at the following Oracle note to see how Oracle calculates cpu costing: How To Calculate CPU Cost [ID 457228.1]. CPU costing is always used in 10g by default according to How to Determine that a Query is Optimized Using CPU Costing in 10g and above [ID 743834.1] and there is a known issue if you use cpu costing with distributed queries: POOR PERFORMANCE OF DISTRIBUTED QUERY WHEN CPU COSTING IS ENABLED [ID 883233.1].
Basically cpu costing is a way for the cbo to take into account the physical cost of single and multi-block IO plus cpu into its calculations.
If use of the hint to not cpu cost helps by actually improving run time in many cases then if you have system stats calculated you may want to recollect them or go back to using the defaults.
Mark D Powell
Mark D Powell