Re: SQL Tuning Regarding System CPU Stats

<andrew.markiewicz_at_gmail.com> wrote in message news:1179498973.655441.180270_at_k79g2000hse.googlegroups.com...
>
> According to Jonathon's book "Cost Based Oracle Fundamentals", p. 82,
> "The default value for this parameter (_optimizer_cost_model) is
> 'choose', and 9i will choose to use CPU costing only if the system
> statistics exist, but 10g will always choose to use CPU costing and
> then synthesize some statistics if there aren't any in place....".
> So db version does matter to how the CBO will generate execution
> plans. I don't particularly care for the idea of Oracle generating
> CPU stats that will then be used to generate SQL statements, therefore
> we must gather system stats.

I've come round to the idea that the default 10g approach with its 'noworkload' system statistics is likely to be the most stable option. The instance estimates an I/O seek time and an I/O transfer rate (which default to 10m/s and 4.096 bytes per millisecond respectively) then calculates the sreadtim and mreadtim based on your initial setting for the db_file_multiblock_read_count (but doesn't recalculate if you change the setting with an alter session or alter system call, at least for the last version I checked). The net result is that multiblock reads are assumed to be more expensive than single block reads by a reasonable factor - and this tends to be good enough.

There will be a note about this on my blog in a day or so - there's also a note about what to do with the optimizer_index_cost_adj when you enable system statistics.

> Also, we
> currently set the
> optimizer_index_cost_adj and optimizer_index_caching values which will
> continue to have an influence on the CBO in conjunction with the CPU
> stats. So the use of those values will need to be reviewed as well.
>
Received on Sun May 20 2007 - 13:14:08 CDT