CBO initial parameters - When, What, How to setup them.

[mson77]

Hello ALL,

This is my first message in this Forum.

Regarding this title: CBO initial parameters - When, What, How to setup them.
I have already read oracle documentation which explain the meaning of each optimization parameter.
And concerning Oracle 10g R2 (v10.2.0.1) the initial CBO parameters come as below:

SQL>show parameter optimizer;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.1
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE

My question here is: When/What/How do we change these parameters and also what are the criterias for these changes?

Some 10gR2 systems I see "optimizer mode" as "CHOOSE";
Some 10gR2 systems I see "optimizer_index_cost_adj" as 80;
And on other systems I see others optimizer parameters like "optimizer_max_permutations".

Can I have some words from you and guidance?

I am trying to figure out these parameters and on next threads (step by step) I intend to ask about awr/statspack, trace, tkprof, explain plan and execution plan.

Regards,


mson77

[debasisdas]

Welcome to TSDN.

Your question is too much theoritical to explain all that here. It sis beyond the scope of this discussion. Kindly follow a ggod book or manual or u can easily find all that by little web searching.

[mson77]

Welcome to TSDN.

Your question is too much theoritical to explain all that here. It sis beyond the scope of this discussion. Kindly follow a ggod book or manual or u can easily find all that by little web searching.

Yes.
Regarding specific a CBO topic... I have already googled for articles/tutorial, have purchased "Optimizing Oracle Performance/Cary Millsap" and read, searched for threads in related forums...thorough.

Besides some words I would appreciate to find one case: case analyzed and then CBO parameters changed accordingly just in the point of view of CBO parameters.

I can find performance tuning cases and articles in terms of SQL tuning... this I can find many. But in terms of CBO... unfortunately seldom.

Do you have any direction/link/resource that I can use regarding this topic? Also I searched in blogs from Jonathan Lewis, Tom Kyte, Kevin Closson... but no success either.

Any help... is VERY WELCOME and APPRECIATED!
Regards,

mson77

[gintsp]

Go to http://www.trivadis.com/e/knowhow/pu...onen/index.asp and browse CBO - A Configuration Roadmap by Christian Antognini.

Gints Plivna
http://www.gplivna.eu