父游标、子游标及共享游标

客官°小女子只卖身不卖艺 2022-05-15 11:26 322阅读 0赞

游标是数据库领域较为复杂的一个概念，因为游标包含了shared cursor和session cursor。两者有其不同的概念，也有不同的表现形式。  
共享游标的概念易于与SQL语句中定义的游标相混淆。本文主要描述解析过程中的父游标，子游标以及共享游标，即shared cursor，同时给出了  
游标(session cursor)的生命周期以及游标的解析过程的描述。

有关游标的定义，声明，与使用请参考：[PL/SQL 游标  
][PL_SQL _] 有关硬解析与软解析请参考：[Oracle 硬解析与软解析][Oracle]

**一、相关定义**  
 shared cursor  
也即是共享游标，是SQL语句在游标解析阶段生成获得的，是位于library cache中的sql或匿名的pl/sql等。其元数据被在视图V$sqlarea  
与v$sql中具体化。如果library cache中的父游标与子游标能够被共享，此时则为共享游标。父游标能够共享即为共享的父游标，子游  
标能够共享极为共享的子游标。  
  
session cursor  
即通过系统为用户分配缓冲区用于存放SQL语句的执行结果。用户可以通过这个中间缓冲区逐条取出游标中的记录并对其处理，直到所  
有的游标记录被逐一处理完毕。session cursor指的跟这个session相对应的server process的PGA里(准确的说是UGA)的一块内存区域  
(或者说内存结构)即其主要特性表现在记录的逐条定位，逐条处理。session cursor的元数据通过v$open\_cursor视图来具体化。每一  
个打开或解析的SQL都将位于该视图。

**二、游标的生命周期(session cursor)**  
 session cursor需要从UGA中分配内存，因此有其生命周期。其生命周期主要包括：  
打开游标(根据游标声明的名称在UGA中分配内存区域)  
解析游标(将SQL语句与游标关联，并将其执行计划加载到Library Cache)  
定义输出变量(仅当游标返回数据时)  
绑定输入变量(如果与游标关联的SQL语句使用了绑定变量)  
执行游标(即执行SQL语句)  
获取游标(即获取SQL语句记录结果,根据需要对记录作相应操作。游标将逐条取出查询的记录，直到取完所有记录)  
关闭游标(释放UGA中该游标占有的相关资源，但Library Cache中的游标的执行计划按LRU原则清除，为其游标共享提供可能性)

对于session cursor而言，可以将游标理解为任意的DML，DQL语句(个人理解，有待核实)。即一条SQL语句实际上就是一个游标，只不过  
session cursor分为显示游标和隐式游标，以及游标指针。由上面游标的生命周期可知，任何的游标(SQL语句)都必须经历内存分配，解析，  
执行与关闭的过程。故对隐式游标而言，生命周期的所有过程由系统来自动完成。对所有的DML和单行查询(select ... into ...)而言，  
系统自动使用隐式游标。多行结果集的DQL则通常使用显示游标。

**二、游标的解析过程(产生shared cursor)**  
 解析过程:

A、包含vpd的约束条件：  
SQL语句如果使用的表使用了行级安全控制，安全策略生成的约束条件添加到where子句中  
  
B、语法、语义、访问权限检查：  
检查SQL语句书写的正确性，对象存在性，用户的访问权限  
  
C、父游标缓存:  
将该游标(SQL语句)的文本进行哈希得到哈希值并在library cache寻找相同的哈希值，如不存在则生存父游标且保存在library cache  
中，按顺序完成D-F步骤。如果此时存在父游标，则进一步判断是否存在子游标。若存在相同的子游标，则直接调用其子游标的执行计  
划执行该SQL语句，否则转到步骤D进行逻辑优化  
  
D、逻辑优化：  
使用不同的转换技巧，生成语义上等同的新的SQL语句(SQL语句的改写)，一旦该操作完成，则执行计划数量、搜索空间将会相应增长。  
其主要目的未进行转换的情况下是寻找无法被考虑到的执行计划  
  
E、物理优化：  
为逻辑优化阶段的SQL语句产生执行计划，读取数据字典中的统计信息以及动态采样的统计信息，计算开销，开销最低的执行计划将被  
选中。  
  
F、子游标缓存：  
分配内存，生成子游标(即最佳执行计划)，与父游标关联。可以在v$sqlarea, v$sql得到具体游标信息，父子游标通过sql\_id关联  
  
对于仅仅完成步骤A与B的SQL语句即为软解析，否则即为硬解析

**三、shared cursor与session cursor的关系以及软软解析**  
 关系：  
一个session cursor只能对应一个shared cursor，而一个shared cursor却可能同时对应多个session cursor

**四、父游标与子游标、共享游标**  
 由游标的解析过程可知，父游标，子游标同属于共享游标的范畴。  
父游标  
是在进行硬解析时产生的，父游标里主要包含两种信息：SQL文本以及优化目标(optimizer goal)，首次打开父游标被锁定，直到其他  
所有的session都关闭该游标后才被解锁。当父游标被锁定的时候是不能被LRU算法置换出library cache，只有在解锁以后才能置换出  
library cache，此时该父游标对应的所有子游标也同样被置换出library cache。v$sqlarea中的每一行代表了一个parent cursor，  
address表示其内存地址。  
  
子游标  
当发生硬解析时，在产生父游标的同时，则跟随父游标会产生相应的子游标，此时V$SQL.CHILD\_NUMBER的值为0。  
如果存在父游标，由于不同的运行环境，此时同样会产生新的子游标，新子游标的CHILD\_NUMBER在已有子游标基础上以1为单位累计。  
子游标包括游标所有相关信息，如具体的执行计划、绑定变量，OBJECT和权限，优化器设置等。子游标随时可以被LRU算法置换出  
library cache，当子游标被置换出library cache时，oracle可以利用父游标的信息重新构建出一个子游标来，这个过程叫reload。  
v$sql中中 的每一行表示了一个child cursor，根据hash value和address与parent cursor 关联。  
child cursor有自己的address，即v$sql.child\_address。

确定一个游标的三个主要字段：address,hash\_value,child\_number,

**五、演示父游标、子游标**

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 首先创建表 t \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ SQL> create table t as select empno,ename,sal from emp where deptno=10; Table created. \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 对表进行查询 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ SQL> select \* from t where empno=7782; EMPNO ENAME SAL ---------- ---------- ---------- 7782 CLARK 2450 SQL> SELECT \* from t where empno=7782; EMPNO ENAME SAL ---------- ---------- ---------- 7782 CLARK 2450 SQL> SELECT \* FROM t WHERE empno=7782; EMPNO ENAME SAL ---------- ---------- ---------- 7782 CLARK 2450 SQL> select \* from t where empno=7782; EMPNO ENAME SAL ---------- ---------- ---------- 7782 CLARK 2450 /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*由下面的查询(v$sqlarea)可知产生了3个父游标，其中一个父游标(2r6rbdp92kyh9)执行了2次 \*\*\*\*\*\*\*\*\*\*\*\*/ /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ /\* Author: Robinson Cheng \*/ /\* Blog: http://blog.csdn.net/robinson\_0612 \*/ /\* MSN: robinson\_0612@hotmail.com \*/ /\* QQ: 645746311 \*/ /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ SQL> col sql\_text format a40 SQL> select sql\_id,sql\_text,executions from v$sqlarea 2 where sql\_text like '%empno=7782%' and sql\_text not like '%from v$sqlarea%'; SQL\_ID SQL\_TEXT EXECUTIONS ------------- ---------------------------------------- ---------- 4rs2136z084y1 SELECT \* from t where empno=7782 1 84w067b4n91h5 SELECT \* FROM t WHERE empno=7782 1 2r6rbdp92kyh9 select \* from t where empno=7782 2 /\*\*\*\*\*\*\*\*\*\*\*\*上面3个父游标对应的子游标可以在v$sql中获得 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ SQL> select sql\_id,hash\_value,child\_number,plan\_hash\_value,sql\_text,executions from v$sql 2 where sql\_text like '%empno=7782%' and sql\_text not like '%from v$sql%'; SQL\_ID HASH\_VALUE CHILD\_NUMBER PLAN\_HASH\_VALUE SQL\_TEXT EXECUTIONS ------------- ---------- ------------ --------------- ---------------------------------------- ---------- 4rs2136z084y1 3187938241 0 1601196873 SELECT \* from t where empno=7782 1 84w067b4n91h5 3376711173 0 1601196873 SELECT \* FROM t WHERE empno=7782 1 2r6rbdp92kyh9 1378449929 0 1601196873 select \* from t where empno=7782 2 /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*调整optimizer\_index\_caching 参数并执行聚合查询 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ SQL> alter session set optimizer\_index\_caching=40; Session altered. SQL> select sum(sal) from t; SUM(SAL) ---------- 8750 SQL> alter session set optimizer\_index\_caching=100; Session altered. SQL> select sum(sal) from t; SUM(SAL) ---------- 8750 /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*相同的查询由于不同的运行环境导致产生了不同的子游标，optimizer\_env\_hash\_value值不同 \*\*\*\*\*\*\*\*\*\*\*\*\*\*/ /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*不同的子游标有不同的child\_address 值 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ SQL> select sql\_id, child\_number, sql\_text,optimizer\_env\_hash\_value oehv,child\_address 2 from v$sql where sql\_text like '%sum(sal)%' and sql\_text not like '%from v$sql%'; SQL\_ID CHILD\_NUMBER SQL\_TEXT OEHV CHILD\_ADDRESS ------------- ------------ ---------------------------------------- ---------- ---------------- gu68ka2qzx3hh 0 select sum(sal) from t 3620536549 0000000093696D00 gu68ka2qzx3hh 1 select sum(sal) from t 2687219005 0000000093767F58 /\*\*\*\*\*\*\*\*\*\* 查询v$sql\_shared\_cursor可以跟踪是那些变化导致了子游标不能共享,此例为optimizer\_mismatch \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ SQL> SELECT child\_number, optimizer\_mismatch 2 FROM v$sql\_shared\_cursor 3 WHERE sql\_id = '&sql\_id'; Enter value for sql\_id: gu68ka2qzx3hh old 3: WHERE sql\_id = '&sql\_id' new 3: WHERE sql\_id = 'gu68ka2qzx3hh' CHILD\_NUMBER O ------------ - 0 N 1 Y /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*观察父游标address,hash\_value,sql\_id \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*观察子游标address,hash\_value,child\_number,sql\_id,child\_address \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*从Oracle 10g 之后，sql\_id既可以唯一确定一个父游标，sql\_id,child\_number唯一确定一个子游标\*\*\*\*\*/ SQL> SELECT address,hash\_value,sql\_id FROM v$sqlarea WHERE sql\_id='gu68ka2qzx3hh'; ADDRESS HASH\_VALUE SQL\_ID ---------------- ---------- ------------- 000000009F8CBB58 2919140880 gu68ka2qzx3hh SQL> SELECT address,hash\_value,child\_number, sql\_id,child\_address 2 FROM v$sql WHERE sql\_id='gu68ka2qzx3hh'; ADDRESS HASH\_VALUE CHILD\_NUMBER SQL\_ID CHILD\_ADDRESS ---------------- ---------- ------------ ------------- ---------------- 000000009F8CBB58 2919140880 0 gu68ka2qzx3hh 0000000093696D00 000000009F8CBB58 2919140880 1 gu68ka2qzx3hh 0000000093767F58  
**六、总结**  
  1、硬解析通常是由于不可共享的父游标造成的，如经常变动的SQL语句，或动态SQL或未使用绑定变量等  
 2、解决硬解析的办法则通常是使用绑定变量来解决  
 3、与父游标SQL文本完全一致的情形下，多个相同的SQL语句可以共享一个父游标  
 4、SQL文本、执行环境完全一致的情形下，子游标能够被共享，否则如果执行环境不一致则生成新的子游标

[PL_SQL _]: http://blog.csdn.net/robinson_0612/archive/2010/12/15/6078622.aspx
[Oracle]: http://blog.csdn.net/robinson_0612/archive/2011/02/19/6195483.aspx