Search

OakieTags

Who's online

There are currently 0 users and 38 guests online.

Recent comments

Affiliations

NO_CPU_COSTING

Vidhyasagar K asks:

Question

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 vidhyaksagar@gmail.com

Answer

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

(no_)cpu_costing hint

 Just to add a comment about effects to Mark's note. In general you should find that the cost of a table scan will increase when you enable CPU Costing (also known as "System Statistics"). This means that your improved run time is likely to be due to changes in execution path that have started to favour execution plans.  There are a few articles about system statistics on my blog that might give you more background, and a couple of links from there to other relevant articles: http://jonathanlewis.wordpress.com/category/oracle/statistics/system-stats/
 

CPU Costing

Hi Jonathan,
Thanks a lot for taking up this question and providing inputs on it. I went through the links you had provided and as well gone through your book "Cost Based Oracle Fundamentals".  By adjusting the Optimizer_Index_cost_adj and Optimizer_index_cahcing  parameter values, the queries started showing improvement. (Much better than my earlier test case results :-). As per you had mentioned, I started setting up value around 30 for Optimizer_Index_cost_adj,  is yielding me better results. I started with the tip optimizer_index_cost_adj = 100 * “single block read time” / “multiblock read time” .
I have an additonal question over here. How often do one need to look into these parameters to conclude whether the values setup is within the expected limits.
Thanks and Regards,
K.Vidhyasagar
 
 

why my system doesn't have system statistics?

hi there,
on my system, it never collected for a long time,
SQL> select * from aux_stats$;

SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- -------
SYSSTATS_INFO STATUS COMPLET
SYSSTATS_INFO DSTART 11-23-2
SYSSTATS_INFO DSTOP 11-23-2
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1875.70621
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC

SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- -------
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR

13 rows selected.

in this case, what is happending? thanks