Re: Fun with the CARDINALITY and DYNAMIC_SAMPLING hint
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 16 Sep 2012 11:37:42 +0100
It's a standard problem once you've spotted a possible anomaly - how much
time can you afford to spend checking it out.
If it's interesting I can usually make some time - but in a production
environment, unless it's a threat to NOT know the answer, there rarely is
time.
One observation on your example (and I'm not suggesting you have to go back
and do any rigorous testing) is that the dynamic sample is randomly
generated - so different runs of the same query COULD get different
execution plans with dynamic sampling because the sample chosen. Noting
that the plan "didn't go back to the original" is not sufficient if the new
plan (a) still used the index you had index hinted or (b) took a path where
the index you had hinted because illegal (thus making the hint invalid).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: <Christopher.Taylor2@xxxxxxxxxxxx>
To: <greg@xxxxxxxxxxxxxxxxxx>; <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Saturday, September 15, 2012 5:03 PM
Subject: RE: Fun with the CARDINALITY and DYNAMIC_SAMPLING hint
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
--
//www.freelists.org/webpage/oracle-l
Other related posts: