> Oracle裡的cursor分為兩種：一種是shared cursor，一種是session cursor。 本測試主要說明shared cursour

shared cursor就是快取在shared pool 裡的一種 library cache object。 主要快取sql和匿名的pl/sql。它們是Oracle快取在library cache中的幾十種library cache之一，它所屬的namespace是 cursor（Oracle透過namespace來管理schema object的名字）。

Shared pool 主要組成由Library cache 和 Data Dictionary cache：
- Library Cache主要用於儲存SQL語句、SQL語句相關的解析樹、執行計劃、PL/SQL程式塊（包括匿名程式塊、儲存過程、包、函式等）以及它們轉換後能夠被Oracle執行的程式碼等，這部分資訊可以透過v$librarycache檢視查詢；
- Data Dictionary Cache主要用於存放資料字典資訊，包括表、檢視等物件的結構資訊，使用者以及物件許可權資訊，這部分資訊相對穩定，在Shared Pool中透過字典快取單獨存放，字典快取的內容是按行（Row）儲存的（其他資料通常按Buffer儲存），所以又被稱為Row Cache，其資訊可以透過v$rowcache查詢。

shared SQL, parent cursor和child cursor 概念：
SQL都是implicitly shareable。 當使用者發出一條SQL後，Oracle會根據SQL文字內容生成hash value，以便能夠快速找到 Shared pool已經存在的相同SQL。如果找不到，則Oracle會為這個SQL建立一個parent cursor和一個child cursor，這與SQL是否共享是沒有關係的。

-   parent cursor包含了SQL TEXT和相關的hash value，v$sqlarea中的每一行代表了一個parent cursor，根據address表示了其記憶體地址。
-  child cursor包含了SQL的metadata，即使得這個SQL可以執行的所有相關資訊，如OBJECT和許可權，最佳化器設定，執行計劃等。v$sql中中 的每一行表示了一個child cursor，根據hash value和address與parent cursor 關聯。child cursor有自己的address，即V$SQL.CHILD_ADDRESS。

第一個child cursor總是使用0來表示其建立順序，V$SQL.CHILD_NUMBER = 0。因此，當從V$SQL_PLAN中查詢某個SQL的執行計劃時，要注意你寫對了CHILD_NUMBER。

如果有多個child cursor，則表示parent cursor有多個版本，v$sqlarea中的version_count欄位就會紀錄下來。

我們透過下面進行測試：
``` sql
--不使用繫結變數，執行以下幾個sql，之後的結果
SQL> col sql_text for a60
SQL> set lines 200
SQL> select sql_text, hash_value,address from v$sqlarea where sql_text like 'select count(*) from emp%';

SQL_TEXT						     HASH_VALUE ADDRESS
------------------------------------------------------------ ---------- ----------------
select count(*) from emp where empno=7499		     3945401509 000000009F88D7A8
select count(*) from emp where empno=7989		     3561150173 000000009F3D1CE0

SQL>

6 rows selected.
```

透過上述結果可以看到，如果不適用繫結變數，沒執行一次語句（條件值不同），都會生成父遊標和子游標，這就是硬解析。

透過使用繫結變數方式：
``` sql
--制定7499,7989
var x number;
exec :x := 7989;
select count(*) from emp2 where empno=:x;

SQL> select sql_text, hash_value,address from v$sqlarea where sql_text like 'select count(*) from emp%';

SQL_TEXT						     HASH_VALUE ADDRESS
------------------------------------------------------------ ---------- ----------------
select count(*) from emp where empno=7499		     3945401509 000000009F88D7A8
select count(*) from emp where empno=7989		     3561150173 000000009F3D1CE0
select count(*) from emp2 where empno=:x		     3447257031 000000007CAFCFA0

--檢視子游標，一個
SQL> select sql_id,address,CHILD_ADDRESS,CHILD_NUMBER from v$sql_shared_cursor where address='000000007CAFCFA0';

SQL_ID	      ADDRESS	       CHILD_ADDRESS	CHILD_NUMBER
------------- ---------------- ---------------- ------------
cv0ar6b6rjxy7 000000007CAFCFA0 00000000A97BB8B0 	   0

--加大數值，執行後再次檢視
SQL> var x number;
exec :x := 798900;
select count(*) from emp2 where empno=:x; SQL> 
PL/SQL procedure successfully completed.

SQL>

COUNT(*)
----------
	 0

SQL> select sql_id,address,CHILD_ADDRESS,CHILD_NUMBER from v$sql_shared_cursor where address='000000007CAFCFA0';

SQL_ID	      ADDRESS	       CHILD_ADDRESS	CHILD_NUMBER
------------- ---------------- ---------------- ------------
cv0ar6b6rjxy7 000000007CAFCFA0 00000000A97BB8B0 	   0
cv0ar6b6rjxy7 000000007CAFCFA0 000000007CF93560 	   1

--檢視不共享原因，欄位中 是Y的對應相關列進行檢查。 如下所示，本例 是BIND_EQUIV_FAILURE （ 繫結值的選擇性與用於最佳化現有子游標的選擇性不匹配）導致
SQL> select * from v$sql_shared_cursor where address='000000007CAFCFA0';

SQL_ID	      ADDRESS	       CHILD_ADDRESS	CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
REASON
--------------------------------------------------------------------------------
cv0ar6b6rjxy7 000000007CAFCFA0 00000000A97BB8B0 	   0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N
<childnode><childnumber>0</childnumber><id>40</id><reason>Bind mismatch(25)
                        </reason></childnode>

【CURSOR】Oracle繫結變數、執行計劃對遊標的影響

相關文章