RE: Fun with the CARDINALITY and DYNAMIC_SAMPLING hint
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <greg@xxxxxxxxxxxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Sat, 15 Sep 2012 11:03:38 -0500
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@xxxxxxxxxxxxxxxxxx]
Sent: Saturday, September 15, 2012 12:19 AM
To: Taylor Christopher - Nashville
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>>
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>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
