I wouldn't worry about this too much.
;)
There are two reasons why you might not see much change:
(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,
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
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
-- //www.freelists.org/webpage/oracle-l