RE: Auto stats gathering is not sufficient - what now?
Date: Thu, 10 Feb 2011 17:16:54 -0600
Message-ID: <0EC85B1F3B2DF8409BF66DC182F495D9858C23_at_DEEDS.pti-nps.com>
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_at_yahoo.com] Sent: Thursday, February 10, 2011 12:16 PM To: greg_at_structureddata.org; david_at_david-aldridge.com Cc: TJ Kiernan; oracle-l_at_freelists.org 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
From: Greg Rahn <greg_at_structureddata.org>
To: david_at_david-aldridge.com
Cc: tkiernan_at_pti-nps.com; oracle-l_at_freelists.org
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_at_david-aldridge.com> 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 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 10 2011 - 17:16:54 CST