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的解析樹和執行計劃。