OPTIMIZER_DYNAMIC_SAMPLING
Date: Mon, 30 Nov 2009 21:05:04 -0800
Message-ID: <a9c093440911302105h3606b30fw4b710bcead682a25_at_mail.gmail.com>
If you are using the default (which is 2) for OPTIMIZER_DYNAMIC_SAMPLING then dynamic sampling is only used for unanalyzed tables. Setting it to 0 (off) will result in a default value of stats for unanalyzed tables. If you are using the out of the box solution of having the auto stats job run, then in reality you should have stats on all your tables, that is, unless they were created since the last maintenance window of the previous night.
I would personally recommend that OPTIMIZER_DYNAMIC_SAMPLING be left at the default and often for DSS/BI/DW systems increase it to at least 4 so it kicks in for all the possible criteria. Dynamic sampling is often quite useful to detect skew and correlation in data that would otherwise have a default density calculated.
Dynamic Sampling Levels:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats.htm#i43032
On Mon, Nov 30, 2009 at 1:17 PM, Kellyn Pedersen <kjped1313_at_yahoo.com> wrote:
>
> If you are gathering stats regularly, it may be worth while, although old-fashioned approach to look at V$SQL for sql_text like '%OPT_DYN%'
> You may be surprised how often your beautiful stats are being undermined by a solid generic solution that Oracle put in place for an "out of the box" solution.... :(
> There are a number of parameters that I tuned when I arrived here at my company because no one had thought of looking at the defaults from Oracle. It's worth the time investigating, testing and implementing those that show improvement.
> good luck!
>
> --- On Mon, 11/30/09, Neil Kodner <nkodner_at_gmail.com> wrote:
> We're using the default value of 2, across all of our 10g instances. After what I've learned/read through the last few weeks, I'm sensing that this isn't always such a good idea.
-- Regards, Greg Rahn http://structureddata.org -- http://www.freelists.org/webpage/oracle-lReceived on Mon Nov 30 2009 - 23:05:04 CST