Re: Fun with the CARDINALITY and DYNAMIC_SAMPLING hint

From: Greg Rahn <greg_at_structureddata.org>
Date: Fri, 14 Sep 2012 22:18:59 -0700
Message-ID: <CAGXkmivE3i+bLV2Jz2oJnrtnHPti6PPq4kmUKqqpPo-_V1OMFg_at_mail.gmail.com>

I have no recollection that there would be any incompatibility between those hints.
Both cardinality and dynamic sampling are only for the table estimates, and as mentioned, if you want to influence join cardinality then OPT_ESTIMATE is required. Or, given you know what the plan should be, just explicitly set the join order with a full hint directive. FYI places like gist.github.com and pastebin.com are great for putting up text that is best viewed in fixed width w/o line wraps, etc.

On Fri, Sep 14, 2012 at 8:11 AM, <Christopher.Taylor2_at_parallon.net> wrote:

> Actually, this behavior I was noticing was:
>
> specify cardinality(tableA xxx) dynamic_sampling(tableB,9)
>
> With just dynamic sampling I get a good plan but I noticed the cardinality
> estimates for tableA were off, so I added the cardinality hint for tableA
> and the plan went back to the previous plan when not using dynamic_sampling.
>
> (again version 10.2.0.4)
>
> I'd provide some formatted details, but it doesn't seem I can send RTF to
> the list from our exchange server.
> I'll try to come up with a test case and see if I can mail it later using
> my gmail account.
>
> --

Regards,
Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter <http://bit.ly/v733dJ> |  linkedin <http://linkd.in/gregrahn>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Sep 15 2012 - 00:18:59 CDT