Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 29 Nov 2010 @ 21:13:40 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Sampled statistics in a production environment
 
From:   Ballinger, Carrie

JK,

There are two methods of changing the default 2% sampling size:

     1.  Systemwide:  DBS Control setting Internal Field  46.
             CollectStatsSample     = 2 (percent)

     2.  At the session level:  DIAGNOSTIC "COLLECTSTATS, SAMPLESIZE=30" ON FOR SESSION;

For some indexes, the optimizer may change the sampling percent to be greater than what you specified. This is normal behavior.

With indexes, blocks at a time are read, so the actual number of rows sampled are rarely equal to the percent specified. Plus NUSIs usually carry many base table row ID for a given index value, and the percentage is applied to the NUSI rows, not the base table rows ID within the NUSI rows.

So you often get a much higher percentage sampled than you intended with NUSIs. For example, I collected stats an index with only 25 values with sample size at 30% and ended up getting 60% of the rows sampled.


Thanks, -Carrie



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023