Re: SQL Tuning Regarding System CPU Stats

On May 20, 1:14 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <andrew.markiew..._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.

(Not sure why text is not showing up. Third time's a charm??)

Thanks Jonathon. I will check out your blog.

> 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.

This I find interesting. Isn't this the same effect as the OICA parameter? Relative cost of index access vs table scan. What's old is new again.
It sounds like measuring the exact CPU stats is a panacea in theory, but in practice it is not stable enough to rely on, at least not yet. Received on Mon May 21 2007 - 16:06:17 CDT