Re: RBO changes plan from 8i to 9i

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