Re: DBMS_STATS.GATHER_SYSTEM_STATS

Jonathan Lewis wrote:
>
> 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
> >
> >

Jonathan is referring to 9.x.

In 8.1.x, there are a few issues to be aware of dependent on the version (plus patchset) you are on. Of the top of my head, there were things like:

1. gather stale incorrectly misses some segments
2. gather_schema skipped the first segment in the schema
3. partition objects take longer (because "more" stats are calculated)

but nothing that some testing won't pick up

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."