RE: RBO changes plan from 8i to 9i

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of amonte
Sent: Wednesday, July 12, 2006 2:18 AM
To: oracle-l@xxxxxxxxxxxxx
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

Other related posts: