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@xxxxxxxxx> 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@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
>


Other related posts: