RE: Fun with the CARDINALITY and DYNAMIC_SAMPLING hint
Date: Sat, 15 Sep 2012 11:03:38 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8851595461B_at_NADCWPMSGCMS10.hca.corpad.net>
It's interesting - It appears (and I haven't done any rigorous testing on this) that having certain hints before the dynamic_sampling hint causes the optimizer to do different things. For example, I had an INDEX() hint in the query and had taken it out. When I put it back in, I placed it first in the list of hints, and the plan didn't go back so I checked my original trace file report (using orasrp) and the hint had originally been after the dynamic_sampling so I moved it and ran the SQL again. This time the plan changed back to what I was expecting. (All of this was done using gather_plan_statistics in the SQL on 10.2.0.4) Just thought I'd mention that. If I had more time, I'd run some tests to see if the behavior is repeatable.
Chris
From: Greg Rahn [mailto:greg_at_structureddata.org]
Sent: Saturday, September 15, 2012 12:19 AM
To: Taylor Christopher - Nashville
Cc: oracle-l_at_freelists.org
Subject: Re: Fun with the CARDINALITY and DYNAMIC_SAMPLING hint
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<http://gist.github.com> and pastebin.com<http://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<mailto: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 - 11:03:38 CDT