5.3　游标示例

下面我们来看一个关于游标的示例。

以SCOTT用户身份登录数据库：

---

conn scott/xxx
select empno,ename from emp;
//当一条SQL第一次被执行的时候，Oracle会同时产生一个Parent Cursor和一个Child Cursor

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在V$SQLAREA中只有一条匹配记录，且这条记录的VERSION_COUNT的值为1(VERSION_COUNT表示某个Parent Cursor所拥有的所有Child Cursor的数量)。这说明了Oracle在执行这条SQL时确实只产生了一个Parent Cursor和一个Child Cursor
*/

select plan_hash_value,child_number 
from v$sql 
where sql_id='78bd3uh4a08av';
PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
     3956160932            0
/*
从V$SQL中查看所有Child Cursor的信息。根据SQL_ID查询V$SQL只有一条匹配记录，而且这条记录的CHILD_NUMBER的值为0(CHILD_NUMBER表示某个Child Cursor所对应的子游标号)，说明Oracle在执行原目标SQL时确实只产生了一个编号为0的Child Cursor
*/

//以HF用户身份登录数据库
conn hf/hf
create table emp as select * from scott.emp;

select empno,ename from emp;
//注意此时执行的SQL语句虽然与前面的相同，但其实是两个完全不同的语句

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
/*
在V$SQLAREA中发现匹配记录的VERSION_COUNTW为2，说明这个SQL语句有一个Parent Cursor和两个Child Cursor
*/

select plan_hash_value,child_number from v$sql where sql_id='78bd3uh4a08av';
PLAN_HASH_VALUE CHILD_NUMBER
--------------- ------------
     3956160932            0
     3956160932            1
//查看V$SQL，可以看到CHILD_NUMBER的值分别为0和1的两个Child Cursor

---

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