执行计划演进

引言

  在数据库系统中，对于用户SQL请求，优化器会选择最优的执行计划，同样的SQL在不同环境，优化器在选择最优计划时可能会发生变化，发生变化的原因有系统升级、统计信息重新收集、优化器相关系统参数变更等。在实际生产系统中，执行计划的变化有可能导致执行性能回退，这种性能回退会导致请求RT升高甚至执行超时，这对数据库用户来说是无法接受的。
  为解决该问题，用户常用的方式是将执行计划通过一些方案固定下来(比如使用outline绑定执行计划等)，避免执行计划因上述种种因素而产生变化，但这类方案有如下缺点：

1. 不能随着环境的变化选择更优的计划。
场景a：新建索引性能更优，但执行计划已经绑定了其他索引导致不能选择更优的索引；
场景b：随着统计信息发生变化，绑定的执行计划可能已经不是最优计划； 
场景c：数据库版本演进后，无法使用最新的优化逻辑生成更好的执行计划；

2. 需要人工进行计划绑定的操作，不能完全自动化。

  本文将介绍一种能够实时自动演进执行计划的方案，该方案的主体思想和Oracle SPM类似，并且能够另外解决其不能自动实时演进和需要依赖低峰期演进问题。

自动演进功能实现

  OceanBase1.0执行计划演进功能主要利用执行计划基线，对执行计划信息进行持久化，并通过计划捕获，计划演进两部分实现。

计划基线

  执行计划基线是一种稳定执行计划的方式。 每个SQL Plan Baseline可能包含两个执行计划信息(Plan Baseline)，一个是已被验证通过的执行计划信息， 另一个是还未验证的执行计划信息，对于已验证通过的Plan Baseline会被持久化到内部表中， 并可以通过SQL ID查到对应的Plan Baseline。

计划捕获

该过程主要是为每条SQL创建Plan Baseline。计划捕获支持两种方式：自动捕获和手动捕获。
＊自动捕获
  自动捕获是默认开启的，当某条SQL请求有新的执行计划生成(主要是每日合并和升级时会重新生成计划)时，如果该SQL不存在Plan Baseline，则使用该新计划创建Plan Baseline，并直接标记为已验证通过。如果已存在，则将该计划标记为未验证计划，等待演进验证通过后才可加入到验证通过的执行计划集合中。所有标记为验证通过的执行计划信息均持久化记录到系统表中。
＊手动捕获
  手动捕获是指手动执行命令为Plan Cache中SQL创建Plan Baseline并持久化到系统表中。在实际生产系统中，对于还未使用执行计划演进功能的环境，系统已经稳定运行，其Plan Cache中的计划已经满足业务需求，在升级前通过手动捕获命令为Plan Cache中每条SQL创建Plan Baseline并持久化到系统表中，在升级后可以使用这些已经经过真实业务验证的计划，从而避免升级导致的执行计划性能回退。

计划演进

  计划演进是指将SQL Plan Baseline中待验证的执行计划进行验证。当任意一条SQL请求有新计划生成且被标记为未验证执行计划时触发实时演进，在开始演进时，Plan Cache中会生成两个执行计划，一个是根据__all_plan_baseline表中存的Plan Baseline生成的执行计划，一个是优化器新生成的待验证的执行计划。演进过程使用用户实际执行流量，不需要后台线程占用额外的资源模拟用户查询进行演进。演进过程中，所有SQL请求会按一定比例分别执行待验证的执行计划(10%)和已验证通过的执行计划执行(90%)，并收集执行的相关性能信息（CPU_TIME, DISK_READ等信息）。当SQL请求总共执行到一定次数后(可配置)结束演进，然后进行性能比对。如果未验证的执行计划性好于已验证通过的执行计划，则验证通过。否则验证不通过，并在后面的SQL执行中该计划也不会被执行到。

场景举例

  数据库系统升级后，由于各种原因可能会导致优化器生成的执行计划比升级前优化器生成执行计划性能更差的场景，使得业务查询rt一直很高，通常需要DBA介入绑定执行计划才能解决。
  下面我们通过举例说明执行计划演进功能是如何自动避免该计划性能回退的。

 CREATE TABLE t1(c1_pk INT PRIMARY KEY, c2_skew INT, c3_unique INT, key idx_u(c2_skew));
 插入1001行数据，c2_skew数据分布：1000条记录为100， 1条记录为111；
 查询SQL A： select sum(c1_pk + c2_skew + c3_unique) from t1 where c2_skew = 100; 
 SQL A对应SQL ID为：7B2EEF7D0BBF189782665D92CF8A1C8F

从上面数据分布可以看出查询SQL A走主键扫描性能会更优， 如果走索引，需要索引回表9999次，明显性能会更低。

升级前：
该条SQL存在以下一条Plan Baseline记录：

select outline_data 
from oceanbase.__all_plan_baseline 
where SQL_ID = '7B2EEF7D0BBF189782665D92CF8A1C8F';
outline_data/*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "spm_db.t1"@"SEL$1") END_OUTLINE_DATA*/

可以看出，SQL A对应的Plan Baseline的计划信息为全表扫描。

升级后：
为了测试，我们构造了对上面这条SQL生成走索引的执行计划， 第一次查询SQL A后，通过Plan Cache可以看出生成了一个走索引的执行计划。

select evolution, evo_executions, outline_data  from oceanbase.gv$plan_cache_plan_stat 
where SQL_ID = '7B2EEF7D0BBF189782665D92CF8A1C8F';evolution evo_executions  outline_data1         1           /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "spm_db.t1"@"SEL$1" "idx_u") END_OUTLINE_DATA*/其中evolution = 1 表示进入演进状态。
evo_executions表示该计划在演进过程中执行次数

第二次执行SQL A后，如下可以看到，SQL A在Plan Cache中对应两个执行计划，其中走主键全表扫描的计划是通过SQL A对应的Plan Baseline复现出来的。

select evolution, evo_executions, outline_data  from oceanbase.gv$plan_cache_plan_stat 
where SQL_ID = '7B2EEF7D0BBF189782665D92CF8A1C8F';evolution evo_executions  outline_data
1         1           /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "spm_db.t1"@"SEL$1" "idx_u") END_OUTLINE_DATA*/1         1           /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "spm_db.t1"@"SEL$1") END_OUTLINE_DATA*/

继续执行98次SQL A后，如下可以看到，走索引计划一起执行了10次，走全表扫描计划一起执行了90次, 并且走索引的新计划比走全表扫描的老计划平均CPU时间(avg_cpu_time)更大。

select cpu_time/evo_executions as avg_cpu_time, evolution, evo_executions, outline_data  from oceanbase.gv$plan_cache_plan_stat 
where SQL_ID = '7B2EEF7D0BBF189782665D92CF8A1C8F';avg_cpu_time evolution evo_executions  outline_data
3836        1          10           /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "spm_db.t1"@"SEL$1" "idx_u") END_OUTLINE_DATA*/2376        1          90           /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "spm_db.t1"@"SEL$1") END_OUTLINE_DATA*/

第101次执行SQL A时，演进结束（evolution = 0， 具体执行多少次后演进结束可配置），Plan Cache中保留更优的执行计划（消耗CPU_TIME小的），淘汰较差的执行计划，并将较优的执行计划信息更新到__all_plan_baseline表中。

select evolution, evo_executions, outline_data 
from oceanbase.gv$plan_cache_plan_statwhere SQL_ID = '7B2EEF7D0BBF189782665D92CF8A1C8F';evolution evo_executions  outline_data
0          90          /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "spm_db.t1"@"SEL$1") END_OUTLINE_DATA*/select outline_data  from oceanbase.__all_plan_baseline 
where SQL_ID = '7B2EEF7D0BBF189782665D92CF8A1C8F';outline_data
/*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "spm_db.t1"@"SEL$1")     END_OUTLINE_DATA*/

  以上演进过程介绍的是防止执行计划出现回退的情况。当出现生成的新计划比以前的计划更优时，通过上面的演进过程，可以将更优的计划持久化，并使用新的更优的计划，从而可以达到执行计划可以朝着更优的方向演进的目的。

总结

  本文针对执行计划稳定性可能出现的问题以及已有解决方案的缺点，介绍了一种执行计划自动实时演进的方案，该方案不仅能自动稳定执行计划性能，也能确保执行计划实时朝更优的方向演进，并且不需要占用额外的资源，没有低峰期执行的依赖，另外通过举例详细说明了执行计划演进过程。

本文转载自公众号OceanBase