Oracle里的Cursor(一) ——shared cursor

共计 8920 个字符，预计需要花费 23 分钟才能阅读完成。

Cursor 直译过来就是“游标”，它是 Oracle 数据库中 SQL 解析和执行的载体。Oracle 数据库是用 C 语言写的，可以将 Cursor 理解成是 C 语言的一种结构(Structure)。

Oracle 数据库里的 Cursor 分为两种类型：一种是 Shared Cursor；另一种是 Session Cursor。本文先介绍 Shared Cursor。

1 Oracle 里的 Shared Cursor。

1.1 Shared Cursor 的含义

Shared Cursor 就是指缓存在库缓存里的一种库缓存对象，说白了就是指缓存在库缓存里的 SQL 语句和匿名 PL/SQL 语句所对应的库缓存对象。Shared Cursor 是 Oralce 缓存在 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 和 Chile Cursor，其中 V$SQLAREA 用于查看 Parent Cursor，V$SQL 用于查看 Child Cursor。

Parent Cursor 和 Child Cursor 的结构是一样的(它们都是以库缓存对象名柄的方式缓存在库缓存中，Namespace 属性的值均为 CRSR)，它们的区别在于目标 SQL 的 SQL 文本会存储在其 Parent Cursor 所对应的库缓存对象句柄的属性 Name 中(Child Cursor 对应的库缓存对象名柄的 Name 属性值为空，这意味着只有通过 Parent Cursor 才能找到相应的 Child Cursor)，而该 SQL 的解析树和执行计划则会存储在其 Child Cursor 所对应的库缓存对象句柄的 Heap 6 中，同时 Oracle 会在该 SQL 所对应的 Parent Cursor 的 Heap 0 的 Chhild table 中存储从属于该 Parent Cursor 的所有 Child Cursor 的库缓存对象名柄地址(这意味着 Oracle 可以通过访问 Parent Cursor 的 Heap 0 中的 Child table 而依次顺序访问从属于该 Parent Cursor 的所有 Child Cursor)。

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

Oracle 设计这种 Parent Cursor 和 Child Cursor 并存的结果是因为 Oralce 是根据目标 SQL 的 SQL 文本的哈希值去相应 Hash Bucket 中的库缓存对象句柄链表里找匹配的库缓存对象句柄的，但是不同的 SQL 文本对应的哈希值可能相同，而且同一个 SQL(此时的哈希值自然是相同的)也有可能有多份不同的解析权和执行计划。可以想象一下，如果它们都处于同一个 Hash Bucket 中的库缓存对象句柄链表里，那么这个库缓存对象句柄的长度就不是最优的长度(这意味着会增加 Oracle 从头到尾搜索这个库缓存对象句柄链表所需要耗费的时间和工作量)，为了能尽量减少对应 Hash Bucket 中库缓存对象句柄链表的长度，Oracle 设计了这种嵌套的 Parent Cursor 和 Child Cursor 并存的结构。

下面看一个 Parent Cursor 和 Child Cursor 的实例：

sys@MYDB>conn zx/zx
Connected.
zx@MYDB>select empno,ename from emp;
 
    EMPNO ENAME
———- ——————————
      7369 SMITH
…… 省略部分输出
 
14 rows selected.

当一条 SQL 第一次被执行的时候，Oracle 会同时产生一个 Parent Cursor 和一个 Child Cursor。上述 SQL 是首次执行，所以现在 Oracle 应该会同时产生一个 Parent Cursor 和一个 Child Cursor。使用如下语句验证：

select sql_text,sql_id,version_count from v$sqlarea where sql_text like ‘select empno,ename%’;

注意到原目标 SQL 在 V$SQLAREA 中只有一条匹配记录，且这条记录的列 VERSION_COUNT 的值为 1(VERSION_COUNT 表示这个 Parent Cursor 所拥有的所有 Child Cursor 的数量)，这说明 Oracle 在执行目标 SQL 时确实产生了一个 Parent Cursor 和一个 Child Cursor。

上述 SQL 所对应的 SQL_ID 为“78bd3uh4a08av”，用这个 SQL_ID 就可以去 V$SQL 中查询该 SQL 对应的所有 Child Cursor 的信息：

zx@MYDB>col sql_text for a50
zx@MYDB>select sql_text,sql_id,version_count from v$sqlarea where sql_text like ‘select empno,ename%’;
 
SQL_TEXT                      SQL_ID                  VERSION_COUNT
————————————————– ————————————— ————-
select empno,ename from emp            78bd3uh4a08av                    1

注意到目标 SQL_ID 在 V$SQL 中只有一条匹配记录，而且这条记录的 CHILD_NUMBER 的值为 0(CHILD_NUMBER 表示某个 Child Cursor 所对应的子游标号)，说明 Oracle 在执行原目标 SQL 时确实只产生了一个子游标号为 0 的 Child Cursor。

把原目标 SQL 中的表名从小写换成大写的 EMP 后再执行：

zx@MYDB>select empno,ename from EMP;
 
    EMPNO ENAME
———- ——————————
      7369 SMITH
…… 省略部分输出
 
14 rows selected.

Oracle 会根据目标 SQL 的 SQL 文本的哈希值去相应的 Hash Bucket 中找匹配的 Parent Cursor，而哈希运算是对大小写敏感的，所以当我们执行上述改写后的目标 SQL 时，大写 EMP 所对应的 Hash Bucket 和小写 emp 所对应的 Hash Bucket 极有可能不是同一个 Hash Bucket(即便是同一个 Hash Bucket 也没有关系，因为 Oracle 还会继续比对 Parent Cursor 所在的库缓存对象句柄的 Name 属性值，小写所对应的 Parent Cursor 的 Name 值为“select empno,ename from emp”，大写 EMP 对就的 Parent Cursor 的 Name 值为“select empno,ename from EMP”，两者显然不相等)。也就是说，小写 emp 所对应的 Parent Cursor 并不是大写 EMP 所要找的 Parent Cursor，两者不能共享，所以此时 Oracle 肯定会新生成一对 Parent Cursor 和 Child Cursor。

下面来验证一下：

zx@MYDB>select sql_text,sql_id,version_count from v$sqlarea where sql_text like ‘select empno,ename%’;
 
SQL_TEXT                      SQL_ID                  VERSION_COUNT
————————————————– ————————————— ————-
select empno,ename from emp            78bd3uh4a08av                    1
select empno,ename from EMP            53j2db788tnx9                    1
 
zx@MYDB>select plan_hash_value,child_number from v$sql where sql_id=’53j2db788tnx9′;
 
PLAN_HASH_VALUE CHILD_NUMBER
————— ————
    3956160932      0

从上述结果可以看出，针对大写 EMP 所对应的目标 SQL(大写 EMP)，Oracle 确实新生成了一个 Parent Cursor 和一个 Child Cursor。

现在构造一个同一个 Parent Cursor 下有不同 Child Cursor 的实例：

使用 scott 用户登录，再次执行小写 emp 所对应的目标 SQL：

zx@MYDB>conn scott/tiger
Connected.
scott@MYDB>select empno,ename from emp;
 
    EMPNO ENAME
———- ——————————
      7369 SMITH
…… 省略部分输出
 
14 rows selected.

Oracle 根据目标 SQL 的 SQL 文本的哈希值去相应的 Hash Bucket 中找匹配的 Parent Cursor，找到了匹配的 Parent Cursor 后还得遍历从属于该 Parent Cursor 的所有 Child Cursor(因为可以被重用的解析权和执行计划都存储在 Child Cursor 中)。

对上述 SQL(小写 emp)而言，因为同样的 SQL 文本之前在 ZX 用户下已经执行过，在 Library Cache 中也已经生成了对应的 Parent Cursor 和 Child Cursor，所以这里 Oracle 根据上述 SQL 的 SQL 文本的哈希值去 Library Cache 中找匹配的 Parent Cursor 时肯定时能找到匹配记录的。但接下来遍历从属于该 Parent Cursor 的所有 Child Cursor 时，Oracle 会发现对应 Child Cursor 中存储的解析权和执行计划此时是不能被重用的，因为此时的 Child Cursor 里存储的解析树和执行计划针对的是 ZX 用户下的表 EMP，面上述 SQL 针对的则是 SCOTT 用户下的同名表 EMP，待查询的目标表根本就不是同一个表，解析权和执行计划当然不能共享了。这意味着 Oracle 还得针对上述 SQL 从头再做一次解析，并把解析后的解析树和执行计划存储在一个新生成的 Child Cursor 里，再把这个 Child Cursor 挂在上述 Parent Cursor 下(即把新生成的 Child Cursor 在库缓存对象句柄地址添加到上述 Parent Cursor 的 Heap 0 的 Child table 中)。也就是说一旦上述 SQL 执行完毕，该 SQL 所对应的 Parent Cursor 下就会有两个 Child Cursor，一个 Child Cursor 中存储的是针对 ZX 用户下表 EMP 的解析树和执行计划，另外一个 Child Cursor 中存储的则是针对 SCOTT 用户下同名表 EMP 的解析树和执行计划。

使用如下语句验证：

scott@MYDB>select sql_text,sql_id,version_count from v$sqlarea where sql_text like ‘select empno,ename%’;
 
SQL_TEXT                      SQL_ID                  VERSION_COUNT
————————————————– ————————————— ————-
select empno,ename from emp            78bd3uh4a08av                    2
select empno,ename from EMP            53j2db788tnx9                    1

注意到上述 SQL(小写 emp)V$SQLAREA 中的匹配记录的列 VERSION_COUNT 的值为 2，说明 Oracle 在执行该 SQL 时确实产生了一个 Parent Cursor 和两个 Child Cursor。

使用如下语句查询上述 SQL 所对应的 Child Cursor 的信息：

scott@MYDB>select plan_hash_value,child_number from v$sql where sql_id=’78bd3uh4a08av’;
 
PLAN_HASH_VALUE CHILD_NUMBER
————— ————
    3956160932      0
    3956160932      1

注意到上述 SQL 在 V$SQL 中有两条匹配记录，且这两条记录的 CHILD_NUMBER 的值分别为 0 和 1，说明 Oracle 在执行上述 SQL 时确实产生了两个 Child Cursor，它们的子游标号分别为 0 和 1.

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，则 Oracle 接下来就会遍历从属于该 Parent Cursor 的所有 Child Cursor 以查找匹配的 Child Cursor。

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

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

(6)步骤 3 如果找不到匹配的 Child Cursor，则意味着没有可以共享的解析树和执行计划，接下来 Oracle 也会从头开始解析上述目标 SQL，新生成一个 Child Cursor，并把这个 Child Cursor 挂在对应的 Parent Cursor 下。

1.2 硬解析

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

硬解析实际上有两种类型，一种是在库缓存中找不到匹配的 Parent Cursor，此时 Oracle 会从头开始解析目标 SQL，新生成一个 Parent Cursor 和 Child Cursor，并把它们挂在对应的 Hash Bucket 中；另一种是找到了匹配的 Parent Cursor 但未找到匹配的 Child Cursor，此时 Oracle 也会从头开始解析该目标 SQL，新生成一个 Child Cursor，并把这个 Child Cursor 挂在对应的 Parent Cursor 下。

硬解析是非常不好的，它的危害性主要体现在如下这些方面：

硬解析可能会导致 Shared Pool Latch 的争用。无论是哪种类型的硬解析，都至少需要新生成一个 Child Cursor，并把目标 SQL 的解析树和执行计划载入该 Child Cursor 里，然后把这个 Child Cursor 存储在库缓存中。这意味着 Oracle 必须在 Shared Pool 中分配出一块内存区域用于存储上述 Child Cursor，而在 Shared Pool 中分配内存这个动作是要持有 Shared Pool Latch 的(Oracle 数据库中的 Latch 的作用之一就是保护共享内存的分配)，所以如果有一定数量的并发硬解析，可能会导致 Shared Pool Latch 争用，而且一旦发生大量的 Shared Pool Latch 争用，系统的性能和可扩展性会受到严重影响(常常表现为 CPU 的占用率居高不下，接近 100%)。

硬解析可能会导致库缓存相关 Latch(如 Library Cache    Latch)和 Mutex 的争用。无论是哪种类型的硬解析，都需要扫描相关的 Hash Bucket 中的库缓存对象句柄链表，而扫描库缓存对象句柄链表这个动作是要持有 Library Cache    Latch 的(Oracle 数据库中 Latch 的另外一个作用就是用于共享 SGA 内存结构的并发访问控制)，所以如果有一定数量的并发硬解析，则可能会导致 Library Cache    Latch 的争用。和 Shared Pool Latch 争用一样，一旦发生大量的 Library Cache    Latch 的争用，系统的性能和可扩展性也会受到严重影响。从 11gR1 开始，Oracle 用 Mutex 替换了库缓存相关 Latch，所以在 Oracle 11gR1 及其后续的版本中，将不再存在库缓存相关 Latch 的急用，取而代之的是 Mutex 的争用(可以简单的将 Mutex 理解成一种轻量级的 Latch，Mutex 主要也是用于共享 SGA 内存结果的并发访问控制)，Oracle 也因此引入了一系列新的等待事件来描述这种 Mutex 的争用，比如：Cursor: pin S、Cursor: pin X、Cursor: pin S wait    on X、Cursor:mutex S、Cursor:mutex X、Library    cache:mutex X 等。

另外需要注意的是，Oracle 在做硬解析时对 Shared Pool Latch 和 Library Cache Latch 的持有过程，大致如下：Oracle 首先持有 Library Cache Latch，在库缓存中扫描相关 Hash Bucket 中的库缓存对象句柄链表，以查看是否有匹配的 Parent Cursor，然后释放 Library Cache Latch(这里释放的原因是因为没有找到匹配的 parent Cursor)。接下来是硬解析的后半部分，首先持有 Library Cache Latch，然后在不释放 Library Cache Latch 的情况下持有 Shared Pool Latch，以便从 Shared Pool 中申请分配内存，成功申请后就会释放 Shared Pool Latch，最后再释放 Library Cache Latch。

对于 OLTP 类型的系统而言，硬解析是万恶之源。

1.3 软解析

软解析 (Soft Parse) 是指 Oracle 在执行目标 SQL 时，在 Library Cache 中找到了匹配的 Parent Cursor 和 Child Cursor，并将存储在 Child Cursor 中的解析树和执行计划直接拿过来重用，无须从头开始解析的过程。

和硬解析相比，软解析的优势主要表现在如下几个方面：

软解析不会导致 Shared Pool Latch 的争用。因为软解析能够在库缓存中找到匹配的 Parent Cursor 和 Child Cursor，所以它不需要生成新的 Parent Cursor 和 Child Cursor。这意味着软解析根本就不需要持有 Shared Pool Latch 以便在 Shared Pool 中申请分配一块共享内存区域，既然不需要持有 Shared Pool Latch，自然不会有 Shared Pool Latch 争用，即 Shared Pool Latch 的争用所带来的系统性能和可扩展性的问题对软解析来说并不存在。

软解析虽然也可能会导致库缓存相关 Latch(如 Library Cache Latch)和 Mutex 的争用，但软解析持有库缓存相关 Latch 的次数要少，而且软解析对某些 Latch(如 Library Cache Latch)持有的时间会比硬解析短，这意味着即使产生了库缓存相关 Latch 的争用，软解析的争用程度也没有硬解析那么严重，即库缓存相关 Latch 和 Mutex 的争用所带来的系统性能和可扩展性的问题对软解析来说要比硬解析少很多。

正是基于上述两个方面的原因，如果 OLTP 类型的系统在执行目标 SQL 时能够广泛使用软解析，则系统的性能和可扩展性就会比全部使用硬解析时有显著的提升，执行目标 SQL 时需要消耗的系统资源 (主要体现在 CPU 上) 也会显著降低。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-03/141360.htm