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 //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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of LS Cheng Sent: Sunday, February 04, 2007 2:18 AM To: paulastankus@xxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx 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@xxxxxxxxx> 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.