Fun with the CARDINALITY and DYNAMIC_SAMPLING hint

From: <Christopher.Taylor2_at_parallon.net>
Date: Thu, 13 Sep 2012 15:33:15 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E885158F3EF2_at_NADCWPMSGCMS10.hca.corpad.net>

(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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 13 2012 - 15:33:15 CDT