Hi Mark
- I have to check if indexes are recreated. However if what you say is true
then different indexes would be used in execution plan. Right now that does
not happen only order is changed.
- I am not sure if parameters affect RBO. I thought parameters only affects
CBO?
thanks
On 7/12/06, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
> Two quick possibilities come to mind:
>
>
>
> 1) If your migration involved re-creating indexes, then you may have
> changed the relative creation times of the indexes (which is a tie breaker
> under RBO for otherwise equally ranked plans).
>
> 2) Some init parameter you had set in 8i is now (silently, or just
> somewhere you're not looking?) deprecated and/or requires the corresponding
> _parameter in 9i to be functional.
>
>
>
> Consider those shots in the dark without more details.
>
>
>
> Regards,
>
>
>
> mwf
>
>
>
> -----Original Message-----
> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org
> ]*On Behalf Of *amonte
> *Sent:* Wednesday, July 12, 2006 2:18 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* RBO changes plan from 8i to 9i
>
>
>
> 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
Received on Wed Jul 12 2006 - 12:10:28 CDT
