Re: Misterious percentage

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: gogala@xxxxxxxxxxxxx
  • Date: Mon, 31 Jan 2005 20:38:40 -0700

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=
>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=
>plausible document documenting this threshold statistics?
>Mladen Gogala
>Oracle DBA


Wolfgang Breitling
Centrex Consulting Corporation 


Other related posts: