Re: Portable DBA: Oracle

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@xxxxxxxxxxxxxxxxx> 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

Other related posts: