Re: Fun with the CARDINALITY and DYNAMIC_SAMPLING hint

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 14 Sep 2012 14:20:41 +0100

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


Other related posts: