使用SQL计划管理修复SQL性能问题

您发现工作中的关键工作量查询比平时花费的时间长得多，并且它开始影响您的服务水平。您怀疑它的执行计划不理想，需要立即对其进行优化，有没有一种快速简便的方法来做到这一点？

自动SQL计划管理可以在没有DBA干预的情况下处理此类问题，但是如果您无法使用此功能，该怎么办？幸运的是，您可以利用从Oracle Database 18c起的SQL计划管理（SPM）所做的增强。这里介绍的方法必须由DBA启动，但它仍然非常容易使用。

步骤如下：

1. 在SQL计划基线中捕获“问题” SQL语句计划。
2. 为此SQL计划基线运行SPM演化任务（使用一组特定的参数，下面将向您展示）。
3. 接受推荐的计划。

在很多情况下-就是这样！

SPM演进将找到并测试执行先前使用的SQL执行计划，并找出哪一个确实是最好的。它不仅仅依赖于优化器成本。可以将其配置为搜索自动工作负载存储库（AWR），Oracle数据库游标缓存和SQL调整集。换句话说，如果您在查询存储库或游标缓存中有更好的计划，SPM可以找到它并将其应用于问题查询。

广义上讲，该过程如下所示：

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');

   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调整集中捕获工作负载，则可以减轻这种风险。这样可以提高修复以前运行速度非常快并且未在AWR中显示的查询的可能性。请注意，如果您使用的是自治 数据库功能，则该数据库已经在自动系统调整集中捕获了您的工作负载。

上面描述的方法在Oracle Database 18c中可用，但是请注意，对于Oracle Database 19c，SPM内部在此区域得到了增强。因此，您可能会发现该技术在此发行版中效果最佳。Oracle Database 12c第2版包括上面使用的参数设置，但是由于错误号29539794，该任务（在编写本文时）有时会因ORA-01422而失败。

SQL计划管理应用一些接受条件，并且仅接受性能比回归计划好一些的计划。在GitHub的完整示例中，您将看到显示了SPM进化报告（主要脚本是spm.sql）。该报告清楚地表明是否通过了验收标准，否则，将不会接受在AWR中找到的计划。