RE: Auto stats gathering is not sufficient - what now?

  • From: "TJ Kiernan" <tkiernan@xxxxxxxxxxx>
  • To: "Kellyn Pedersen" <kjped1313@xxxxxxxxx>, <greg@xxxxxxxxxxxxxxxxxx>, <david@xxxxxxxxxxxxxxxxxx>
  • Date: Thu, 10 Feb 2011 17:16:54 -0600

No statistics at all was a bit further off the reservation than I
planned on going.  Thanks everybody for the advice!

 

Thanks,

T. J.

 

 

From: Kellyn Pedersen [mailto:kjped1313@xxxxxxxxx] 
Sent: Thursday, February 10, 2011 12:16 PM
To: greg@xxxxxxxxxxxxxxxxxx; david@xxxxxxxxxxxxxxxxxx
Cc: TJ Kiernan; oracle-l@xxxxxxxxxxxxx
Subject: Re: Auto stats gathering is not sufficient - what now?

 

I was thrilled when Greg sent this link to me after I had complained
about this for the umpteenth time, (sill girl just argued the point on
her research instead of looking for data from Oracle that would validate
it... :))  I agree and do tire of seeing dynamic sampling as the top
elapsed time in a database that has sorely neglected statistics and
erratic execution plans.  Dynamic sampling should only *enhance* the
statistics in a database environment for the CBO, not replace it....

 
OK, off my soapbox and I promise to go take a deep breath... :)

Kellyn Pedersen

Multi-Platform Database Administrator

www.pythian.com <http://www.pythian.com/> 

http://www.linkedin.com/in/kellynpedersen

http://dbakevlar.com

 

 

 

________________________________

From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
To: david@xxxxxxxxxxxxxxxxxx
Cc: tkiernan@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Thu, February 10, 2011 10:31:48 AM
Subject: Re: Auto stats gathering is not sufficient - what now?

I would advise against that.  Dynamic Sampling (DS) does not have the
same information that you get from dbms_stats.

"The most common misconception is that DS can be used as a substitute
for optimizer statistics." 2nd sentence from
http://blogs.oracle.com/optimizer/2010/08/dynamic_sampling_and_its_impac
t_on_the_optimizer.html


On Thu, Feb 10, 2011 at 12:16 AM, David Aldridge
<david@xxxxxxxxxxxxxxxxxx> wrote:
> You could consider not gathering statistics at all -- delete current
> statistics and lock the table statistics -- and rely on dynamic
sampling.
> The usual duration of reporting queries against large tables,
particularly
> the consequences for the duration if the execution plan is incorrect,
> generally make the dynamic sampling overhead acceptable.
-- 
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l



 

Other related posts: