作者：Maria Colgan
我经常被问到关于SQL profile和SQL plan baseline的区别，以及为什么SQL Profile可以共享，但SQL plan baseline却不可以。我觉得写一篇关于他们之间的差异和相互影响的贴子是个不错的主意。首先，让我们简要回顾他们的特性。
查询优化器使用诸如对象和系统的统计信息、编译环境、绑定变量等等这类信息来决定最佳的执行计划。在某些情况下，这些输入信息或优化器的缺陷，会导致产生一个欠优的执行计划。SQL Profile包含了可以缓解这个问题的辅助信息。当这些辅助信息与常规输入的信息一起使用时，SQL profile就可以帮助优化器最小化错误，并因此更有可能选择到最优的执行计划。
SQL plan baseline是由一系列已接受的执行计划组成的。当语句被解析时，优化器只会从这些执行计划中选择最好的执行计划。如果使用正常的基于成本的选择过程，发现了一个不同的执行计划 ，优化器会添加该计划到执行计划历史中，但是这个计划并不会被使用，除非它已经被验证比现存的执行计划更好，并且被进化了。这个行为在我们之间的贴子有过描述。
因此，SQL profile提供额外的信息来帮助优化器选择最优的执行计划；它们不会限制优化器到任何特定的执行计划上，这也是它们为什么可以被共享的原因。另一方面，SQL plan baseline，限制优化器从已接受的执行计划中去选择。基于成本的方法仍然被用于选择执行计划，但是，只限于已接受的执行计划。SQL plan baseline是比SQL profile更保守的执行计划选择策略。那么，我们应该何时使用SQL profile和SQL plan baseline呢？
如果你只是想在优化器评估过程中给它一点帮助，而并不想限定选择特定的执行计划时，应该使用SQL profile.当您希望系统立即适配变化，比如新的对象统计信息，这个方法会特别有用。如果您更保守，并且要控制使用哪一个执行计划，应该使用SQL plan baseline.如果您正在使用SQL plan baseline，而且发现有时优化器并不能从已接受的执行计划列表中，选择到最优的执行计划，或者不能发现成本最佳的执行计划添加到执行计划历史中，那么你就总是使用SQL profile就好了。如果一条语句同时使用SQL profile和SQL plan baseline， 会发生什么呢？
如果您还记得，SQL计划管理（SPM）有三个组件，执行计划捕获、执行计划选择和执行计划演进。SQL profile的存在会影响SPM的所有三个组件，我们将在下面描述这些影响。
执行计划捕获和SQL profile
当语句被执行时，它将被硬解析并生成一个基于成本的执行计划。这个执行计划会受到SQL profile的影响。一旦基于成本的执行计划被确定了，它会与存在于SQL plan baseline中的执行计划做比较。如果执行计划与SQL plan baseline中某个已接受的执行计划匹配，我们会继续使用它。但是，如果执行计划与SQL plan baseline中已接受的执行计划都不匹配，它将被添加到SQL plan baseline中，做为一个尚未接受的执行计划。
执行计划选择和SQL profile
当一个使用了SQL plan baseline的SQL语句被解析时，已接受的执行计划中，拥有最优成本的会被选中。这个过程使用是常规的优化流程。SQL profile的存在会影响对每一个执行计划成本的评估，并潜在影响到了执行计划的最终选择。
执行计划演进和SQL profile
SPM的第三个组件是对尚未接受的执行计划进行验证或演进。演进过程会测试执行尚未接受的执行计划，并与已接受执行计划中最佳的做比较。已接受执行计划中的最佳也是基于成本选择的。同样，如果SQL profile存在，它将影响成本的评估，以及用于与尚未接受的执行计划比较的，已接受执行计划的选择
希望这些信息能让您清楚地了解SQL profile和SQL plan baselinee有何不同，以及它们是如何相互作用的！更多关于使用SQL计划基线来控制SQL执行计划的内容请点这里。
原文链接：https://blogs.oracle.com/optimizer/what-is-the-difference-between-sql-profiles-and-sql-plan-baselines
原文内容：
What is the difference between SQL Profiles and SQL Plan Baselines?
Maria Colgan
Master 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.
