Re: DBMS_STATS.GATHER_SYSTEM_STATS

It's good, but I think the change-over is going to be a bit like the change-over from rule-based to cost-based. If you don't understand how it works you may see some strange events.

sys.aux_stats$ will record:

    assumed CPU speed in MHz
    single block read time in milliseconds     multiblock read time in milliseconds     typical achieved multiblock read.

This has three main effects.

Instead of Oracle assuming that single block reads are just as expensive as multiblock reads, Oracle KNOWS the relative speed. This is roughly equivalent to setting optimizer_index_cost_adj according to the db file XXXX read average wait times - it will encourage Oracle to use indexed access paths instead of tablescans.

Secondly, Oracle will use the 'achieved' average multiblock read count to calculate the number of reads required to scan a table, instead of using an adjusted value of db_file_multiblock_read_count. Since many people use a value of dbfmbrc that is inappropriate, the result of this is that Oracle is likely to increase the cost, and therefore decrease the probability of doing tablescans (and iff scans).

Finally, Oracle knows that a to_char(date_col) = 'character' costs a lot more CPU than number_col = number, so it may change its choice of index to use a less selective index if the consequential cost of using that index is lower on CPU usage. (Also, Oracle will re-arrange the order of non-access predicates to minimise CPU costs, but the difference in performance is not likely to be visible).

Overall - it's definitely a good thing. In practice, you may see a much stronger bias towards indexed access paths which MAY impact performance.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Stephan Bressler wrote in message ...


>Hi,

>

>anybody with experience with this new feature out there?

>

>I'm interested in bugs, influence on optimizer, general experience.

>

>Regards

>Stephan

>

>