Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SIEBEL PERFORMANCE RBO to CBO

RE: SIEBEL PERFORMANCE RBO to CBO

From: Milen Kulev <makulev_at_gmx.net>
Date: Sun, 4 Feb 2007 11:51:15 +0100
Message-ID: <002801c7484a$64cf1060$14b2a8c0@trivadis.com>


Paula,
bear in mind that if your SIEBEL applications is heavily customized, the recommendations from SIEBEL support are no more , mildly formulated, "appropriate".
I have several instances running with OPTIMIZER_MAX_PERMUTATIONS = 500, or even 2000 (the default value anyway). What is much more important are the "_" paramemeters, disabling BITMAP indexes, SEMI and ANTI jons functionality. I mean , you should set the parameters _bitmap_tree_plans=FALSE,
_always_anti_join=FALSE and
_always_semi_join=FALSE , otherwise you will get some disastrous execution plans (consuming cpu like mad),
and what is more important, extremely long parse times ! These parameters I have already extensively tested on 9i. For 10g I would set also _optimizer_cost_based_transformation = FALSE.  

OPTIMIZER_INDEX_COST_ADJ to 1 ist in any case ridiculous (It works well only on Vanilla SIEBEL installations). Especially on 10g, since you must have system statistics... For some excellent explanations why, see http://www.dbazine.com/oracle/or-articles/jlewis18 , http://jonathanlewis.wordpress.com/2006/10/24/optimizer_index_cost_adj/ and http://www.jlcomp.demon.co.uk/system_stats.html  

You should pay attention to the shared pool too. The official SIEBEL Recommendation WAS cursor_space_for_time=TRUE. This recommendation could have been true at 8.x times, but not any more. The real problem is that too many SIEBEL Support people are not aware that this is obsolete. There is special note on SIEBEL supportweb stating that on Oracle 9i the database could be run with cursor_space_for_time=FALSE. Anyway, if :
1) you choose cursor_space_for_time=FALSE, be sure you have _bitmap_tree_plans=FALSE,
_always_anti_join=FALSE and _always_semi_join=FALSE .Otherwirse your system will be CPU bound (because of hards parses)
by defintion ;)
2) you choose cursor_space_for_time=TRUE, be sure that you have generously sized SHARED_POOL (~ 700-1100MB is not uncommon), and you do not use dynamic relocation of SHARED_POOL and and DB_CACHE_SIZE. Pay attentions that in this case the parameter
_kghdsidx_count should be explicitely set to 1. If you do not do this Oracle will split the shared pool in subheaps (see
the very excellent post of Tanel http://www.freelists.org/archives/oracle-l/11-2004/msg00291.html), und since the exec plans will not age out (as fast as usual), sooner or later you will get ORA-4031.  

Yes, you can always use stored outlines (or SQL Profiles in 10g, if your company have already purchased Tuning Pack), but you should be aware ( aka, do it yourself) what is the "perfect" exectution plan in each case.  

 Especially dramatic is the behaviour of the CBO when OPTIMIZER_MODE = FIRST_ROWS_10 AND you have SQL statements with ORDER BY clauses ;)
If necessary, do not be shy to create additional indexes or even manipulate some column statistics-> it will pay quickly off.  

HTH. Milen          

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of LS Cheng Sent: Sunday, February 04, 2007 2:18 AM
To: paulastankus_at_yahoo.com
Cc: oracle-l_at_freelists.org
Subject: Re: SIEBEL PERFORMANCE RBO to CBO

Hi  

Have you followed Siebel recommendatio on CBO?  

The recommendation they make makes CBO runs like RBO, for example set OPTIMIZER_MAX_PERMUTATIONS to 100 and OPTIMIZER_INDEX_COST_ADJ to 1. 30 tables joins is quite normal in Siebel, over 45 is not unusual.  

AFIAK when you switch to CBO (from Siebel 7.7) by default internally Siebel runs these SQL statements to optimize client queries  

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10   ALTER SESSION SET HASH_JOIN_ENABLED = FALSE   ALTER SESSION SET _OPTIMIZER_SORTMERGE_JOIN_ENABLED = FALSE   ALTER SESSION SET _OPTIMIZER_JOIN_SEL_SANITY_CHECK = TRUE   You should try to trace a client session and see what it is doing.  

On 2/3/07, Paula Stankus <paulastankus_at_yahoo.com> wrote:

We have made a number of changes to optimizer parameters and generated statistics in our Siebel database migrated to 10g from 8i (i.e. rule-based to cost-based optimizer). However, we still see significant response time differences between the two versions. I am thinking of using outlines to make Siebel on 10g behave and have the same execution plans as Siebel on 8i.  

Has anyone else had experience in doing this?  

Thanks in advance,

Paula


It's here! Your new message!
Get new email alerts <http://us.rd.yahoo.com/evt=49938/*http://tools.search.yahoo.com/toolbar/features/mail/> with the free Yahoo! Toolbar.

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Feb 04 2007 - 04:51:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US