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
>DBMS_STATS.GATHER_SCHEMA_STATS
>with method_opt FOR COLUMNS SIZE SKEWONLY (not using the ALL) and
>estimating with dbms_stats.auto_sample_size, options GATHER and CASCADE =
true.
>
>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 =
following:
- Activate MONITORING
- Periodically call DBMS_STATS with the following parameters
dbms_stats.gather_schema_stats(
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 =
1%).
- 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 =
statistics).
HTH
Chris
--
//www.freelists.org/webpage/oracle-l
Other related posts:
