SQL Profiles and SQL Plan Baselines

The query optimizer normally uses information like object and system statistics, compilation environment, bind values and so on to determine the best plan for a SQL statement. In some cases, defects in either these inputs or the optimizer can lead to a sub-optimal plan. The SQL Tuning Advisor can be used to find a better plan for a SQL statement. If a better plan is found, the advisor will suggest you accept a SQL profile. This contains corrective information to help the optimizer find a better plan next time.

A SQL plan baseline for a SQL statement consists of a set of accepted plans. When the statement is parsed, the optimizer selects only the best plan from among this set. If a different plan is found using the normal cost-based selection process, the optimizer will add it to the plan history, but this plan will not be used until it is verified to perform better than the existing accepted plan and is evolved. We described this behavior in more detail in a series of posts.

SQL profiles provide additional information to the optimizer to help select the best plan; they don’t constrain the optimizer to any specific plan, which is why they can be shared. On the other hand, SQL plan baselines constrain the optimizer to only select from a set of accepted plans. The cost-based approach is still used to choose a plan, but only within this set of plans.

When should you use SQL profiles versus SQL plan baselines?

Use SQL profiles suggested by the SQL Tuning Advisor to find a better plan and help the optimzer use a better plan in future. Nevertheless, it woni’t fix a particular execution plan, so it may change over time if a SQL plan baseline isn’t used to control it. Subsequent plan changes may have a negative impact on performance.

Use a SQL plan baseline if you want to the optimizer to use a particular SQL execution plan or a set of ‘approved,’ accepted SQL execution plans. The SQL plan baseline will allow a SQL statement plan to evolve over time in a managed and controlled way.

Can you use SQL profile and SQL plan baseline together?

Yes. For example, you can use the SQL Tuning Advisor to create a SQL profile, and if you want to manage the plan in the long term, you can create a SQL plan baseline for the same SQL statement. Once you have created the SQL plan baseline, you can disable the SQL profile if you wish.

What about fixing a SQL execution plan or copying one from one SQL statement to another?

Some of you may use the coe_xfr_sql_profile.sql script from My Oracle Support: Encouraging CBO to Pickup a Better Execution Plan Using the COE XFR SQL Profile Script (Doc ID 1955195.1). This is an ’emergency’ technique and I would encourage you to create a SQL plan baseline on the SQL statement once the script has been used. In this way, you can manage the plan properly over time. It is a misconception that the script will enforce a plan more successfully than a SQL plan baseline. This is not true: a SQL profile used in this way (i.e. outside SQL Tuning Advisor) controls the plan with a full set of outline hints, which is what a SQL plan baseline does. This difference is that if the outline hints in the SQL profile fail to enforce the plan you want, the SQL profile will not tell you that the plan you thought you had isn’t the plan you have! In addition, a SQL profile created with the script prevents the optimizer from finding better plans if there are (for example) data volume or database schema changes. SQL plan baselines report if a plan is not reproducing properly, and will properly manage plan changes in the long term using SQL plan management evolution.

A SQL profile used with the SQL profile script has the advantage that it can be used with force matching, whereas you cannot do that with a SQL plan baseline. A SQL profile will match a SQL statement even if it uses literal values instead of bind variables.