系统性能随着并发数量的递增而显著下降的现象，往往是因为没有使用绑定变量而产生大量硬解析所致。

Cursor，游标

Cursor 是 Oracle 数据库里 SQL 解析和执行的载体。有两种类型的 Cursor：Shared Cursor，Session Cursor。

库缓存对象

库缓存（Libaray Cache）实际上是 SGA 中的一块内存（更确切说是 Shared Pool 中的一块内存区域），它的主要作用是缓存刚刚执行过的 SQL 语句和 PL/SQL 语句（如存储过程、函数、包、触发器）所对应的执行计划、解析树（Parse Tree）、Pcode、Mcode 等对象，当同样的 SQL 语句和 PL/SQL 语句再次被执行时，就可以利用缓存在 Libaray Cache 中的那些对象而无须再次从头开始解析，这就提高了这些 SQL 语句和 PL/SQL 语句在重复执行时的执行效率。

缓存在库缓存中的对象称为库缓存对象（Library Cache Object），所有的库缓存对象都是以一种名为库缓存对象句柄（Library Cache Object Handle）的结构存储在库缓存中的，Oracle 是通过访问相关的库缓存对象句柄来访问对应的库缓存对象的。

库缓存对象句柄是以哈希表（Hash Table）的方式存储在库缓存中的。

Shared Cursor

Shared Cursor 是 Oracle 缓存在 Library Cache 中的几十种缓存对象之一，它所对应的库缓存对象句柄的 Namespace 属性的值是 CRSR（也就是 Cursor 的缩写）。

Shared Cursor 会存储目标 SQL 的 SQL 文本、解析树、该 SQL 所涉及的对象定义、该 SQL 所使用的绑定变量类型和长度，以及该 SQL 的执行计划等信息。

Oracle 数据库中的 Shared Cursor 又细分为 Parent Cursor （父游标）和 Child Cursor（子游标）这两种类型，可以通过分别查询视图 V$SQLAREA 和 V$SQL 来查看当前缓存在库缓存中的 Parent Cursor 和 Child Cursor，其中 V$SQLAREA 用于查看 Parent Cursor，V$SQL 用于查看 Child Cursor。

在 Oracle 数据库里，任意一个目标 SQL 一定会同时对应两个 Shared Cursor，其中一个 Parent Cursor，另一个是 Child Cursor， Parent Cursor 存储该 SQL 的SQL 文本，该 SQL 真正可以重用的解析树和执行计划则存储在 Child Cursor 中。

Oracle 在解析目标 SQL 时去库缓存中查找匹配 Shared Cursor 的过程：

1. 根据目标 SQL 的 SQL 文本的哈希值去库缓存中找匹配的 Hash Bucket。准确地说，哈希运算是基于对应库缓存对象句柄的属性 Name 和 Namespace 的值的，只不过对于 SQL 语句而言，其对应的库缓存对象句柄的属性 Name 的值就是该 SQL 的 SQL 文本，属性 Namespace 的值就是常量 “CRSR”，所以可以看似是只根据目标 SQL 的 SQL 文本来做哈希运算。

2. 在匹配的 Hash Bucket 的库缓存对象链表中查找匹配的 Parent Cursor，在查找匹配 Parent Cursor 的过程中肯定会比对目标 SQL 的 SQL 文本（不同 SQL 文本计算出来的哈希值可能相同）。

3. 步骤 2 如果找到了匹配的 Parent Cursor，接下来会遍历从属于该 Parent Cursor 的所有 Child Cursor 以查找匹配的 Child Cursor。

4. 步骤 2 如果找不到匹配的 Parent Cursor，意味着没有可以共享的解析树和执行计划，就从头开始解析上述目标 SQL，新生成一个 Parent Cursor 和一个 Child Cursor，并把它们挂在对应的 Hash Bucket 中。

5. 如果找到了匹配的 Child Cursor，就把存储于该 Child Cursor 中的解析树和执行计划直接拿过来重用，而不用从头开始解析。

6. 如果找不到匹配的 Child Cursor，就从头开始解析上述目标 SQL，新生成一个 Child Cursor，并把这个 Child Cursor 挂在对应的 Parent Cursor 中。

硬解析

硬解析（Hard Parse）是指 Oracle 在执行目标 SQL 时，在库缓存中找不到可以重用的解析树和执行计划，而不得不从头开始解析目标 SQL 并生成相应的 Parent Cursor 和 Child Cursor 的过程。

硬解析的危害：

1. 硬解析可能会导致 Shared Pool Latch 的争用。Oracle 必须在 Shared Pool 中分配出一块内存区域来存储新生成的 Child Cursor，这个分配动作必须在持有 Shared Pool Latch 的前提下进行。

2. 硬解析可能会导致库缓存相关 Latch（如 Library Cache Latch） 和 Mutex 的争用。无论哪种类型的硬解析，都需要扫描相关的 Hash Bucket 中的库缓存对象句柄链表，这是需要在持有 Library Cache Latch 的。从 11gR1 开始，Oracle 用 Mutex 替换了库缓存相关的 Latch，Latch 争用就变为 Mutex 争用。

Oracle 在做硬解析时对 Shared Pool Latch 和 Library Cache Latch 的持有过程大致如下：Oracle 首先持有 Library Cache Latch，在库缓存中扫描相关的 Hash Bucket 中的库缓存对象句柄链表，以查看是否有匹配的 Parent Cursor，然后释放 Library Cache Latch。接下来是硬解析的后半部分，首先持有 Library Cache Latch，然后持有 Shared Pool Latch，以便从 Shared Pool 中申请分配内存，成功申请后就会释放 Shared Pool Latch，最后再释放 Library Cache Latch。

绑定变量

绑定变量（Bind variable）是一种特殊类型的变量，又被称为占位符（Placeholder），绑定变量通常出现在目标 SQL 的 SQL 文本中，用于替换 SQL 文本中 where 条件或者 values 子句中的具体输入值。

绑定变量的使用语法是 :variable_name，即冒号和自定义变量名称的组合来替换目标 SQL 中的 SQL 文本中的具体输入值，变量名可以是字母、数字或者字母与数字的组合。

绑定变量的作用

只要待执行的目标 SQL 的 SQL 文本稍有不同，那么据此计算出来的哈希值就极有可能不同（就算是哈希值相同的，Oracle 还会继续比对 Parent Cursor 所对应的 SQL 文本），也就是说这些 SQL 文本不完全相同的目标 SQL 之间是没法重用解析树和执行计划的。

对于 OLTP 的系统，绑定变量可以有效降低系统硬解析的数量。

PL/SQL 中使用绑定变量的标准语法：
execute immediate [带绑定变量的目标SQL] using [对应绑定变量的具体输入值，多个用逗号分隔];

PL/SQL 代码中给绑定变量赋值的关键字是 using，目标 SQL 中有几个绑定变量，using 后面就跟几个具体的输入值。关键字 using 后传入的绑定变量具体输入值只与对应绑定变量在目标 SQL 中所在的位置有关，而与其名称无关。

sql := 'delete from emp where cc = :1 returning ename into :2';

关键字 returning 可以和带绑定变量的目标 SQL 连用，目的是把受该 SQL 影响的行记录的对应字段的值给取出来。

PL/SQL 中的批量绑定的优势在于它是一次处理一批数据，而不是像像常规那样一次只处理一条数据，所以它能够减少 PL/SQL 引擎和 SQL 引擎上下文切换的次数。

PL/SQL 引擎可以看作是 Oracle 数据库中专门用来处理 PL/SQL 代码中除 SQL 语句之外所有剩余部分（如变量、复制、循环、数组等）的子系统；而 SQL 引擎则是 Oracle 数据库中专门用来处理 SQL 语句的子系统，这里的 PL/SQL 引擎和 SQL 引擎上下文切换就是指它们之间的交互。

理论上 PL/SQL 代码里只要执行 SQL 语句，就会发生 PL/SQL 引擎和 SQL 引擎的交互，但对 PL/SQL 代码性能有影响的交互主要发生在 PL/SQL 代码中的如下两处：

• 显式游标或参考游标需要循环执行 fetch 操作时。这里的循环操作由 PL/SQL 引擎来处理，而 fetch 一条记录对应要执行的 SQL 语句则需要 SQL 引擎来处理，所以如果不做任何优化，那么这里每 fetch 一条记录，PL/SQL 引擎就需要和 SQL 引擎交互一次。
• 显式游标或参考游标的循环内部需要执行 SQL 语句时，还是和上述远离一样，每 fetch 一条记录，PL/SQL 引擎就需要和 SQL 引擎交互一次。

PL/SQL 代码中批量 fetch 所对应的语法为：
fetch cursor_name bulk collect into [自定义的数组] <limit CN_BATCH_SIZE>

limit CN_BATCH_SIZE 表示一次批量 fetch 的最多记录数，通常建议为 1000。如果不指定则 fetch 所有的记录，这是不可取的，给 PGA 造成极大压力。

forall  i  in  1..[自定义数组的长度]
     execute immediate  [带绑定变量的目标 SQL]  using  [对应绑定变量的具体输入值];

这里的关键字 forall 表示一次执行一批 SQL 语句，可以和 insert, update, delete 语句联用。

绑定变量窥探（Peeking）

对于使用绑定变量的目标 SQL 而言，Oracle 可以选择如下两种方法来决定其执行计划：

• 使用绑定变量窥探；
• 如果不使用绑定变量窥探，则对于那些可选择率可能会随着具体输入值的不同而不同的谓词条件使用默认的可选择率（例如 5%）。

当绑定变量窥探被启用后，每当 Oracle 以硬解析的方式解析使用了绑定变量的目标 SQL 时，Oracle 都会实际窥探一下对应的绑定变量的具体输入值，并以这些具体输入值为标准，来决定这些使用了绑定变量的目标 SQL 的 where 条件的 selectivity 和 Cardinality 的值，并据此来选择该 SQL 的执行计划。注意，这个窥探的动作只在硬解析的时候才会执行，当使用了绑定变量的 SQL 再次执行时（此时对应的是软解析/软软解析），即便此时对应的绑定变量的具体输入值和之前硬解析时对应的值不同，Oracle 也会沿用之前硬解析时所产生的解析树和执行计划，而不再重复执行上述窥探动作。

使用 DDL 操作可以让 Oracle 再次执行目标 SQL 时使用硬解析，弊端在于一旦对某个表执行了 DDL 操作，再次执行与这个表相关的所有 SQL 时就会全部使用硬解析。对于 OLTP 系统而言，可能会导致短时间内的硬解析数量剧增，进而影响系统性能。

绑定变量分级（Bind Graduation）

绑定变量分级是指 Oracle 在 PL/SQL 代码中会根据文本型绑定变量的定义长度而将这些文本型绑定变量分为四个等级：

• 第一级：定义长度在 32 字节以内，固定分配 32 字节内存空间；
• 第二级：定义长度在 33–128 字节，固定分配 128 字节内存空间；
• 第三级：定义长度在 129 — 2000 字节，固定分配 2000 字节内存空间；
• 第四级：定义长度在 2001 字节以上，分配 4000 字节内存空间。

这些内存分配是在 PGA 区的。

绑定变量分级仅适用于文本型的绑定变量，这意味着 Oracle 不会对数据值 NUMBER 型的绑定变量做分级。数值型的变量最多占用 22 字节，Oracle 统一分配 22 字节。

Child Cursor 中除了会存储目标 SQL 的解析树和执行计划之外，还会存储该 SQL 所使用的绑定变量的类型和长度，这意味着即使该 SQL 的 SQL 文本没有发生任何改变，只要其 SQL 文本中文本型绑定变量的定义长度发生了变化，那么该 SQL 再次执行时就可能还是做硬解析。

绑定变量的个数不宜太多

目标 SQL 的 SQL 文本中绑定变量的个数不宜太多，否则可能会导致目标 SQL 总的执行时间大幅度增长。增长的时间主要耗费在执行目标 SQL 时对每个绑定变量都用其实际的值来替换（这个过程即所谓的绑定变量值替换），目标 SQL 的 SQL 文本中的绑定变量的个数越多，这个替换过程所耗费的时间就越长，该 SQL 总的执行时间也就越长。

如何得到已执行的绑定变量的值

如果满足如下两个条件之一，则该 SQL 中的绑定变量的具体输入值就会被 Oracle 捕获，并可通过视图 V$SQL_BIND_CAPTURE 查询：

• 当含有绑定变量的目标 SQL 以硬解析的方式被执行；
• 当含有绑定变量的目标 SQL 以软解析/软软解析的方式重复执行时，该 SQL 中的绑定变量的具体输入值也可能会被 Oracle 捕获，只不过默认情况下这种捕获操作 Oracle 至少得间隔 15 分钟才会做一次。

Oracle 只会捕获那些位于目标 SQL 的 where 条件中的绑定变量的具体输入值，而对于那些使用了绑定变量的insert 语句，不管该 insert 语句是否是以硬解析的方式执行，Oracle 始终不会捕获其 values 子句中对应的绑定变量的具体输入值。

Oracle 里的游标共享（Cursor Sharing）

游标共享是指 Shared Cursor 间的共享，就是重用存储在 Child Cursor 中的解析树和执行计划而不用从头开始解析。

使用绑定变量面临如下两个问题：

• 很多 OLTP 系统没有使用绑定变量；
• 对于使用了绑定变量的目标 SQL 只会沿用硬解析时产生的解析树和执行计划，即使这种沿用完全不适合当前的情形。

Oracle 引入了常规游标共享来解决第一个问题，自适应游标共享来解决第二个问题。

常规游标共享

开启了常规游标共享后，Oracle 在解析目标 SQL 之前，会先用系统产生的绑定变量来替换目标 SQL 的 SQL 文本中 where 条件或者 values 子句中的具体输入值，这样替换后实际执行的 SQL 就已经是使用了绑定变量的的改写后的等价 SQL。

---

欢迎关注我的微信公众号: coderbee笔记，可以更及时回复你的讨论。