Difference Between SQL Profiles and SQL Plan Baselines
- A SQL profile contains auxiliary information that helps mitigate issues arising from defects in inputs or the optimizer.
- SQL profiles provide additional information to the optimizer, aiding it in minimizing mistakes and selecting a better plan.
- They are used in conjunction with regular optimizer inputs to enhance plan selection.
- SQL profiles do not constrain the optimizer to specific plans; instead, they guide the optimizer’s costing process.
- They are a more flexible approach, allowing the optimizer to adapt quickly to changes such as new object statistics.
- SQL profiles can be shared among different queries, enhancing their versatility.
SQL Plan Baselines:
- A SQL plan baseline consists of a set of accepted plans for a specific SQL statement.
- The optimizer selects the best plan from this set when parsing the statement, limiting the selection to accepted plans.
- Plans not within the accepted set are put into the plan history but require verification through the evolution process.
- SQL plan baselines are a more conservative approach, restricting the optimizer’s choices to a predefined set of plans.
- They are useful for ensuring consistent performance by controlling which plans are used.
- Unlike SQL profiles, SQL plan baselines cannot be easily shared, as they are specific to a particular statement and set of accepted plans
Interactions and When to Use:
- SQL profiles provide additional information to the optimizer without constraining plan selection and can be used when immediate adaptation is desired.
- SQL plan baselines limit plan selection to a set of accepted plans, offering more control, but they are less adaptable.
- If using SQL plan baselines and facing suboptimal selections, a SQL profile can be added to help enhance the optimizer’s choices.
- When both a SQL Profile and a SQL plan baseline are present for a statement, the SQL Profile influences plan capture, plan selection, and plan evolution within SQL Plan Management (SPM).
In conclusion, SQL Profiles enhance the optimizer’s decision-making process by providing additional information without restricting plan selection, while SQL Plan Baselines offer controlled and consistent performance by confining the choice of plans. The presence of a SQL Profile alongside a SQL plan baseline can affect the various components of SQL Plan Management (SPM) to refine plan selection and evolution.