如何让SQL语句的执行计划稳定不变-存储概要介绍(转)

刺骨的言语ヽ痛彻心扉 2022-09-19 04:28 50阅读 0赞

# 前言 #

你还在为CBO选择器下SQL语句的执行计划改变而烦恼吗？还在为是否在SQL语句前增加/\*+rule\*/而犯难呢？通过本文的介绍，你将了解到为什么CBO下同样的SQL语句而执行计划存在差异，以及我们如何通过存储概要（stored outlines）在CBO环境下达到稳定SQL语句执行计划的目的。

**关键字：CBO、执行计划、存储概要**

# 背景 #

我们日常工作中经常遇到这样的性能问题，平时运行得不错的系统，突然之间某个操作性能变得非常的慢或者某个用户在进行大版本升级后整个系统性能变得非常的差。而分析其原因往往是由于SQL语句的执行计划改变所致。例如以前走索引的变成了对大表进行全表扫描或者明明该走高效率索引的变成了走效率低下的索引等等。而这些问题在公司测试环境中可能又往往不存在，这就困扰了很多年轻的研发和技术人员，不知道该如何编写SQL语句。最后只得采用简单粗暴的方式，在SQL语句前面加上/\*+rule\*/强制使用规则模式，看似问题解决，却又带来了新的麻烦，一旦SQL语句本身书写不规范，在用户处出现性能问题，现场技术人员无法通过DBA手段快速处理，只得反馈研发人员通过程序进行调整，使得问题的处理周期变长。那么有什么办法能够避免SQL执行计划的变更使其能够稳定，接下来我们就其进行分析和讲解。

# sql执行计划为什么会变？ #

为什么我们的SQL语句执行计划会改变？如何才能稳定SQL语句的执行计划？要想回答上面的2个问题，我们就要首先知道SQL语句的执行计划是如何产生的，有那些因素影响执行计划的生成，只有了解了这些因素我们才能对症下药，稳定我们的SQL语句执行计划。

我们知道，一条SQL语句他的执行计划可能不止一个，数据库是如何确定采用那条执行计划的呢？这是由数据库的优化器所决定，它的作用就是在所有可能的执行计划当中选择一个最‘优’的执行计划。目前的数据库的优化器有2种：基于规则的RBO优化器和基于成本的CBO优化器。RBO优化器是利用一套内部规则决定SQL语句的执行计划，而不管这个执行计划是否是最佳选择；而CBO优化器则是通过一套严密的换算机制把该SQL每个执行计划的执行成本计算出来，然后选择成本最‘小’的那个作为该SQL语句的执行计划。从描述中可以看出，在理想环境中采用CBO优化器决定SQL语句的执行计划更为科学。

目前我们的用户普遍采用Oracle 10g的数据库，Oracle 10g默认的优化器CBO即为基于成本的优化器，但是正是由于CBO的这种选择机制，由于种种原因，可能导致CBO计算出的最‘小’成本执行计划出现变更，从而导致该SQL的执行计划出现变更。而影响CBO计算成本的因素有io、cpu、网络、统计信息、初始化参数等，如果这些环境发生了变化，就会导致CBO在计算SQL语句的执行计划成本值发生改变，从而最终可能导致执行计划的选择发生改变。

![如何让SQL语句的执行计划稳定不变-存储概要介绍(转) - 666 - 666][SQL_-_ - 666 - 666]

除了由于优化器的原因可能导致SQL语句执行计划改变外，如果数据结构改变，也可能导致执行计划的变化，最常见的就是如索引的创建或失效、普通表变为分区表等，这些数据结构的改变同样可能会导致SQL执行计划的改变。

## 常见稳定SQL执行计划的方法 ##

通过前面的介绍我们了解了SQL语句执行计划变化的原因，为了使SQL语句的执行计划固定，可以采用下面的几种方式来稳定SQL语句的执行计划，不过虽然它们都能稳定SQL语句的执行计划，但是每种方式都有一定的缺陷，要根据实际情况进行甄酌。

**1. 选用规则（RBO）优化器**

通过前面的介绍我们知道，RBO下的SQL语句的执行计划是通过一定的规则生成的，也就是说一旦SQL语句确定，那么它的执行计划也就确认了，只要数据结构不变，它的执行计划就始终不会改变。

**缺陷**：同样正是由于RBO下SQL语句的执行计划是基于规则生成，首先就要求SQL编写人员有一定的性能优化知识，编写时就充分考虑RBO的规则，否则可能导致执行计划效率不高，这对程序编写人员的能力要求就比较高，而我们很多程序人员不具备这方面的能力；再次Oracle公司明确指出不再对RBO进行维护，意味这以后可能逐渐放弃这种优化器而用CBO取代，例如10g以后默认的优化器都是CBO模式，为了产品的持续发展，也迫使我们选用更合理的CBO模式。

**2. 加提示字/\*+hints \*/**

虽然数据库的优化器选择了CBO模式，但是我们可以对单独的SQL 语句采用加提示字/\*+rule\*/的方式指定该语句采用RBO模式，这样即不违背数据库整体使用CBO的模式，又对指定SQL使用了RBO，稳定了这些SQL语句的执行计划，这也是目前我们程序员常用的一种稳定SQL语句执行计划的方式。

**缺陷**：目前我们程序中大量使用了/\*+rule\*/的方式稳定指定SQL语句的执行计划，这种方式虽然简单有效，但是同样暴露出很多问题。首先就是同样由于书写SQL不规范的原因导致RBO下生成可能不是最高效的执行计划，在用户处经常出现性能问题，而这时SQL调整由于有提示字，必须通过程序人员修改程序调整，处理问题的周期变长；另外后期ZLHIS可能采用表分区技术，Oracle有个规则就是，一旦SQL语句中有分区表，加提示字采用RBO的方式将失效，这势必带来性能隐患。

**3. 统计信息锁定**

前面我们说了，在CBO环境下，影响执行计划的因素有I/O、CPU、数据库参数和统计信息，其中在我们用户环境中，最可能改变的是统计信息，而其他因素一般情况下一旦服务器部署完成，不会轻易更改。既然统计信息的改变是执行计划不稳定的主要因素，那么我们就想办法把统计信息锁定。Oracle提供了dbms\_stats包来把统计信息进行锁定，这样统计信息就不会随实际数据增长而改变，而被锁定在一个指定的范围内，如下图，我们把住院费用记录表和其索引的统计信息锁定

sql>**exec** dbms\_stats.lock\_table\_stats(ownname => 'ZLHIS',tabname => '住院费用记录')

一旦统计信息锁定，这时我们对其用dbms\_stats,analyze收集统计信息将不起作用，如果要对其进行收集，必须通过下面的语句解锁才有效。

sql>**exec** dbms\_stats.unlock\_table\_stats(ownname => 'ZLHIS',tabname => '住院费用记录')

**缺陷**：锁定统计信息的缺陷也显而易见，锁定后的统计信息无法反映数据表的真实情况，这样计算出来的执行计划的成本肯定也是不真实的，而如果我们想要真实的统计信息，就必须首先解锁，然后还要之前锁定对象的统计信息重新收集一遍，这势必耗费大量资源。

**4. 采用存储概要**

存储概要（stored outlines）是Oracle8.1开始出现的一个稳定SQL语句执行计划的特性，在Oracle10g得到了进一步完善和加强，接着我们通过单独一个章节对其进行详细的介绍。

## 存储概要 ##

**1. 什么是存储概要**

Oracle 8i开始提供了一个捕获SQL语句执行计划并保存为存储概要的机制，一个存储概要实际上是一条由DBA或开发者优化过的存储在‘概要’方案中的SQL语句，当一条SQL语句被执行且它与存储概要完全匹配，Oracle就使用存储概要替换使用的SQL语句，从而使该SQL语句不再进行解析，而直接使用保存在存储概要中的信息，其中就包括执行计划。而存储概要非常灵活，它即可以分阶段执行也可以限制到仅那些匹配的会话才能执行。

**2. 存储概要的原理**

其实通过前面的定义，我们已经大概知道了存储概要的工作原理，接着我们通过一个流程图更加直观的了解下存储概要是如何起到稳定执行计划的。

![如何让SQL语句的执行计划稳定不变-存储概要介绍(转) - 666 - 666][SQL_-_ - 666 - 666 1]

通过上面的流程图我们可以看到，如果部署了存储概要，SQL语句在执行前首先去匹配存储概要，校验存储概要中是否有与执行SQL匹配的语句，如果有就提取存储概要中保存的执行计划直接执行SQL，如果没有再按正常流程生成执行计划，再执行SQL。正是由于存储概要中保存了SQL语句的执行计划，而每次执行其中的SQL语句时，不再生成而直接提取保存的执行计划，因此就起到了稳定SQL执行计划的作用。

**3. 存储概要的部署**

讲了这么多存储概要，那么存储概要如何创建和部署呢？接下来我们就通过一个案例进行讲解，我们首选知道，存储概要保存了SQL语句的执行计划，而执行计划是通过解析得到，因此存储概要必须要SQL语句被解析的时候才能被创建与应用，如果想创建存储概要的SQL已经被缓存了，我将会发现这种SQL无法使用我们新创建的存储概要, 我们使用存储概要可以稳固数据库的所有SQL的执行计划，也可以稳固某些指定的SQL语句，这就需要根据不同的需要具体考虑，我们分别进行演示

**3.1． 数据库所有SQL语句创建存储概要**

要稳定数据库所有的S QL语句，我们只需要修改数据库的CREATE\_STORED\_OUTLINES参数，如下：

sql> alter system set CREATE\_STORED\_OUTLINES = true;

或

sql> alter system set CREATE\_STORED\_OUTLINES = zlhis\_outline;

存储概要是分category(类目)的，当CREATE\_STORED\_OUTLINES = true时，Oracle使用默认的“default”类目，当CREATE\_STORED\_OUTLINES = zlhis\_outline时，Oracle将使用我们自定义的“zlhis\_outline”类目；当我们设置了CREATE\_STORED\_OUTLINES参数时，Oracle将自动为了后续所有编译的SQL创建outline到CREATE\_STORED\_OUTLINES指定的类目下，当我们把CREATE\_STORED\_OUTLINES设置为false时，Oracle就停止创建outline，这种机制是为了对SQL进行分类管理，这里我们就不再叙述，通过上面的设置，只要数据库的SQL进行了解析，就会创建该SQL对应的存储概要,但是要想让SQL语句下次执行的时候使用存储概要，还需要执行下面的操作。

sql> alter system set USE\_STORED\_OUTLINES =true;

或

sql> alter system set USE\_STORED\_OUTLINES =zlhis\_outline;

如何查看存储概要是否生效呢？我们可以通过查询USER\_OUTLINES视图检阅，语句如下：

![如何让SQL语句的执行计划稳定不变-存储概要介绍(转) - 666 - 666][SQL_-_ - 666 - 666 2]

NAME是存储概要的名称，它命名规则是由数据库自己定义，SQL\_TEXT是SQL语句的脚本，也是存储概要匹配的依据，USED表示这个存储概要是否使用，UNUNSED表示存储概要创建后还没有使用，只有等其对应的SQL再次执行匹配了该存储概要，状态就会变成USED。

**3.2． 指定SQL创建存储概要**

前面对整个数据库所有SQL语句创建存储概方法比较简单，但是大多数时候我们并不需要这样做，这样既浪费资源，又不利用管理。我们只需要对指定的某些SQL语句创建存储概要就可以了，这里我们就要使用数据库自带的dbms\_ outln包，通过dbms\_ outln.create\_outline根据已有的执行正常的SQL游标来创建存储概要，我们来具体操作下

假如我们想创建指定SQL语句” **Select \* From 部门表 Where Id=1584**”的存储概要，我们首先需要在SQL游标中找到该语句的hash\_value、child\_number值，可以通过v$sql视图进行查找（注意：该视图中存在上述SQL语句的前提是该SQL已经执行过，如果SQL语句从未执行，则视图中无法找到该SQL语句的游标），查找语句如下

![如何让SQL语句的执行计划稳定不变-存储概要介绍(转) - 666 - 666][SQL_-_ - 666 - 666 3]

找到对应SQL语句的hash\_value、child\_number值后，接着把这2个值带入dbms\_ outln.create\_outline包，创建对应的存储概要，如下：

sql>**exec** dbms\_ outln.create\_outline(hash\_value => '3345575829',child\_number => 0,category => '测试\_存储概要');

接着同样要执行命令启用该存储概要，如下

sql>alter system set USE\_STORED\_OUTLINES =测试\_存储概要;

最后查询USER\_OUTLINES视图，检阅刚才创建的存储概要是否生效，如下

![如何让SQL语句的执行计划稳定不变-存储概要介绍(转) - 666 - 666][SQL_-_ - 666 - 666 4]

**4. 测试存储概要效果**

讲了这么多存储概要，也大概了解了存储概要的创建方法，那么存储概要是否真如其描述的能够稳定SQL语句的执行计划呢？下面我们就来做一个简单的实验，看看存储概要在稳定SQL语句执行计划所起的作用。

实验思路：我们编写一段SQL语句，并且确认这段SQL语句在CBO和RBO条件下执行计划是不同的，然后我们在CBO环境下创建该SQL的存储概要，并使其生效，最后再次比较CBO和RBO环境下的执行计划，看执行计划有什么改变。

1. 我们看下面的相同的SQL语句在CBO和RBO下执行计划存在差异。

![如何让SQL语句的执行计划稳定不变-存储概要介绍(转) - 666 - 666][SQL_-_ - 666 - 666 5]

2. 接着我们用前面介绍的方式，创建该SQL语句的概要文件，并使其生效，下面是创建存储概要的详细步骤。

![如何让SQL语句的执行计划稳定不变-存储概要介绍(转) - 666 - 666][SQL_-_ - 666 - 666 6]

3. 接着我们重复第一步的操作，查看CBO和RBO下该SQL语句的执行计划，得到的结果如下：

![如何让SQL语句的执行计划稳定不变-存储概要介绍(转) - 666 - 666][SQL_-_ - 666 - 666 7]

可以清楚的看到，现在无论是在CBO还是RBO下，该SQL语句的执行计划都是一样的，事实证明存储概要在稳定SQL执行计划上起了作用，达到我们想要的效果。

**5. 存在的缺陷**

同其他稳定SQL语句执行计划的方式一样，存储概要同样存在这一定的缺陷。

他是通过SQL文本进行匹配，一旦SQL语句改变就必须生成新的对应的存储概要，因此管理维护比较麻烦；

存储概本身有单独额外的开销，新解析的SQL语句Oracle都将检查是否存在一个相关的存储概要这是一笔不小的代价，另外这些信息的表是创建保存在system表空间中的，对于一个生产系统来讲当你开始创建存储概要的时候你将发现其在system表空间中使用了大量的空间。

不过Oracle从8i开始，一直对存储概要进行着不断的优化改进，功能也越来越完善，例如除了稳定执行计划，还有添加存储概要hints的功能等**，**而且存储概要还有个最大的特点就是可以进行迁移，相信只要经过充分的验证，其在中联产品中的应用还是大有可为的。

## 总结 ##

通过本文的介绍，我们应该掌握以下几点知识：

1.了解影响SQL语句执行计划的因素。

2.常见稳定SQL语句执行计划的方法以及他们的优缺点。

3.对存储概要有了一定的认识和了解，知道它是如何稳定SQL语句执行计划的，并且能够进行简单的部署。

[SQL_-_ - 666 - 666]: /images/20220828/80b2434e04f147cbac49a65eb3907294.png
[SQL_-_ - 666 - 666 1]: /images/20220828/ef5b47b65c9e42aa9523e70a3a76aa8e.png
[SQL_-_ - 666 - 666 2]: /images/20220828/c0f77895d2ac4ef79d5be5d0fd056168.png
[SQL_-_ - 666 - 666 3]: /images/20220828/d0d094cb22774621bc74d1c2377d6247.png
[SQL_-_ - 666 - 666 4]: /images/20220828/7f3706c966fb4daebd77eb7f00ab34fb.png
[SQL_-_ - 666 - 666 5]: /images/20220828/426cdbcb36f540b2bec91d59a82aaefd.png
[SQL_-_ - 666 - 666 6]: /images/20220828/41f403b9ead6440389160bccb3f68297.png
[SQL_-_ - 666 - 666 7]: /images/20220828/7bd6751474b44e9f82ea2b214eaff732.png