游標是資料庫領域較為複雜的一個概念，因為游標包含了shared cursor和session cursor。兩者有其不同的概念，也有不同的表現形式。共用游標的概念易於與SQL語句中定義的游標相混淆。本文主要描述解析過程中的父游標，子游標以及共用游標，即shared cursor，同時給出了游標(se ...
游標是資料庫領域較為複雜的一個概念，因為游標包含了shared cursor和session cursor。兩者有其不同的概念，也有不同的表現形式。
共用游標的概念易於與SQL語句中定義的游標相混淆。本文主要描述解析過程中的父游標，子游標以及共用游標，即shared cursor，同時給出了
游標(session cursor)的生命周期以及游標的解析過程的描述。
有關游標的定義，聲明，與使用請參考：PL/SQL 游標
有關硬解析與軟解析請參考：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都將位於該視圖。http://hovertree.com/menu/oracle/
二、游標的生命周期(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: [email protected] */ /* 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值不同 **************/ /* 何問起 hovertree.com */ /***************不同的子游標有不同的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文本、執行環境完全一致的情形下，子游標能夠被共用，否則如果執行環境不一致則生成新的子游標
推薦：http://www.cnblogs.com/roucheng/p/3506033.html
