Re: RBO changes plan from 8i to 9i

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 3 Aug 2006 14:41:21 +0100
Message-ID: <00b801c6b702$815affc0$0200a8c0@Primary>

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:

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_at_gmail.com> To: <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> 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_at_jlcomp.demon.co.uk> 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

Received on Thu Aug 03 2006 - 08:41:21 CDT