RE: RBO changes plan from 8i to 9i

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 12 Jul 2006 08:14:50 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKIEONIEAA.mwf@rsiz.com>

Two quick possibilities come to mind:

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).
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 - 07:14:50 CDT