[Oracle Database] SQL計画ベースラインとSQLプロファイルの違い


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. A SQL profile contains auxiliary information that mitigates these problems. When used together with its regular inputs, a SQL profile helps the optimizer minimize mistakes and thus more likely to select the best plan.



A SQL plan baseline for a SQL statement consists of a set of accepted plans. When the statement is parsed, the optimizer will only select 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 previous post.



So, 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. SQL plan baselines, on the other hand, 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. SQL plan baselines are a more conservative plan selection strategy than SQL profiles. So when should you use SQL profiles versus SQL plan baselines?



You should use a SQL profiles if you just want to help the optimizer a little in its costing process without constraining it to any specific plan. This approach can be extremely useful when you want the system to adapt immediately to changes like new object statistics. You should use SQL plan baselines if you are more conservative and want to control which plans are used. If you are using SQL plan baselines and find that the optimizer sometimes does not select the best plan from the accepted list or does not find a best-cost plan to add to the plan history, then you can always use a SQL profile as well.





Be the first to comment

Leave a Reply

Your email address will not be published.