原文地址：https://blogs.oracle.com/optimizer/post/what-is-the-difference-between-sql-profiles-and-sql-plan-baselines
原文作者：Maria Colgan

我經(jīng)常被問(wèn)及SQL Profiles和SQL Plan Baselines的區(qū)別，以及為什么SQL Profiles可以共享，而SQL Plan Baselines卻不可以。所以，我認(rèn)為寫(xiě)一篇解釋他們之間的差異以及他們是如何互動(dòng)的，應(yīng)該是一個(gè)不錯(cuò)的想法。但首先，讓我們簡(jiǎn)要回顧一下他們各自的特性。

查詢優(yōu)化器通常會(huì)使用諸如對(duì)象和系統(tǒng)統(tǒng)計(jì)信息，編譯環(huán)境，綁定變量等來(lái)決定一條SQL語(yǔ)句的最佳執(zhí)行計(jì)劃。在某些情況下，輸入信息或優(yōu)化器的缺陷會(huì)導(dǎo)致一個(gè)欠優(yōu)的執(zhí)行計(jì)劃。SQL Profiles則包含有緩解此類問(wèn)題的輔助信息。當(dāng)將它與正常輸入一起使用時(shí)，SQL Profile幫助優(yōu)化器最小化差錯(cuò)，并因此更有可能選擇一個(gè)最佳的執(zhí)行計(jì)劃。

SQL語(yǔ)句的SQL Plan baseline則由一組已接受的執(zhí)行計(jì)劃構(gòu)成。當(dāng)語(yǔ)句被解析時(shí)，優(yōu)化器將僅從這一組中選擇最好的。如果通過(guò)正常的基于成本的選擇流程，找到一個(gè)不同的執(zhí)行計(jì)劃，優(yōu)化器將其添加到執(zhí)行計(jì)劃歷史，但這個(gè)計(jì)劃并不會(huì)被使用，直到它被驗(yàn)證比現(xiàn)存被接受的執(zhí)行計(jì)劃執(zhí)行得更好，即所謂的演進(jìn)。我們?cè)谥暗?a href="http://www.sunline.cc/db/31780" target="_blank">博文中詳細(xì)描述過(guò)這個(gè)行為。

所以，SQL Profiles是通過(guò)為優(yōu)化器提供附加信息，幫助它選擇最優(yōu)的執(zhí)行計(jì)劃；它們不會(huì)限定優(yōu)化器選擇特定的執(zhí)行計(jì)劃，這也是它們可以被共享的原因。與此相反，SQL plan baselines限定優(yōu)化器僅可以從已接受的執(zhí)行計(jì)劃中選擇。基于成本的方法依然被用于選擇執(zhí)行計(jì)劃，但僅可從這一組執(zhí)行計(jì)劃中。相比于SQL Profiles,SQL plan baselines是更保守的執(zhí)行計(jì)劃選擇策略.那么，什么時(shí)候我們?cè)撌褂肧QL profiles，而什么時(shí)候又該用SQL plan baselines呢？

如果你僅僅是希望在優(yōu)化器估算成本的過(guò)程中，給優(yōu)化器一個(gè)小小的幫助，并且并不限定使用特定的執(zhí)行計(jì)劃，那么應(yīng)該使用SQL profiles。這個(gè)方法尤其適用于你希望系統(tǒng)可以立即適配諸如對(duì)象統(tǒng)計(jì)信息變化的情況。如果你更保守并且希望控制哪個(gè)執(zhí)行計(jì)劃被使用，那么你應(yīng)該使用SQL plan baselines。如果你正在使用SQL plan baselines，并且發(fā)現(xiàn)優(yōu)化器并不能從已接受的執(zhí)行計(jì)劃中，選擇最佳的執(zhí)行計(jì)劃，或者不能發(fā)現(xiàn)成本最優(yōu)的執(zhí)行計(jì)劃，并將其添加到執(zhí)行計(jì)劃歷史中，那么你也可以始終使用SQL profile。如果SQL語(yǔ)句上既有SQL Profile，又有SQL plan Baseline，會(huì)發(fā)生什么呢？

如果你還記得，SQL Plan Management (SPM)有三個(gè)組件：執(zhí)行計(jì)劃捕獲、執(zhí)行計(jì)劃選擇和執(zhí)行計(jì)劃演進(jìn)。而SQL profile的存在會(huì)影響SPM的全部三個(gè)組件，我們將在下面，描述這其中的每一個(gè)影響：

執(zhí)行計(jì)劃捕獲與SQL profiles

當(dāng)語(yǔ)句被執(zhí)行時(shí)，會(huì)發(fā)生硬解析并產(chǎn)生一個(gè)基于成本的執(zhí)行計(jì)劃。該執(zhí)行計(jì)劃會(huì)受SQL profiles的影響。一旦基于成本的執(zhí)行計(jì)劃被確定，將會(huì)與存在于SQL plan baseline中的執(zhí)行計(jì)劃做比較。如果該執(zhí)行計(jì)劃匹配SQL plan baseline中某一個(gè)已接受的執(zhí)行計(jì)劃，我們會(huì)繼續(xù)使用它。然而，如果該執(zhí)行計(jì)劃沒(méi)能與SQL plan baseline中任何一個(gè)已接受的執(zhí)行計(jì)劃區(qū)配上，則會(huì)將其作為未接受的執(zhí)行計(jì)劃添加到執(zhí)行計(jì)劃基線中。

執(zhí)行計(jì)劃選擇與SQL profiles

當(dāng)一個(gè)帶有SQL plan baselines的SQL語(yǔ)句被解析，則具有最優(yōu)成本的，已被接受的執(zhí)行計(jì)劃將被選中。這一過(guò)程使用常規(guī)的優(yōu)化器。SQL profile的存在將影響每一個(gè)執(zhí)行計(jì)劃所評(píng)估的成本，因而潛在影響了最終選擇的執(zhí)行計(jì)劃。

執(zhí)行計(jì)劃演進(jìn)與SQL profiles

SPM的第三個(gè)組件用于驗(yàn)證或演進(jìn)未接受的執(zhí)行計(jì)劃。演進(jìn)過(guò)程測(cè)試執(zhí)行未接受的執(zhí)行計(jì)劃，并與最優(yōu)的已接受執(zhí)行計(jì)劃比較。而最優(yōu)的已接受執(zhí)行計(jì)劃依據(jù)成本來(lái)選擇。同樣的，如果SQL profile存在，其將會(huì)影響選擇與未接受執(zhí)行計(jì)劃做比較的，已接受執(zhí)行計(jì)劃的評(píng)估成本。

希望這些信息能使你清楚的了解SQL Profiles和SQL Plan Baselines的區(qū)別以及他們是如何互動(dòng)的。更多有關(guān)使用SQL plan baselines來(lái)控制SQL執(zhí)行計(jì)劃的內(nèi)容請(qǐng)點(diǎn)此。

原文附后：

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.