Re: statistics_level parameter --- performance impact

harvinder76_at_gmail.com wrote:
> Hi,
>
> We are in the development phase and have Oracle 10.2.0.2 on Sun Solaris
> 10. By default statistics_level is set to typical and
> optimizer_dynamic_sampling =2
> We will be doing testing at our end also but It will be great if you
> can send your feedback regarding how much performance impact can this
> parameters cause that can offset the benefits of lot of auto options.
> In our application we insert about 10k rows daily in few tables and
> about 3-5M rows in 5-6 big tables. We seldomly use update operation and
> delets can be in range of 1-2% of inserts.
>
> Thanks
> --Harvinder

If you have a chance before Oracle 10.2.0.2 is used for production, buy a copy of "Cost-Based Oracle Fundamentals".

Appendix A Upgrade Headaches pg 462 from that book: "Dynamic sampling was introduced in 9.2 with a default value of 1..." "In 10g, the default level for dynamic sampling is 2, which means that any table without statistics with have 32 blocks sampled. This could prove particularly helpful when you mix global temporary tables (GTTs) with normal tables." Oracle 10.2.0.2 automatically collects statistics for objects.

Check here for a description of the STATISTICS_LEVEL parameter: http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams210.htm

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Nov 16 2006 - 13:19:37 CST