原文地址：https://blogs.oracle.com/optimizer/post/what-is-the-difference-between-sql-profiles-and-sql-plan-baselines
原文作者：Maria Colgan
我经常被问及SQL Profiles和SQL Plan Baselines的区别，以及为什么SQL Profiles可以共享，而SQL Plan Baselines却不可以。所以，我认为写一篇解释他们之间的差异以及他们是如何互动的，应该是一个不错的想法。但首先，让我们简要回顾一下他们各自的特性。
查询优化器通常会使用诸如对象和系统统计信息，编译环境，绑定变量等来决定一条SQL语句的最佳执行计划。在某些情况下，输入信息或优化器的缺陷会导致一个欠优的执行计划。SQL Profiles则包含有缓解此类问题的辅助信息。当将它与正常输入一起使用时，SQL Profile帮助优化器最小化差错，并因此更有可能选择一个最佳的执行计划。
SQL语句的SQL Plan baseline则由一组已接受的执行计划构成。当语句被解析时，优化器将仅从这一组中选择最好的。如果通过正常的基于成本的选择流程，找到一个不同的执行计划，优化器将其添加到执行计划历史，但这个计划并不会被使用，直到它被验证比现存被接受的执行计划执行得更好，即所谓的演进。我们在之前的博文中详细描述过这个行为。
所以，SQL Profiles是通过为优化器提供附加信息，帮助它选择最优的执行计划；它们不会限定优化器选择特定的执行计划，这也是它们可以被共享的原因。与此相反，SQL plan baselines限定优化器仅可以从已接受的执行计划中选择。基于成本的方法依然被用于选择执行计划，但仅可从这一组执行计划中。相比于SQL Profiles,SQL plan baselines是更保守的执行计划选择策略.那么，什么时候我们该使用SQL profiles，而什么时候又该用SQL plan baselines呢？
如果你仅仅是希望在优化器估算成本的过程中，给优化器一个小小的帮助，并且并不限定使用特定的执行计划，那么应该使用SQL profiles。这个方法尤其适用于你希望系统可以立即适配诸如对象统计信息变化的情况。如果你更保守并且希望控制哪个执行计划被使用，那么你应该使用SQL plan baselines。如果你正在使用SQL plan baselines，并且发现优化器并不能从已接受的执行计划中，选择最佳的执行计划，或者不能发现成本最优的执行计划，并将其添加到执行计划历史中，那么你也可以始终使用SQL profile。如果SQL语句上既有SQL Profile，又有SQL plan Baseline，会发生什么呢？
如果你还记得，SQL Plan Management (SPM)有三个组件：执行计划捕获、执行计划选择和执行计划演进。而SQL profile的存在会影响SPM的全部三个组件，我们将在下面，描述这其中的每一个影响：
执行计划捕获与SQL profiles
当语句被执行时，会发生硬解析并产生一个基于成本的执行计划。该执行计划会受SQL profiles的影响。一旦基于成本的执行计划被确定，将会与存在于SQL plan baseline中的执行计划做比较。如果该执行计划匹配SQL plan baseline中某一个已接受的执行计划，我们会继续使用它。然而，如果该执行计划没能与SQL plan baseline中任何一个已接受的执行计划区配上，则会将其作为未接受的执行计划添加到执行计划基线中。
执行计划选择与SQL profiles
当一个带有SQL plan baselines的SQL语句被解析，则具有最优成本的，已被接受的执行计划将被选中。这一过程使用常规的优化器。SQL profile的存在将影响每一个执行计划所评估的成本，因而潜在影响了最终选择的执行计划。
执行计划演进与SQL profiles
SPM的第三个组件用于验证或演进未接受的执行计划。演进过程测试执行未接受的执行计划，并与最优的已接受执行计划比较。而最优的已接受执行计划依据成本来选择。同样的，如果SQL profile存在，其将会影响选择与未接受执行计划做比较的，已接受执行计划的评估成本。
希望这些信息能使你清楚的了解SQL Profiles和SQL Plan Baselines的区别以及他们是如何互动的。更多有关使用SQL plan baselines来控制SQL执行计划的内容请点此。
原文附后：
What is the difference between SQL Profiles and SQL Plan Baselines?
May 9, 2020 | 3 minute read
Maria Colgan
Distinguished Product Manager
I’m frequently asked about the difference between SQL profiles and SQL plan baselines and why SQL profiles can be shared but SQL plan baselines can’t. So I thought it would be a good idea to write a post explaining the differences between them and how they interact. But first let’s briefly recap each feature.
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. What happens if a SQL statement has both a SQL Profile and a SQL plan Baseline?
If you recall, SQL Plan Management (SPM) has three component, plan capture, plan selection, and plan evolution. The presence of a SQL profile affects all three components of SPM and we will describe each of those interactions below.
SPM plan capture and SQL profiles
When the statement is executed it will be hard parsed and a cost based plan will be generated. That plan will be influenced by the SQL Profile. Once the cost based plan is determined it will be compared to the plans that exist in the SQL plan baseline. If the plan matches one of the accepted plans in the SQL plan baseline, we will go ahead and use it. However, if the cost based plan doesn’t match any of the accepted plans in the SQL plan baseline it will be added to the plan baseline as an unaccepted plan.
SPM plan selection and SQL profiles
When a SQL statement with a SQL plan baseline is parsed, the accepted plan with the best cost will be chosen. This process uses the regular optimizer. The presence of a SQL profile will affect the estimated cost of each of these plans and thus potentially the plan that is finally selected.
SPM plan evolution and SQL profiles
The third sub-component of SPM is verification or evolution of non-accepted plans. The evolution process test-executes the non-accepted plan against the best of the accepted plans. The best accepted plan is selected based on cost. Again, if a SQL profile exists, it will influence the estimated cost and thus the accepted plan chosen for comparison against the non-accepted plan.
Hopefully this information gives you a clear picture of how SQL profile and SQL plan baselines differ and how they interact with one another! There’s more on using SQL plan baselines to control SQl execution plans here.
