Re: RBO changes plan from 8i to 9i

From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 13 Jul 2006 09:24:22 +0100

I wouldn't worry about this too much.

It's really a run-time optimization for nested
loops using a range scan on an index on the
inner table. Since there is a change in the
physical implementation of the nested loop,
this has echoed back into the plan - which
includes the RULE-generated plan.  However,
if you do a cost-based check, you will find that
the old and new forms of the plan do the same
arithmetic, it's only the presentation that hash
changed.

(The feature is called table pre-fetch - the idea is that it allows Oracle to do a multi-block read to get many blocks at once from the second table, rather than having to do one physical block read at a time as dictated by stepping through the index one rowid at a time. It may also reduce the level of activity on the cache buffers chains latch).

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

----- Original Message ----- From: "FreeLists Mailing List Manager" <ecartis@xxxxxxxxxxxxx> To: "oracle-l digest users" <oracle-l@xxxxxxxxxxxxx> Sent: Thursday, July 13, 2006 7:05 AM Subject: oracle-l Digest V3 #201

-----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)


--
//www.freelists.org/webpage/oracle-l

Re: RBO changes plan from 8i to 9i

Other related posts: