Re: OPTIMIZER_DYNAMIC_SAMPLING
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- Date: Tue, 1 Dec 2009 15:50:21 -0800
I think this brings some points that need to be reiterated:
1) dynamic sampling should not be used as a substitute for gathering
stats, it is mainly designed to augment "static" statistics (and can
help in emergencies where no stats exist).
2) when tables or partitions are bulk loaded, stats need to be
collected immediately (partition and possibly global) and generally it
is best to do this as part of the ETL/ELT process and not a process
that DBAs own.
On Tue, Dec 1, 2009 at 8:24 AM, Kellyn Pedersen <kjped1313@xxxxxxxxx> wrote:
>
> I think what Greg is saying here is incredibly important, too. As much as
> I'm the "Queen of TMI" in person, I seem to falter in coming across via
> email the same way. I work in a large datawarehouse environment where there
> are partitions being exchanged and truncated without any thought to
> statistics. As new partitions are brought in without any statistics, this
> was the area we found issue with dynamic sampling. The developers here would
> say, "but the previous DBA's gathered statistics on the table back in
> March!", not thinking about all the new partitions being created daily and
> that dynamic sampling was needed by the CBO for those to form a solid
> execution plan. It can not be guaranteed to be the SAME execution plan that
> the stats would have offered. That was the downfall...
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
