Re: The exact difference between SQL profile and baselines in Oracle 11

It looks like that the underlying basic principle of both, SQL Profiles and Baselines are still the same as Stored Outlines: A set of hints that are applied to a SQL Statement.

However, there are many differences:

1. SQL Profiles are supposed to help the optimizer to come up with a better execution plan that is based on better cardinality and cost estimates. Hence a SQL Profile is usually generated by the SQL Tuning Advisor that runs the CBO in "offline" optimization mode where parts of the SQL statement are actually executed to compare the estimated with the actual cardinalities. Based on that offline optimization the SQL Profile will contain corresponding "correction" hints that allow to correct statistics and cardinalities.

This also means that SQL Profiles are only available with EE + Diagnostic + Tuning Pack. SQL Plan Baselines is an EE feature that doesn't require any additional options (not sure why it is not available with SE though).

2. SQL Plan Baselines are complete sets of hints (the same as Stored Outlines in that respect) that "lock in" a plan. However, in comparison to Stored Outlines they offer the following advantages:

• The SQL Plan Baseline also stores the PLAN_HASH_VALUE of the desired execution plan. If after applying the stored hints the plan cannot be reproduced (meaning it results in a different PLAN_HASH_VALUE) the baseline will be rejected / ignored and the CBO will continue with normal costing. This should prevent issues that can happen with Stored Outlines that cannot be applied completely but will potentially result in a "disaster" plan due to partially applied hints
• There can be multiple accepted plans for the same SQL Statement (you can have only a single Stored Outline) - this is for example crucial for Adaptive Cursor Sharing to work in conjunction with SQL Plan Baselines or other "multiple child cursors with different plans" scenarios.
• The CBO does normal costing in addition to the accepted SQL Plan Baseline and adds "non-accepted" plans that can be verified and evolved if they turn out to be superior to the locked plan(s). This allows Plan Stability but at the same time gives the ability to arrive at better plans if desired (requires some kind of activity, an automatic evolution is not enabled by default). This addition of unaccepted plans can be avoided by marking the SQL Plan Baseline as "fixed" - this should potentially speed up the parsing phase but of course defeats one of the main advantages of SQL Plan Baselines over Stored Outlines.
• SQL Plan Baselines allow to apply a set of hints to an unhinted version of the same statement easily - as shown in various blogs like Jonathan's or the Optimizer Group. So this is basically the ability to get the desired plan for a given SQL Statement by applying hints but without the need to change the underlying application code by adding hardcoded hints. The same could be achieved with Stored Outlines but was a quite messy process

So you can think of SQL Plan Baselines as "Stored Outlines on steroids". And as described in the mentioned blog posts you can have both, a SQL Profile and SQL Plan Baseline for the same SQL Statement. The SQL Profile will be used by the CBO to generate the execution plan. If there are for example multiple accepted plans for the same SQL Statement, then the SQL Profile might drive the CBO to use another accepted plan than without the SQL Profile.

And finally - yes, you can "misuse" SQL Profiles easily to act like a Stored Outline by manually generating a SQL Profile that contains a (full) set of hints. The SQL Profile doesn't offer the above mentioned features though. There is one particular feature however that is unique to SQL Profiles: You can enable the "force matching" option that basically allows to apply the SQL Profile to any SQL Statement that would be similar if literals were replaced by bind variables (like CURSOR_SHARING = FORCE) - this is something that is not possible with SQL Plan Baselines, they only work with exact text matching and are therefore useless for SQL Statements that differ only in literals.

I've elaborated about this here:
http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html

Hope this helps,
Randolf Received on Tue Oct 11 2011 - 04:13:01 CDT