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. Chris -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis Sent: Friday, September 14, 2012 8:21 AM To: oracle-l@xxxxxxxxxxxxx Subject: Re: Fun with the CARDINALITY and DYNAMIC_SAMPLING hint Which version are you on. I've just done a couple of simple checks on 11.2.0.3 and there doesn't seem to be any unreasonable conflict. Of course, if you specifiy table-level dynamic sampling and specify the single table cardinality for the same table then you have to expect one of the hints to "lose" - and in this case the cardinality hint overrides anything the dynamic sampling may have done. 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: <oracle-l@xxxxxxxxxxxxx> Sent: Thursday, September 13, 2012 9:33 PM Subject: Fun with the CARDINALITY and DYNAMIC_SAMPLING hint | (Thanks to Jonathan Lewis, Greg Rahn (and others) I've been learning a lot about tweaking the execution paths for a particular problematic SQL. | A special thank you to you guys for all the blog posts (a tremendous source of knowledge) that you write at Oracle Scratchpad and StructuredData!) | I was playing around with the hints mentioned in the subject for a particular problematic query we're working on and using dynamic_sampling I could reduce the performance from 25M lios to 5M lios (which I think is still probably too high for this complex DSS query). | | However, when I added the CARDINALITY hint, it seems to immediately disallow the dynamic sampling hint (or something)? | | Anyone know if these 2 are compatible? It doesn't seem so. | | Also, as a "fun" learning experiment, I played around with cardinality hints that were completely unrealistic such as 9999999999 (9 billion) - interesting explain plans doing things like that :) Performance not so great, but was still interesting to see how you can move tables around in the xplan based on what you know of them. | | Chris | | -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l