Re: Fun with the CARDINALITY and DYNAMIC_SAMPLING hint

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_at_parallon.net> To: <greg_at_structureddata.org>; <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> 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

--

http://www.freelists.org/webpage/oracle-l Received on Sun Sep 16 2012 - 05:37:42 CDT