Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: 10g optimizer and dynamic sampling
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-dbaReceived on Fri Jun 02 2006 - 14:09:13 CDT