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

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.

SQLプロファイルはオプティマイザのための補助情報。オプティマイザのミスを抑えて、最適な実行計画を選択しやすくなる。

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.

一方、SQL計画ベースラインは選択された実行計画で構成される。異なる実行計画が見つかっても、acceptedされるまで利用されない。

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?

SQLプロファイルに比べて、SQL計画ベースラインはより慎重なアプローチといえる。

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.

特定の実行計画に拘束されることなく、オプティマイザが最適な実行計画を選ばせたいならSQLプロファイルを利用するとよい。このアプローチは、例えば、オブジェクト変更後、速やかに最適な実行計画を探したい場合に有効。