Re: 11G Statistics Collection

# Neither is it desirable.  This fixation with "collect statistics
every day to resolve all performance ills" is getting beyond ridiculous...

Oracle seems driven these days to automatically collect things. I am ok with Oracle getting stats for its own schemas but not going to let things happen automatically for "my schemas".

One way ot doing that is by this:

DBMS_STATS.SET_PARAM(‘AUTOSTATS_TARGET’,’ORACLE’);
# I'm always reminded of Peoplesoft - an ORACLE product, let's not
forget it! - where temp tables are used all over the place. Truncated, populated, then queried upon and cleared after. Yeah right, of course the DBA needs to calculate stats every day. Right when they are empty!  It's such a great way of stuffing up performance!...

For the databases I control I use a compromise hybrid solution. Several times a day ( and at night ) I look for empty tables in schemas and if empty I delete table stats and use dynamic sampling ( for me at level 2 ).

Tables that have had stats deleted are also checked multiple times a day and once a "significant" number of rows are in the table stats are re-generated.

Obviously one has to be aware of batch and weekend schedules and special cases.

One also has to be aware of the impacts of rolling invalidation and recent changes in recent releases as to when SQL in the shared pool is invalidated.

In other words be careful and don't forget that the golden olden days of all the impacted SQL getting tossed out when new statistics are available have changed. Received on Sun Apr 15 2012 - 12:06:56 CDT