Re: RBO changes plan from 8i to 9i

That is great. I wonder is this prefetch mechanism explained anywhere in the documentation?

Thanks all and specially Jonathan for your detailed explanation

Regards

Alex

On 7/13/06, LS Cheng <exriscer_at_gmail.com> wrote:
>
> Hi Alex
>
> The table prefetch mechanism can be disabled by setting
> _table_lookup_prefetch_size to cero if you wish to use the old mechanism.
>
>
>
> 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