RE: SIEBEL PERFORMANCE RBO to CBO

  • From: "Milen Kulev" <makulev@xxxxxxx>
  • To: <exriscer@xxxxxxxxx>, <paulastankus@xxxxxxxxx>
  • Date: Sun, 4 Feb 2007 11:51:15 +0100

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. 






Other related posts: