Oracle里的Cursor(一) ——shared cursor

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为“78bd3uh5a08av”，用这个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			   78bd3uh5a08av				       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			   78bd3uh5a08av				       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			   78bd3uh5a08av				       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='78bd3uh5a08av';

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，详细过程可以参考http://www.laoxiong.net/shared-pool-latch-and-library-cache-latch.html。

对于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上)也会显著降低。