Debate: SPA vs. SPM vs. ACS, SPM vs. sql_profiles etc.

Well there are many sources to explain about the functionality of

• SQL Stored Outlines
• SQL Profiles
• SQL Performance Analyzer
• Adaptive Cursor Sharing
• SQL Plan Management

Indeed all these tools are available to give the plan stability but with different ways.

Basics:- Plan instability can occur due to bind value differences. Binds are used to reduce the hard parsing when application is using same statement.

Oracle introduced the bind peeking feature in Oracle 9i. With bind peeking, the Optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This allows the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables, thus improving the quality of the execution plan generated for statements using bind variables.

However, there was a problem with this approach, when the column used in the WHERE clause with the bind contained a data skew. If there is data skew in the column, it is likely that a histogram has been created on this column during statistics gathering. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, it is not guaranteed that this plan will be good for all possible values for the bind variable. In other words, the plan is optimized for the peeked value of the bind variable, but not for all possible values.

The optimizer has been further enhanced in 11g to allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value.

So with above changes from 9i to 11g, Oracle comes with plan stability with different set of

tools. First lets look at small paragraphs for each of above.

SQL Stored Outlines:-  Allow the DBA to “freeze” SQL execution plans, and more important, change execution plans without touching the SQL source, a critical tool for tuning third-party vendor systems where you cannot touch the source code, works with hints

SQL Profiles:- Similar like Stored outline but persistent in data dictionary, A SQL profile helps generate a better execution plan than the normal optimization because it is tested against a real-world workload in the SQL Tuning Set (STS). Additional tasks like checking for advanced predicate selectivity, correlation between columns, join skews, and complex predicates such as functions, help in profiling the SQL statement. Once a SQL statement is profiled and stored, differing execution plans can be invoked at will. The sql profiles will be created by SQL Tuning advisor compared with workload or the executions and estimates and generate the sql profile ready for you automatically from AWR data.

SQL Performance Analyzer:- Introduced in 11g, SQL Performance Analyzer enables you to assess the performance impact of any system change resulting in changes to SQL execution plans and performance characteristics. Examples of common system changes for which you can use SQL Performance Analyzer include:

• Database upgrade
• Configuration changes to the operating system, hardware, or database
• Database initialization parameter changes
• Schema changes, for example, adding new indexes or materialized views
• Gathering optimizer statistics
• Validating SQL tuning actions, for example, creating SQL profiles or implementing partitioning

So it compares the STS (SQL Tuning Sets) and provides you the exhaustive report of sql performance with the above changes in place.

You can get full steps of SPA implementation here: Click Here

Adaptive Cursor Sharing:-  Plan stability features like the stored outlines, SQL Profiles are not the run time fixes , they are reactive and not adaptive and cannot change or swift to new plans according to the cost. Where in with advent of ACS the execution plan is marked as bind sensitive first time and second time with different workload executes it marked as bind aware and the third time it will completely change and swift to new plan by allowing a hard parse , there by eliminating the bind peeking issue runtime.

SQL Plan Management:- SQL Plan Management (SPM) allows database users to maintain stable yet optimal performance for a set of SQL statements. SPM incorporates the positive attributes of plan adaptability and plan stability,One of the 11g new features which is SQL Plan Management and SQL Plan baselines enables us to ‘lock in’ proven execution plans and ensure that the optimizer only chooses different execution plans to those which have been base lined if they are found to offer better performance as compared to existing plans.

So then, how does ACS and SPM will work along with SQL Profiles.

SPM and SQL profiles
A SQL statement can have both a SQL profile and a SQL plan baseline. Such a case was described in Part 3 where we evolved a SQL plan baseline by accepting a SQL profile. In this case, the SPM aware optimizer will use both the SQL profile and the SQL plan baseline. The SQL profile contains additional information that helps the optimizer to accurately cost each accepted plan and select the best one. The SPM aware optimizer may choose a different accepted plan when a SQL profile is present than when it is not.

SPM and stored outlines
It is possible for a SQL statement to have a stored outline as well as a SQL plan baseline. If a stored outline exists for a SQL statement and is enabled for use, then the optimizer will use it, ignoring the SQL plan baseline. In other words, the stored outline trumps a SQL plan baseline. If you are using stored outlines, you can test SPM by creating SQL plan baselines and disabling the stored outlines. If you are satisfied with SPM, you can either drop the stored outlines or leave them disabled. If SPM doesn't work for you (and we would love to know why), you can re-enable the stored outlines.
If you are using stored outlines, be aware of their limitations:

• You can only have one stored outline at a time for a given SQL statement. This may be fine in some cases, but a single plan is not necessarily the best when the statement is executed under varying conditions (e.g., bind values).
• The second limitation is related to the first. Stored outlines do not allow for evolution. That is, even if a better plan exists, the stored outline will continue to be used, potentially degrading your system's performance. To get the better plan, you have to manually drop the current stored outline and generate a new one.
• If an access path (e.g., an index) used in a stored outline is dropped or otherwise becomes unusable, the partial stored outline will continue to be used with the potential of a much worse plan.

SPM and adaptive cursor sharing
Adaptive cursor sharing

(ACS) may generate multiple cursors for a given bind sensitive SQL statement if it is determined that a single plan is not optimal under all conditions. Each cursor is generated by forcing a hard parse of the statement. The optimizer will normally select the plan with the best cost upon each hard parse.

When you have a SQL plan baseline for a statement, the SPM aware optimizer will select the best accepted plan as the optimal plan. This also applies for the hard parse of a bind sensitive statement. There may be multiple accepted plans, each of which is optimal for different bind sets. With SPM and ACS enabled, the SPM aware optimizer will select the best plan for the current bind set.

Thus, if a hard parse occurs, the normal SPM plan selection algorithm is used regardless of whether a statement is bind sensitive.

I referred here to list out all things from what I have read from below for my future reference and also for your references.

References:-

http://optimizermagic.blogspot.in/search/label/sql%20plan%20management

http://gavinsoorma.com/2010/01/11g-optimizer-plan-stability/

http://optimizermagic.blogspot.in/2007/12/why-are-there-more-cursors-in-11g-for.html

http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm

-Thanks

Geek DBA