Re: does anybody recomends using dbms_stats.gather_schema_stats with for columnns size skewonly
- From: Alfonso León <aleon68@xxxxxxxxx>
- To: Christian Antognini <Christian.Antognini@xxxxxxxxxxxx>
- Date: Mon, 17 Jan 2005 22:19:09 -0500
Hi Chris:
>
> As pointed out by other people this is not the right way to go. Why you would
> like to avoid "ALL"?
I found this is the approach that is used at this place. When I check
the dates on the LAST_ANALYZED on columns statistics I found they were
all staled and some there weren't even calculated. When I report my
finding I was told it was on purpose that in this way Oracle gathers
the statistics as it needs them. This would sound reasonable but the
times of column statistics don't match with the times of table
statistics, I was not convinced with this explanation so I asked for
experts opinion.
Thanks everyone for your help.
Alfonso
>
> >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 => user,
> estimate_percent => 5,
> cascade => TRUE,
> method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
> options => '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
>
>
--
Alfonso Leon
--
//www.freelists.org/webpage/oracle-l
Other related posts:
