That is great. I wonder is this prefetch mechanism explained anywhere in the documentation?
Thanks all and specially Jonathan for your detailed explanation
Regards
Alex
Hi Alex
The table prefetch mechanism can be disabled by setting _table_lookup_prefetch_size to cero if you wish to use the old mechanism.
On 7/12/06, amonte <ax.mount@xxxxxxxxx> wrote:
> 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 >