Re: RBO changes plan from 8i to 9i

Hi Sergey

I am sure CBO is not supported. Words from Siebel. There is a Siebel alert about it (I can send it to you tomorroow). In fact, this is hilarius, one of requirement when you migrate to 9i is set optimizer_features_enable to 8.1.7!!!

One of the problem with Siebel and CBO is you have these monster joins with over 15., 20 tables. The parsing time is quite horrible. Pinning would fix the problem but.... CBO is great but with these Peoplesoft, Siebel, Billing Applications you must spend tons of time (months probably years) fine tuning the table, index statistics!

The migration to 9i is in fact a preparation to migrate to Siebel 7.8 which supports CBO but that is another project in 10 months time at least.

I am concerned with the execution plan changes because I was expecting same plans with RBO.

tia

Alex

On 7/12/06, Sergey Popov <sspopov_at_gmail.com> wrote:
>
> 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_at_gmail.com> 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
> >
> >
>
>

--
http://www.freelists.org/webpage/oracle-l