RE : Re: RBO changes plan from 8i to 9i

Hi,
We have Siebel7 on 9.2 working with CBO. And it is supported by Siebel/Oracle 
too. Note that you may have to apply the 9.2.0.7 patchset as some bugs on 
queries with 22+ tables in FROM clause are fixed.
Regards

Sergey Popov <sspopov@xxxxxxxxx> a écrit : Hi,
  
 I also got Siebel on my immediate responsibility list a couple of months ago. 
The DB is already on 9i and I'm trying to convince app owner to try CBO instead 
of RBO in test. 
 Were you able to confirm that RBO is the only supported optimizer for Siebel? 
The only note I was able to find so far on this is "Siebel Server Installation 
Guide fo Microsoft Windows version 7.5.3". Over on page 307 it says: 
 ------------------------
 NOTE: 
Siebel optimizes and certifies Siebel applications on a standard Oracle 
installation using Rule-Based Optimizer (RBO). Using advanced features like 
Cost-Based Optimizer (CBO) and table partitioning are not supported and could 
have a detrimental effect on query performance.  If there is a reason to 
believe that the issues are caused by CBO, table partitioning, or other 
database features that are not currently supported, Siebel Support will request 
that you reproduce issues against a standard installation using RBO. 
 
--------------------------  
 With CBO making much better decisions on execution plan I am not really sure I 
want to talk to Siebel side of Oracle about performance of their application at 
all.
 
 As far as your execution plan changes I would agree with Alex. Optimizer did 
not switch over to a new index but changed the order of the steps. 
  
  
 Sergey
 
 On 7/12/06, amonte <ax.mount@xxxxxxxxx> wrote:  Hi all

I am migrating a Siebel 7.x CRM database from 8i to 9i under HPUX. This version 
of Siebel only supports RBO therefore we will be using RBO in 9i as well.

I noticed that many execution plan changed when migrated to 9i. I thought RBO 
is not affected by  init.ora parameters, version changes and its development 
stopped ages ago. 

Any clues?

Most change are like this:

ORIGINAL:
-----------------
Execution Plan
----------------------------------------------------------  
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (ORDER BY)
   2    1     NESTED LOOPS
   3    2       INDEX (RANGE SCAN) OF 'S_ESCL_RULE_M1' (UNIQUE)
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'S_ESCL_STATE'  
   5    4         INDEX (RANGE SCAN) OF 'S_ESCL_STATE_M1' (NON-UNIQUE)

9i:
--------------------
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE  
   1    0   SORT (ORDER BY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'S_ESCL_STATE'
   3    2       NESTED LOOPS
   4    3         INDEX (RANGE SCAN) OF 'S_ESCL_RULE_M1' (UNIQUE)
   5    3         INDEX (RANGE SCAN) OF 'S_ESCL_STATE_M1' (NON-UNIQUE)  


tia
 


 


Regards,
Marius
                
---------------------------------
 Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail et son 
interface révolutionnaire.

Other related posts: