Re: OPTIMIZER_DYNAMIC_SAMPLING
Date: Tue, 1 Dec 2009 15:50:21 -0800
Message-ID: <a9c093440912011550o31c16d51k6641942abf6902b5_at_mail.gmail.com>
I think this brings some points that need to be reiterated:
- 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).
- 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_at_yahoo.com> 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 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 01 2009 - 17:50:21 CST