作者：Nigel Bayliss
SQL Plan Management(SPM)被设计用于预防应用中使用的SQL（假设问题SQL会被使用超过1次）发生性能退化。SPM使用与单个SQL相关联的SQL plan baselines来控制哪些执行计划是允许使用的。这是一个简单但功能强大的想法，它为以更多选择性和应对方法来使用SQL plan baselines的可能性，打开了一扇大门：在不修改应用查询或者不改变应用本身的情况下，影响单个查询的执行计划。在博客集（点此链接）和SPM白页（点此链接）中涵盖了相关技术介绍，但是，用一个完整示例来展示它，还是值得的 。
如果您不想阅读下面的所有背景并跳转到一个真实的例子，我已经在GitHub中添加了一些新的脚本（点此链接）。它们类似于Oracle支持部门几年前发布的SQL概要文件示例，它们演示了如何从SQL调优集和AWR中检索计划。它们将在Oracle Database 12c Release 2以后的版本（甚至在Oracle Database 18c 标准版上）中工作。下面的另一个代码示例则可以在Oracle Database 11g以后的版本中工作。
考虑这样一个场景：一个应用程序使用了一个SQL语句，该应用程序有一个次优计划，您需要对此做些什么。为了便于讨论，我们假设您知道有一个提示可以用来实现一个更好的计划。从现在开始，我假设您希望应用一个提示，但是应用程序代码不能以任何方式更改。
请看下面的SQL执行计划。这是一个使用索引筛选销售记录的应用程序查询：
SQL> SELECT *
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'f23qunrkxdgdt'));
PLAN_TABLE_OUTPUT
-----------------
SQL_ID f23qunrkxdgdt, child number 2
-------------------------------------
select sum(num) from sales where id < :idv
Plan hash value: 2327341677
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES |
|* 3 | INDEX RANGE SCAN | SALESI |
-------------------------------------------------------
如果这个计划不是最优的呢？在这个案例中，它是最佳的，但是为了示例起见，我将假定我希望Oracle优化器选择一个完整的表扫描。我们需要的是FULL的提示：
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 82x4tj3z2vg23, child number 0
-------------------------------------
select /*+ FULL(sales) */ sum(num) from sales where id < :idv
Plan hash value: 1047182207
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS FULL| SALES |
------------------------------------
加了提示的测试查询为我们提供了一个我们期望使用的，全表扫描的执行计划的示例。现在我们可以使用SPM将我们的首选计划与应用程序查询相关联。 步骤如下：
步骤0确认我们有一个SQL语句，其中包含一个要更改的计划。其余步骤包括：
-
为应用程序查询创建初始禁用的SQL计划基线。我之所以使用术语“一个或多个”，是因为一个查询可能有多个SQL执行计划。我们将为SQL语句使用的每个计划创建一个SQL计划基线，但实际上只需要一个。
-
执行（或解析）有提示的测试查询以生成首选计划。
-
将在步骤2中创建的首选计划加载到SQL计划基线中（这次启用了enabled=‘YES’）。
有提示的语句的文本显然和应用的语句的文本是不同的，但这正好：我们可以简单地使用执行计划而不是SQL文本。只要执行计划可以重新产生并且是有效，我们的应用查询就会使用。我这么说是什么意思？请看下面的示例：
假设一个对CUSTOMERS的查询发生了全表扫描：
select sum(num) from CUSTOMERS;
如果我们使用这个查询的计划，尝试去影响我们对SALES的查询，这是不能正常工作的。这就像下面这样，来要求SQL plan baseline去影响对SALES的执行计划：
select /*+ FULL(customers) */ sum(num) from SALES where id < :idv
表象背后，是SQL plan baselines使用一套完整的提示来控制执行计划。因此，我们对SALES的查询，FULL（customers）不是一个有效的提示，并且不会产生期望的结果！如果您有时间，可以尝试将对CUSTOMERS查询的计划加载到与对SALES查询关联的SQL计划基线中。不会有错误消息，但您也无法产生您想要的计划（除非只是运气使然）。
工作示例
我已经上传了一个示例过程（点此链接）和一个完整的示例到GitHub（点此链接），这样您就可以看到上面的步骤是如何实现的。根据下面的评论，我也添加了这个过程。它以禁用状态加载所有现有的执行计划，并添加一个新的启用的执行计划到SQL plan baseline（而不是替换现有的）中。你应该调整程序以适合你的具体要求。例如，您可能不希望删除预先存在的SQL计划基线。
我将使用上面显示的sqlid和plan hash值。演示如何使用我的示例过程set_my_plan和add_my_plan（请参阅GitHub中的proc.sql和proc2.sql）：
Set my plan procedure
执行Procedure
注：“SPB"表示Sql Plan Baseline
SQL> set serveroutput on
SQL> set linesize 200
SQL> exec set_my_plan('f23qunrkxdgdt','82x4tj3z2vg23',1047182207)
No existing SQL plan baselines to drop
Created 1 disabled SPBs for SQLID f23qunrkxdgdt
SPB Detail: SQL_PLAN_3yr9p97b3j5gbfaa7aab3 handle SQL_3f5d3549d63895eb
Associating plan SQLID/PHV 82x4tj3z2vg23/1047182207 with SPB SQL Handle SQL_3f5d3549d63895eb
Enabled SPB - Name: SQL_PLAN_3yr9p97b3j5gb35032dee SQL handle: SQL_3f5d3549d63895eb
SQL> set serveroutput off
下面是procedure执行后，对应用查询所做的EXPLAIN PLAN的输出。未加HINT的SQL语句现在使用了全表扫描，并且，你可以从NOTE部分，看到SQL plan baseline已被使用的注释.
SQL> SELECT *
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'LAST'));
PLAN_TABLE_OUTPUT
-----------------
SQL_ID f23qunrkxdgdt, child number 0
-------------------------------------
select sum(num) from sales where id < :idv
Plan hash value: 1047182207
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS FULL| SALES |
------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<:IDV)
Note
-----
- SQL plan baseline SQL_PLAN_3yr9p97b3j5gb35032dee used for this statement
注意事项
SPM使用签名来匹配SQL语句，而不是SQL_ID。签名产生自规范化后的SQL文本。因此，如果有多个SQL ID具有相同的签名，那么它们将共享相同的SQL计划基线。例如，以下查询具有相同的签名：
select sum(num) from sales where id < :idv
SELECT SUM(num) FROM sales WHERE id < :idv
select sum(num) from sales where id < :idv
上面的演示procedure会删除与应用SQL语句拥有相同签名的，已存在的SQL plan baseline.而新的脚本（点此链接）在发现有存在的SQL plan baseline时会报错，除非你使用了FORCE参数。
一如既往，欢迎评论和更正。只需在底部发表评论
译者注： 若希望更进一步了解SPM，请点此链接https://www.modb.pro/db/29991
原文链接：https://blogs.oracle.com/optimizer/using-sql-plan-management-to-control-sql-execution-plans
原文内容：
Using SQL Plan Management to Control SQL Execution Plans
Nigel Bayliss
Product Manager
SQL plan management (SPM) is designed to prevent performance regression for all SQL statements used by an application (assuming that the SQL statements in question are used more than once). SPM uses SQL plan baselines that are associated with individual SQL statements to control what execution plans they are permitted to use. It’s a simple but powerful idea that opens the door to the possibility of using SQL plan baselines in a more selective and reactive way: to influence the SQL execution plans of individual queries without having to modify application queries or change the application itself. The technique is covered in blogs and in the SPM white paper here, but it deserves a post of its own along with a full example.
If you want to avoid reading all the background below and jump to a real example, I’ve added some new scripts to GitHub. They are similar to SQL profile examples published by Oracle Support some years ago and they demonstrate how you can retrieve plans from SQL tuning sets and AWR. They will work in Oracle Database 12c Release 2 onwards (and even on Oracle Database 18c Standard Edition). The other code example below will work in Oracle Database 11g onwards.
Consider the scenario where you have a SQL statement used by an application that’s got a sub-optimal plan and you need to do something about it. For the sake of argument, let’s assume that you know that there’s a hint you can use to achieve a better plan. I’m going to assume from now on that you want to apply a hint but the application code cannot be changed in any way.
Take a look at the following SQL execution plan. It’s an application query that filters SALES rows using an index:
SQL> SELECT *
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'f23qunrkxdgdt'));
PLAN_TABLE_OUTPUT
-----------------
SQL_ID f23qunrkxdgdt, child number 2
-------------------------------------
select sum(num) from sales where id < :idv
Plan hash value: 2327341677
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES |
|* 3 | INDEX RANGE SCAN | SALESI |
-------------------------------------------------------
What if this plan isn’t optimal? It is optimal in this case, but for the sake of example I’m going to assume that I want the Oracle Optimizer to pick a full table scan instead. All we need is the FULL hint:
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 82x4tj3z2vg23, child number 0
-------------------------------------
select /*+ FULL(sales) */ sum(num) from sales where id < :idv
Plan hash value: 1047182207
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS FULL| SALES |
------------------------------------
The hinted test query gives us an example of the TABLE ACCESS FULL plan we want to use. At this point we are in a position to use SPM to associate our preferred plan with the application query. Here are the steps:
Controlling plans with SQL plan management
Step zero acknowledges that we have a SQL statement with a plan we want to change. The remaining steps are:
Create an initial disabled SQL plan baselines for the application query. I’m using the term “one or more” because a query might have more than one SQL execution plan. We will create a SQL plan baseline for each plan used by the SQL statement, but only one is actually needed.
Execute (or parse) a hinted test query to generate the preferred plan.
Load the preferred plan into a SQL plan baseline created in step two (this time with enabled=’YES’).
The hinted statement’s text is of course different to the application statement’s text, but that’s just fine: we’re simply using the plan and not the SQL text. Our application query will use the plan as long as it can reproduce it and it’s valid. What do I mean by that? Here’s an example:
Imagine a CUSTOMERS query that happens to perform a FULL scan:
select sum(num) from CUSTOMERS;
If we use the plan for this query in an attempt to influence our SALES query, it’s not going to work. We would be asking the SQL plan baseline to influence the SALES plan like this:
select /*+ FULL(customers) */ sum(num) from SALES where id < :idv
Under the covers, SQL plan baselines use a complete set of hints to control execution plans. So, for our SALES query, FULL(customers) is not a valid hint and is not going to yield the desired result! If you’ve got some time on your hands, you can try loading a plan for a CUSTOMERS query into a SQL plan baseline associated with a SALES query. There won’t be an error message, but you won’t be able to reproduce the plan you want either (unless it’s just by luck).
Worked Example
I’ve have uploaded an example procedure and a fully worked example to GitHub so you can see how the steps above can be implemented. Based on a comment below, I added this procedure too. It loads all existing plans in a disabled state and adds a new enabled SQL plan baseline (rather than replacing an existing one). You should adapt the procedures to meet your specific requirements. For example, you might not want to drop pre-existing SQL plan baselines.
I’ll be using the SQL IDs and plan hash value that I highlighted in bold, above. Here’s how to use my example procedures set_my_plan and add_my_plan (see proc.sql and proc2.sql in GitHub):
Set my plan procedure
Executing the Procedures
Note that “SPB” stands for SQL plan baseline:
SQL> set serveroutput on
SQL> set linesize 200
SQL> exec set_my_plan('f23qunrkxdgdt','82x4tj3z2vg23',1047182207)
No existing SQL plan baselines to drop
Created 1 disabled SPBs for SQLID f23qunrkxdgdt
SPB Detail: SQL_PLAN_3yr9p97b3j5gbfaa7aab3 handle SQL_3f5d3549d63895eb
Associating plan SQLID/PHV 82x4tj3z2vg23/1047182207 with SPB SQL Handle SQL_3f5d3549d63895eb
Enabled SPB - Name: SQL_PLAN_3yr9p97b3j5gb35032dee SQL handle: SQL_3f5d3549d63895eb
SQL> set serveroutput off
Here’s the explain plan for the application query after the procedure was executed. The non-hinted SQL statement now uses the FULL scan and you can see from the Note section that the SQL plan baseline is being used.
SQL> SELECT *
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'LAST'));
PLAN_TABLE_OUTPUT
-----------------
SQL_ID f23qunrkxdgdt, child number 0
-------------------------------------
select sum(num) from sales where id < :idv
Plan hash value: 1047182207
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS FULL| SALES |
------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<:IDV)
Note
-----
- SQL plan baseline SQL_PLAN_3yr9p97b3j5gb35032dee used for this statement
Usage Notes
SPM matches a SQL statement using a signature, not a SQL ID. The signature is generated from the normalized SQL text. For this reason, if there are multiple SQL IDs that have the same signature then they will all share the same SQL plan baseline. For example, the following queries have the same signature:
select sum(num) from sales where id < :idv
SELECT SUM(num) FROM sales WHERE id < :idv
select sum(num) from sales where id < :idv
The example procedures (above) will drop any pre-existing SQL plan baselines for SQL statements that have the same signature as the application SQL statement. The newer scripts will generate an error if there are existing SQL plan baselines unless you use the FORCE parameter.
As always, comments and corrections are welcome. Just post a comment at the bottom.
