RE: does anybody recomends using dbms_stats.gather_schema_stats with for columnns size skewonly

  • From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
  • To: <aleon68@xxxxxxxxx>
  • Date: Mon, 17 Jan 2005 09:06:41 +0100

Hi Alfonso

>We are estimating statistics for each schema of our aplication using
>with method_opt FOR COLUMNS SIZE SKEWONLY (not using the ALL) and
>estimating with dbms_stats.auto_sample_size, options GATHER and CASCADE =
>does this approach will estimate the estadistics for all columns using
>size 1 if the data is not skewed? would it do it only if needed?

As pointed out by other people this is not the right way to go. Why you =
would like to avoid "ALL"?

>What is the recomended aproach to collect the statistics?

It really depends. Anyway, as starting point I usually use the =

- Periodically call DBMS_STATS with the following parameters

    ownname          =3D> user,
    estimate_percent =3D> 5,
    cascade          =3D> TRUE,
    method_opt       =3D> 'FOR ALL COLUMNS SIZE SKEWONLY',
    options          =3D> 'GATHER STALE'

Some remarks:
- Periodically means everything makes sense for your application! Daily =
is usually not so bad, but sometimes weekly or monthly is better. One of =
my customers even refreshes stale statistics every hour.
- An estimate percent at 5% is for large tables (e.g. larger than 10/50M =
rows) too much. Therefore you should reduce it if necessary (e.g. try =
- If SKEWONLY gathers too many histograms you should either try AUTO or =
otherwise switch the gathering at table level and specify a list of the =
columns (this method is usually only needed if you have an important =
amount of data or if you have a very short time to refresh the =



Other related posts: