Re: Misterious percentage

I believe that threshold was for analyze (50%). I don't think there is an explicit threshold for dbms_stats.
dbms_stats uses the sample option on the select. So at estimate_percent=50 and block sampling it samples on average every second block. At higher percentages you practically get to an implicit full scan. If you are using row sampling you get to a practically full scan much sooner than 50% since blocks usually have more than 1 row. Let's say you have 10 rows/block on average. In order to get a 10% random row sample, you are reading close to all blocks. That has been confirmed by tracing a gather_table_stats with a 10046 level 8 trace. Unless you have either very big rows (=few rows/blocks) or a high freespace percentage, you need to use estimate_percent < 1 in order to get a significant reduction in blocks read for the analyze.

At 08:16 PM 1/31/2005, Mladen Gogala wrote:
>I once heard that there is a threshold percentage for DBMS_STATS after whi=
>ch
>the whole table is implicitly analyzed, but I am unable to find that
>threshold percentage on ixora, asktom or Metalink. Can anyone point me to a=
>=20
>plausible document documenting this threshold statistics?
>--=20
>Mladen Gogala
>Oracle DBA
>
>
>--
>http://www.freelists.org/webpage/oracle-l

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l