Re: RBO changes plan from 8i to 9i



You might be missing the line which says:
I wouldn't worry about this too much.

;)


There are two reasons why you might not see much change:

a) The principal target seems to be to reduce the I/O queueing overheads on physical reads:

(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,

b) I've typically seen the plans change only in the last table of a multi-table join. So if you have 30-table
joins, then any change due reduced logical I/O and
latch acquisition may well be insignificant (one table out of 30)


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: "amonte" <ax.mount@xxxxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, August 02, 2006 10:35 PM
Subject: Re: RBO changes plan from 8i to 9i



Hi all again


I just finished verifying execution plan changes in 9i after migrating from 8i.

I have collected statistics for around 40 Queries (quite complex since this
is Siebel, 30 table joins is not suprirsing) which changed execution plan
due to prefetch mechanism. To my suprise the statistics is so similar that I
am really in doubts if prefetch is doing effect at all.

I see queries with virtually same number of fetch cpu time, fetch query
reads, fetch current reads in 8i and 9i.

Am I missing something?


On 7/13/06, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote:

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



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


Other related posts: