使用SQL Plan Management修复SQL性能退化

赵勇 2024-12-21

110

当你发现了一条重要的查询花费比平时更长的时间，并且它开始影响服务水平时，你会希望这是一个平安无事的一天。你怀疑它使用了一个不佳的执行计划，需要马上将好的执行计划找出来。那是否有一种快速而简单的方法呢？

Automatic SQL plan management可以在没有DBA干预的情况下处理这类问题，但是，如果这个特性没有启用，你又可以做什么呢？幸运的是，你可以利用在Oracle Database 18c 及之后提供的SQL plan management (SPM)的增强（后面我还会提及Oracle Database 12c）。虽然这里提及的方法必须由DBA初始化，但其仍然是非常易用的。
操作步骤如下：

捕获该“问题”SQL的执行计划到SQL plan baseline中
对该SQL plan baseline运行SPM演进任务（使用我下面提供的特定参数集）
接受建议的执行计划

在绝大多数情况下，这就解决了

SPM演进任务会定位并测试执行此前使用过的执行计划，并从中找出哪一个才是真正最优的。这个过程并不依赖优化器的成本估算。它可以被配置成在automatic workload repository (AWR)，游标缓存和SQL调优集中来搜索。换句话说，如果在查询库或游标缓存中有一个更好的执行计划，SPM可以找到并应用它到问题查询上。

处理步骤类似如下：

Copy code snippet

BEGIN 
   --
   -- Create a SQL plan baseline for the problem query plan
   -- (in this case assuming that it is in the cursor cache)
   -- 
   n := dbms_spm.load_plans_from_cursor_cache(
                  sql_id => '<problem_SQL_ID>', 
                  plan_hash_value=> <problem_plan_hash_value>, 
                  enabled => 'no');
   --
   -- Set up evolve
   --
   tname := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle=>handle); 

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => tname,
      parameter => 'ALTERNATE_PLAN_BASELINE', 
      value     => 'EXISTING');

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => tname,
      parameter => 'ALTERNATE_PLAN_SOURCE', 
      value     => 'CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY+SQL_TUNING_SET');

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => tname,
      parameter => 'ALTERNATE_PLAN_LIMIT', 
      value     => 'UNLIMITED');
   --
   -- Evolve
   --
   ename := DBMS_SPM.EXECUTE_EVOLVE_TASK(tname);
   --
   -- Optionally, choose to implement immediately
   --
   n := DBMS_SPM.IMPLEMENT_EVOLVE_TASK(tname);
END; 
/
复制

有一类情况是SQL在之前执行得都非常好，从来没有被捕获并存储到AWR中（也许也不在游标缓存中），这种情况下，SPM可能无法找到此前好的执行计划。如果你周期性的捕获运行负载到SQL调优集中，或者启用 automatic SQL tuning set，则可以避免这一风险。
前述步骤中出现的“SQL_TUNING_SET”选项，将包含SQL调优集做为潜在的执行计划。这将改善你修复这类此前运行得非常快，但并未被记录到AWR中的SQL的可能性。

上面描述的方法在Oracle Database 18c中可用的，但是请注意这个部分在Oracle Database 19c中做了内部增强。因此，你可能发现这个技巧在这个版本中会工作得更好。Oracle Database 12c Release 2包含了上面使用的参数，但是运行时，有时会因bug 29539794而报ORA-01422的错误。

SQL plan management采用了一些接受标准，并只会接受那些比性能退化的执行计划优于一定幅度的执行计划。在Github中的完整工作示例中，你会看到展示的SPM演进报告（主要的脚本是spm.sql）。该报告清晰的显示接受标准是否满足，如果不满足，则AWR中发现的执行计划是不会被接受的。

SQL调优集和AWR的使用有许可要求，请参考Database Licence Information User Manual.

你可以帮助改进该脚本，欢迎评论。

原文链接：https://blogs.oracle.com/optimizer/post/repairing-sql-performance-regression-with-sql-plan-management

Repairing SQL Performance Regression with SQL Plan Management

October 15, 2019 | 3 minute read

Nigel Bayliss

Product Manager

You were hoping for an uneventful day at work when you notice that you have a critical workload query that is taking much longer to execute than usual, and you can see that it is beginning to affect your service levels. You suspect that it has a sub-optimal execution plan and you need to get it sorted out immediately. Is there a quick and easy way to do that?

Automatic SQL plan management can deal with this type of issue without DBA intervention, but what can you do if this feature is not available to you? Fortunately, you can take advantage of enhancements made in SQL plan management (SPM) from Oracle Database 18c onwards (I have a word to say about Oracle Database 12c Release 2 later on). The approach presented here must be initiated by a DBA, but it is nevertheless very easy to use.

The steps are as follows:

Capture the ‘problem’ SQL statement plan in a SQL plan baseline.
Run the SPM evolve task for this SQL plan baseline (using a particular set of parameters which I’ll show you below).
Accept the recommended plan.

In many cases - that’s it!

SPM evolution will locate and test execute previously-used SQL execution plans and figure out which ones are really the best. It does not rely on optimizer costings alone. It can be configured to search the automatic workload repository (AWR), the Oracle Database cursor cache and SQL tuning sets. In other words, if you have a better plan in a query repository or the cursor cache, SPM can find it and apply it to the problem query.

In broad terms, this is what the procedure looks like:

Copy code snippet

BEGIN 
   --
   -- Create a SQL plan baseline for the problem query plan
   -- (in this case assuming that it is in the cursor cache)
   -- 
   n := dbms_spm.load_plans_from_cursor_cache(
                  sql_id => '<problem_SQL_ID>', 
                  plan_hash_value=> <problem_plan_hash_value>, 
                  enabled => 'no');
   --
   -- Set up evolve
   --
   tname := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle=>handle); 

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => tname,
      parameter => 'ALTERNATE_PLAN_BASELINE', 
      value     => 'EXISTING');

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => tname,
      parameter => 'ALTERNATE_PLAN_SOURCE', 
      value     => 'CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY+SQL_TUNING_SET');

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => tname,
      parameter => 'ALTERNATE_PLAN_LIMIT', 
      value     => 'UNLIMITED');
   --
   -- Evolve
   --
   ename := DBMS_SPM.EXECUTE_EVOLVE_TASK(tname);
   --
   -- Optionally, choose to implement immediately
   --
   n := DBMS_SPM.IMPLEMENT_EVOLVE_TASK(tname);
END; 
/
复制

There will be cases where a SQL statement was previously performing very well and was never picked up and stored in AWR (and perhaps are no longer in the cursor cache). In this case, SPM might not be able to find the previous good plan. You can mitigate this risk if you periodically capture your workloads in SQL tuning sets or if you enable the automatic SQL tuning set. The ‘SQL_TUNING_SET’ option shown above will include SQL tuning sets as the source of potential execution plans. This will improve the likelihood that you can repair queries that previously ran very quickly and did not show up in AWR.

The method described above is available in Oracle Database 18c, but note that SPM internals were enhanced in this area for Oracle Database 19c. It is therefore possible that you will find that the technique will work best in this release. Oracle Database 12c Release 2 includes the parameter settings used above but the task (at the time of writing) will sometimes fail with ORA-01422 due to bug number 29539794.

SQL plan management applies some acceptance criteria and will only accept plans that perform better than the regressed plan by some margin. In the fully-worked example in GitHub you will see that the SPM evolve report is displayed (the primary script is spm.sql). The report clearly indicates whether or not the acceptance criteria is passed and if it is not, then the plan found in AWR will not be accepted.

The use of SQL tuning sets and AWR has licensing implications. Always check the Database Licence Information User Manual.

You can help to improve the scripts - comments welcome.

spm