Stored outlines vs SQL plan baseline vs SQL PROFILE

Oracle SQL Tuning Features: Stored Outlines, SQL Plan Baselines, and SQL Profiles

Oracle Database provides several powerful features to influence and stabilize SQL execution plans, helping to optimize query performance. This document explains three key features: Stored Outlines, SQL Plan Baselines, and SQL Profiles.

Stored Outlines

Stored Outlines were an earlier mechanism to "freeze" the execution plan for a specific SQL statement.

• Purpose: To ensure that a particular SQL statement always uses the same execution plan, regardless of changes in statistics, database parameters, or optimizer versions.

• Limitation: While they freeze a plan, they also prevent the optimizer from generating potentially more beneficial execution plans in the future. This can be a disadvantage if database conditions or data distribution change significantly, and a new, better plan could be generated.

SQL Plan Baseline

SQL Plan Management (SPM), introduced in Oracle Database 11g, provides a more robust and flexible way to manage and evolve SQL execution plans through SQL Plan Baselines.

• Concept: When SPM is enabled, the optimizer stores generated execution plans in a special repository called the SQL Management Base (SMB). All stored plans for a specific SQL statement form its plan history.

• Accepted Plans: Some of the plans in the history can be explicitly marked as "accepted." When the SQL statement is re-parsed, the optimizer considers only these accepted plans from the history. This set of accepted plans for that SQL statement is called a SQL plan baseline.

• Flexibility: With SQL plan management, you can:

  • Examine all available plans in the plan history for a SQL statement.

  • Compare them to see their relative efficiency.

  • Promote a specific plan to "accepted" status.

  • Even make a plan the permanent ("fixed") one, similar to a stored outline but within the SPM framework.

• Creation Methods: You can create a SQL plan baseline in several ways:

  • Using a SQL Tuning Set (STS).

  • From the cursor cache.

  • Exporting from one database and importing into another.

  • Automatically for every statement (if configured).

• Evolution: A SQL plan baseline can be "evolved" (meaning new, better plans can be tested and added to the baseline) either by executing the evolve_sql_plan_baseline function (part of DBMS_SPM package) or by using the SQL Tuning Advisor.

• Querying Baselines: You can view accepted SQL plan baselines using:

  SELECT sql_text, plan_name, enabled, accepted FROM dba_sql_plan_baselines;
  
  

SQL Profile

A SQL Profile is a set of auxiliary information that the query optimizer uses to enhance its ability to select the best execution plan for a SQL statement.

• Purpose: The optimizer normally uses inputs like object and system statistics, compilation environment, and bind values to determine the optimal plan. However, in some cases, defects in these inputs or in the optimizer itself can lead to a sub-optimal plan. A SQL profile contains additional, corrective information that mitigates these problems.

• Functionality: When used together with its regular inputs, a SQL profile helps the optimizer minimize mistakes and thus is more likely to select the best plan. It doesn't "freeze" a plan like an outline or baseline, but rather provides hints and corrections to guide the optimizer's decision-making process.

• Creation: SQL profiles are typically created by the SQL Tuning Advisor when it identifies a sub-optimal plan for a SQL statement and finds better execution paths.