If you trace that further with a 10046 level 8 trace you'll find that
while "sample (51.0)" looks like it does a 50% sample, that means 50% of
the rows are sampled which means that practically 100% of the blocks are
read, which is generally what you really are interested in from a
performance point of view - which is why you chose estimate_percent=>50
over NULL in the first place. You didn't really save anything. Probably
on the contrary because with a full compute Oracle can just do a full
table scan which is more efficient than a 50% sample which ends up
reading 100% of the blocks.
It gets even worse. Practically, any estimate_percent over ~ 1% is
likely going to read almost 100% of the blocks. I guess the actual
threshold would be somewhere around 100*(1/rows_per_block)%
Of course as always it depends. Here on the # of rows per block. If you
have only 1 row/block then a row sample is effectively the same as a
block sample. But I haven't tested what the actual effect on number of
blocks read is.
Niall Litchfield wrote:
> On Thu, 9 Dec 2004 09:44:25 -0600, Jesse, Rich
> <Rich.Jesse_at_quadtechworld.com> wrote:
>
>>Without looking, doesn't estimate_percent >50 cause a compute? I remember it did on ANALYZE, but I don't remember with DBMS_STATS...
>
>
> I won't post all 2k of the 10046 trace but
>
> PARSING IN CURSOR #7 len=76 dep=0 uid=64 oct=47 lid=64 tim=23455557015
> hv=926033817 ad='69ede8d4'
> BEGIN dbms_stats.gather_schema_stats('UTILS',estimate_percent => 51); END;
> END OF STMT
> PARSE #7:c=100144,e=394825,p=35,cr=270,cu=0,mis=1,r=0,dep=0,og=1,tim=23455557002
>
> generates sql of the form
>
> insert /*+ append */ into sys.ora_temp_1_ds_14 select /*+
> cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)
> no_monitoring */ <COLUMNLIST> from "UTILS".<TABLENAME> sample (
> 51.0000000000) t
>
> so I'd say that the answer was no. It does the sample you request.
>
>
--
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 09 2004 - 11:28:36 CST
