OPTIMIZER_DYNAMIC_SAMPLING
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: kjped1313@xxxxxxxxx
- Date: Mon, 30 Nov 2009 21:05:04 -0800
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@xxxxxxxxx> 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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
