Re: System statistics

Even though you probably know what I think about it, here is a short feedback...

> Given our tolerance for cardinality errors why are we so concerned about
> accurately quantifying the size and performance of multi-block reads?

1. I see execution plans flipping for much less than a factor of 2 or 3.
2. You increase the likelihood to see features like cardinality feedback kicking in.

> Why not just delete system statistics and set DB_FILE_MULTIBLOCK_READ_COUNT
> to 0 (implying the maximum possible I/O size) and be done with it?

1. Even though you delete them they will be automatically re-gathered. As of 10g it is simply not possible to run a database without system statistics.
2. The auto DB_FILE_MULTIBLOCK_READ_COUNT is not good for at least two reasons:
   • Suboptimal performance is possible
   • When MEMORY_TARGET or SGA_TARGET are in use the value can change at every instance bounce. As a result, execution plans can change when you bounce an instance.

Just my two cents...

HTH
Chris Antognini

Troubleshooting Oracle Performance, Apress 2008 http://top.antognini.ch

--
http://www.freelists.org/webpage/oracle-l