Re: 10g optimizer and dynamic sampling

Ronald Rood wrote:
> On Fri, 2 Jun 2006 16:42:46 +0200, p.santos000_at_gmail.com wrote
> (in article <1149259366.409877.203280_at_i39g2000cwa.googlegroups.com>):
>
> > to execute with
> > dynamic sampling of 4. We turn this on at the session level via
> > " alter session set optimizer_dynamic_sampling=4"
> > SELECT ... FROM TABLE where product_id = xyz;
> >
> <snip>
> > The table has 57 million records, and product_id = xyz only returns
> > 2.3 million records. That is
> > this particular product is a very small percentage of all the rows
> > in the table.
> <snip>
> >
> > So my question is:
> > When the optimizer uses dyn sampling other than 2, and it's querying
> > a very small
> > percentage of the overall table data, it appears to get it wrong...
> > Is there any configuration
> > or setting that can tell the optimizer the following:
> > " If you sample and get n results, default to dyn sampling of 2"...
> > OR
> > How can I increase the accuracy of the sampling ..other than
> > going up to higher levels?
> > Probably wishful thinking on my part, but I figure I'd ask.
>
> Why not take care for correct statistics and forget about the dynamic
> sampling ? to my opinion dynamic sampling just adds extra overhead to the
> query, increasing the response times.
>
>
> --
> With kind regards / met vriendelijke groeten,
> Ronald
>
> http://ronr.blogspot.com/
> http://ronr.nl/unix-dba

Well that's a good point, but here is the difficult thing about this system. Our customers
upload data anytime they want at any point of the day. So in the morning COL1='X' could
return 100 rows, but the same query in the evenning could yield a million rows.

Most of the queries executed on our system take anywhere from 5 minutes to 30-45 minutes, so a couple of extra seconds is for the sampling is insignificant.

-peter Received on Fri Jun 02 2006 - 14:17:11 CDT