Re: Has anyone experimented with OPTIMZER_INDEX_COST_ADJ???

Comments inline.

Nuno Souto wrote:
>
> On Thu, 31 May 2001 00:23:48 GMT, "David Sisk" <davesisk_at_ipass.net>
> wrote:
>
> ...
>
> One side effect of FIRST_ROWS and low OPTIMIZE_INDEX_COST_ADJ: your
> export dictionary views become exceedingly slow. I had to modify them
> to force RULE and re-compile the lot. Now works fine. No, I don't
> have stats on SYS tables at all!

The reason for this is that CHOOSE will use RBO when reading SYS tables because they don't have (or should not have) statistics. If you set FIRST_ROWS, you force the CBO without any statistics which lead to very bad execution times as you experienced with exp because here a lot of SYS tables are read. That is the reason why you should *not* set ALL_ROWS or FIRST_ROWS as goal but only CHOOSE or RULE (again: you can set it but you should not). If Oracle would only mention the reason for this advice many people would easily follow it.

Martin Received on Fri Jun 01 2001 - 02:01:42 CDT