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