Oracle’s Cardinality Hint, Profiles, Outlines and SQL Baselines
This is more of a bookmark for a future blog post on using the Cardinality hint. This post by Greg Rahn give some example and I posted in the comments some more detailed examples:
Here is another excellent post by Kerry Osborne on SQL Baselines:
which made me ask in the comments why one would want to use Outlines vs Profiles vs Baselines, in particular what advantages if any Profiles have.
Interesting question. I had an online dialog with a guy name Randolf Geist a year ago or so where he convinced me to look closer at SQL Profiles. Up until that point I had only seen SQL Profiles that were created by the SQL Tuning Advisor and I was not very impressed. They tended to go sour after a little time because of the OPT_ESTIMATE hint, but I digress. The answer is yes, I think SQL Profiles have a couple of advantages over Outlines at this point.
1. They have the ability to be used on multiple statements (ones that are the same except for literals) using the force matching signature bit.2. They are newer. I’m starting to worry about Outlines getting buggy due to lack of attention. Documentation says they are deprecated and although they continue to work in 11g, I am a bit leery of continuing to use them at this point. (the whole hint based mechanism is tricky enough as it is).11gR2 includes a procedure to migrate Outlines to Baselines by the way (DBMS_SPM.MIGRATE_STORED_OUTLINE). At any rate, I am not implementing Outlines at this point.