Oracle - 共享遊標、父子游標、硬軟解析
一、共享遊標的相關概念
1、Oracle Cursor 的說明
Oracle 裡的 Cursor 分為 Shared Cursor 和 Session Cursor 兩種
Shared Cursor 即共享遊標，快取在 Library Cache 裡的 SQL 和 匿名PL/SQL 等可執行物件
Session Cursor 即通過系統為使用者分配的用於存放 SQL語句 的執行結果的緩衝區
2、Shared Cursor 的說明
Shared Cursor 即 共享遊標，是指快取在 Library Cache（SGA下的Shared Pool中）裡的 SQL語句 和 PL/SQL塊，是快取在 Library Cache 中的幾十種 Library Cache Object（LCO）之一，它所屬的 Namespace 是 CRSR（即Cursor）
3、Shared Pool 的說明
Shared Pool 主要組成由 Library Cache 和 Data Dictionary cache 組成
Library Cache 主要用於儲存 SQL語句、語句解析樹、執行計劃、PL/SQL程式塊（包括匿名程式塊、儲存過程、包、函式等）、以及它們轉換後能夠被 Oracle 執行的程式碼等，這部分資訊可以通過 v$librarycache 檢視查詢
Data Dictionary Cache 主要用於存放資料字典資訊，包括表、檢視等物件的結構資訊，使用者以及物件許可權資訊；這部分資訊相對穩定，在 Shared Pool 中通過字典快取單獨存放，字典快取的內容是按行（Row）儲存的（其他資料通常按 Buffer 儲存），所以又被稱為 Row Cache，其資訊可以通過 v$rowcache 查詢
4、Library Cache 的說明
Library Cache 中的物件被稱為 Library Cache Object。Shared Cursor 是 Lco 表示的幾十種物件中的一種。Library Cache 是通過 Hash Table 的形式儲存的，Hash Table 經由一系列的 Hash Bucket 組成，單個 Hash Bucket 指向由 雜湊值 相同的所有 Lco Handle 組成的Object Handles連結串列。Lco 就是以 Lco handle 的結構經由 Hash Bucket 儲存在以 Hash Table 構成的 Library Cache 中。
Lco handle也就是庫快取物件控制程式碼，類似c語言的結構體，其中巢狀了一些子結構，儲存了Name、Namespace、Lock、Pin、Heap 0 Pointer等資訊
Name屬性表示的是 Lco handle 所對應的 Lco 的名稱(如果是SQL語句對應的庫快取物件控制程式碼，則屬性 Name就是該SQL語句的SQL文字；如果是表對應的庫快取物件控制程式碼，則屬性Name就是該表的表明)；
Namespace 表示的是Lco Handle對應的Lco的名稱空間，比如SQL語句和PL/SQL塊所對應的庫快取物件控制程式碼的namespace值都是CRSR即Cursor）
Heap 0 Pointer：表示的是指向子結構heap 0即Lco的指標；
Heap 0 即 Lco也是一種複雜的結構，儲存了Object Types、Object Name、Flags、Tables、Datablocks等資訊
Tables：記錄的是該Lco依賴的Lco的Handle的地址的集合；Table又細分為很多類，其中Child Table記錄的就是從屬於該Lco的子Lco的Handle的地址的集合
Data Block Pointer：該Lco中儲存的指向Data Heap的指標；Heap0 也僅僅儲存是一個結構，它不儲存實際的Data，實際的Data是儲存在Data Heap中的
Data Heap可以簡單理解為庫快取中的一塊連續的記憶體區域，而這些記憶體區域儲存著Cursor的動態執行時的資料，比如特別常見的執行計劃、SQL所涉及的物件定義、繫結變數型別和長度等
Heap 6：SQL Context：SQL的執行計劃就是存放Heap 6中
5、Parent/Child Cursor 的說明
Shared Cursor又細分為Parent Cursor（父遊標）和Child Cursor（子游標）兩種型別;
Parent Cursor只包含一些為了管理文字相同的遊標的管理性資料，如目標SQL的SQL文字及其相關的Hash值等; 檢視 v$sqlarea 中的每一行代表了一個 Parent Cursor；Parent Cursor 通過 SQL_ID 標識，Address 表示了其記憶體地址，VERSION_COUNT 表示子游標的數量，即當前是第幾個子游標
Child Cursor包含了這個遊標所有的相關資訊，如OBJECT和許可權，優化器設定、解析樹、執行計劃等。檢視v$sql中中 的每一行表示了一個child cursor，通過SQL_ID和CHILD_NUMBER標識，第一個child cursor總是使用CHILD_NUMBER=0來表示其建立順序；child cursor有自己的記憶體地址即CHILD_ADDRESS，可以根據hash value和address與parent cursor 關聯。
V$SQL_SHARED_CURSOR 標識了產生子游標的原因。
6、Hard/Soft Parse 的說明
硬解析（Hard Parse）是指 Oracle 在執行 目標SQL 時，在 Library Cache 中找不到可以重用的解析樹和執行計劃，而不得不從頭開始解析 目標SQL 並生成相應的 Parent Cursor 和 Child Cursor 的過程。當 SQL 語句第一次執行時，會進行硬解析。
軟解析（Soft Parse）是指 Oracle 在執行 目標SQL 時，在 Library Cache 中找到了匹配的 Parent Cursor 和 Child Cursor，並將儲存在 Child Cursor 中的解析樹和執行計劃直接拿過來重用，無須從頭開始解析的過程。
二、SQL解析的相關說明
1、Oracle 對 SQL 的處理過程
1）語法檢查（Syntax Check），檢查此 SQL 的拼寫是否符合語法
2）語義檢查（Semantic Check）， 檢查諸如 SQL 語句中的訪問物件是否存在及該使用者是否具備相應的許可權
3）對 SQL 語句進行解析（Prase），利用內部演算法對 SQL 進行解析，生成解析樹（Parse Tree）及執行計劃（Execution Plan）
4）執行SQL，並返回結果（Execute and Return）
2、Oracle 在解析 目標SQL 時在 Library Cache 中匹配的過程
1）首先基於對應 LCO Handle（庫快取物件控制程式碼）的屬性 Name 和 Namespace 的值做 Hash 運算，即對於 SQL 來說是基於屬性 Name 對應的該 SQL 的 SQL文字 和屬性 Namespace 對應的常量 CRSR 做 Hash 運算
2）根據得到的 Hash 值在Library Cache 的 Hash Table 中匹配是否有對應的 Hash Bucket
3）在匹配到 Hash Bucket 之後，在這個 Hash Bucket 對應的 LCO handles（庫快取物件控制程式碼連結串列）中尋找匹配的 LCO Handle（庫快取物件控制程式碼），在SQL的過程中 LCO Handle 即 Parent Cursor 的控制程式碼；這裡需要檢驗 SQL文字 是否一致，因為不同的 SQL文字 計算出來的雜湊值可能相同
4）如果沒有匹配到 LCO Handle，就意味著沒找到對應的Parent Cursor，更沒有對應的Child Cursor，也就意味著沒有可以共享的解析樹和執行計劃，需要從頭開始解析上述 目標SQL，新生成一個 Parent Cursor 和一個 Child Cursor，並掛在對應的 Hash Bucket 中，這就是硬解析
5）如果有匹配到 LCO Handle，也就找到了這個 Handle 所指向的 LOC，即找到了匹配的 Parent Cursor，遍歷這個 Parent Cursor 的 Child Tables 以查詢滿足重用的執行計劃和解析樹對應的 Child Cursor，這裡需要對比涉及的物件定義、繫結變數型別和長度、優化器引數等
6）如果有查詢到滿足條件的 Child Cursor，則直接重用該 Child Cursor 的執行計劃和解析樹等，不用再從頭開始解析 目標SQL，這就是軟解析
7）如果沒有沒找到滿足條件的 Child Cursor，就意味著沒有可以共享的解析樹和執行計劃，需要從頭開始解析上述 目標SQL，新生成一個 Child Cursor，並把這個 Child Cursor 掛在對應的 Parent Cursor 下，這也是硬解析
三、軟解析和硬解析的對比
當 Oracle 要執行 目標SQL 語句時，首選對該 SQL 的 SQL文字 做 Hash 運算，然後根據得到的 Hash 值去相關的 Hash Bucket 中掃描 LCO handles（庫快取物件控制程式碼連結串列），而掃描庫快取物件控制程式碼連結串列這個動作需要持有 Library Cache Latch；
如果在 Hash Bucket 中找到了匹配的 LCO handle，即找到了 Parent Cursor，且在其中找到了匹配的 Child Cursor，則可以直接使用其執行計劃、解析樹等物件，最後釋放 Library Cache Latch，這就是我們常說的軟解析;
如果沒有在 Hash Bucket 中找到對應的 LCO Handle，需要再次持有 Library Cache Latch，在此前提下再持有 Shared Pool Latch，然後從 Shared Pool 中申請分配記憶體,以便新生成一個 Child Cursor 或者 Parent Cursor 和 Child Cursor，成功申請後就會釋放 Shared Pool Latch，然後重新解析 SQL 並把相關的 SQL 執行計劃、解析樹等物件載入該 Child Cursor 裡，再以 LCO Handle 的方式儲存到相關的 Hash Bucket 中的 LCO Handles 連結串列中，最後再釋放 Library Cache Latch
軟解析不會導致 Shared Pool Latch 的爭用，因為軟解析能夠在庫快取中找到匹配的 Parent Cursor 和 Child Cursor；軟解析也有可能會導致庫快取相關的如 Library Cache Latch 和 Mutex 的爭用，但軟解析持有庫快取相關 Latch 的次數要少，且部分持有的時間會比硬解析短。在 Oracle 11g 後，Oracle 用 Mutex 替換了相關的 Library Cache Latch
四、父遊標和子游標的樣例
1、相同的SQL語句，在執行環境等相同的情況下，第一次被執行時要做硬解析，生成一個父遊標和一個子遊標，第二次執行時會共享第一次執行時生成的父遊標和子游標
[oracle@epay ~]$ sqlplus scott/tiger
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE EMPNO=7566;
-- 第一次執行目標sql
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7566 JONES MANAGER 2975
SQL> SELECT SQL_ID,HASH_VALUE,ADDRESS,SQL_TEXT,LOADS,EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE '%NO=7566%';
-- 檢視父遊標，此時載入過一次，執行過一次
SQL_ID HASH_VALUE ADDRESS SQL_TEXT LOADS EXECUTIONS
------------- ---------- ---------------- ------------------------------------------------------ -------- ----------
fnhr4u8drbu15 460711973 000000015C853DD8 SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE EMPNO=7566 1 1
SQL> SELECT SQL_ID,HASH_VALUE,CHILD_ADDRESS,CHILD_NUMBER,LOADS,EXECUTIONS FROM V$SQL WHERE SQL_ID='fnhr4u8drbu15';
-- 檢視子游標，此時只有一個子遊標，載入過一次，執行過一次
SQL_ID HASH_VALUE CHILD_ADDRESS CHILD_NUMBER LOADS EXECUTIONS
------------- ---------- ---------------- ------------ ---------- ----------
fnhr4u8drbu15 460711973 000000015C82C017 0 1 1
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE EMPNO=7566;
-- 第二次執行目標sql
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7566 JONES MANAGER 2975
SQL> SELECT SQL_ID,HASH_VALUE,ADDRESS,SQL_TEXT,LOADS,EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE '%EMPNO=7566%';
-- 檢視父遊標，此時載入過一次，執行過兩次
SQL_ID HASH_VALUE ADDRESS SQL_TEXT LOADS EXECUTIONS
------------- ---------- ---------------- ------------------------------------------------------------ ---------- ----------
fnhr4u8drbu15 460711973 000000015C853DD8 SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE EMPNO=7566 1 2
SQL> SELECT SQL_ID,HASH_VALUE,CHILD_ADDRESS,CHILD_NUMBER,LOADS,EXECUTIONS FROM V$SQL WHERE SQL_ID='fnhr4u8drbu15';
-- 檢視子游標，此時只有一個子遊標，載入過一次，執行過兩次
SQL_ID HASH_VALUE CHILD_ADDRESS CHILD_NUMBER LOADS EXECUTIONS
------------- ---------- ---------------- ------------ ---------- ----------
fnhr4u8drbu15 460711973 000000015C82A096 0 1 2fnhr4u8drbu15 460711973 000000015C82A096 0 1 2
2、不同的sql語句，在第一次執行時要分別做硬解析，分別生成對應的父遊標和子游標
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE EMPNO=7900;
-- 執行第一條SQL
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7900 JAMES CLERK 950
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE EMPNO=7788;
-- 執行第二天SQL
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7788 SCOTT ANALYST 3000
SQL> SELECT SQL_ID,HASH_VALUE,ADDRESS,SQL_TEXT,LOADS,EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE '%EMPNO=7788%' or SQL_TEXT LIKE '%EMPNO=7900%';
-- 檢視父遊標，每條SQL各載入了一次，執行了一次
SQL_ID HASH_VALUE ADDRESS SQL_TEXT LOADS EXECUTIONS
------------- ---------- ---------------- ------------------------------------------------------------ ---------- ----------
9vqs2frmta8mx 3885310589 000000015C5AF7D8 SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE EMPNO=7788 1 1
6cunv8390bfwc 3523591052 000000015DDB4440 SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE EMPNO=7900 1 1
SQL> SELECT SQL_ID,HASH_VALUE,CHILD_ADDRESS,CHILD_NUMBER,LOADS,EXECUTIONS FROM V$SQL WHERE SQL_ID='6cunv8390bfwc' or SQL_ID='9vqs2frmta8mx';
-- 檢視子游標，每條SQL對應一個子遊標，各執行了一次
SQL_ID HASH_VALUE CHILD_ADDRESS CHILD_NUMBER LOADS EXECUTIONS
------------- ---------- ---------------- ------------ ---------- ----------
9vqs2frmta8mx 3885310589 000000015C8410C0 0 1 1
6cunv8390bfwc 3523591052 000000015C8410B5 0 1 1
3、相同的sql語句，在不同的使用者(Schema)或者執行環境等情況下，共享父遊標，但生成新的子游標
[oracle@epay ~]$ sqlplus scott/tiger
-- 第一個使用者登入，該使用者下有一張emp表
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE EMPNO=7698; -- 使用scott使用者第一次執行
-- 使用scott使用者第一次執行SQ
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7698 BLAKE MANAGER 2850
SQL> SELECT SQL_ID,HASH_VALUE,ADDRESS,SQL_TEXT,LOADS,EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE '%EMPNO=7698%';
-- 檢視父遊標，此時SQL被載入和執行了一次
SQL_ID HASH_VALUE ADDRESS SQL_TEXT LOADS EXECUTIONS
------------- ---------- ---------------- ------------------------------------------------------------ ---------- ----------
da4hr63f5gpzm 3696744435 000000015C394778 SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE EMPNO=7698 1 1
SQL> SELECT SQL_ID,HASH_VALUE,CHILD_ADDRESS,CHILD_NUMBER,LOADS,EXECUTIONS FROM V$SQL WHERE SQL_ID='da4hr63f5gpzm';
-- 檢視子游標，此時只有一個子遊標
SQL_ID HASH_VALUE CHILD_ADDRESS CHILD_NUMBER LOADS EXECUTIONS
------------- ---------- ---------------- ------------ ---------- ----------
da4hr63f5gpzm 3696744435 000000015C84A016 0 1 1
[oracle@epay ~]$ sqlplus xl/xl
-- 切換使用者，該使用者下也有一張emp表
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE EMPNO=7698;
-- 使用xl使用者第二次執行SQL
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7698 BLAKE MANAGER 2850
SQL> alter session set optimizer_mode=first_rows;
-- 仍然使用xl使用者，但修改系統環境
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE EMPNO=7698;
-- 使用xl使用者第三次執行SQL
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7698 BLAKE MANAGER 2850
SQL> SELECT SQL_ID,HASH_VALUE,ADDRESS,SQL_TEXT,LOADS,EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE '%EMPNO=7698%';
-- 檢視父遊標，此時只有一個父遊標，但是被載入和執行了三次
SQL_ID HASH_VALUE ADDRESS SQL_TEXT LOADS EXECUTIONS
------------- ---------- ---------------- ------------------------------------------------------------ ---------- ----------
da4hr63f5gpzm 3696744435 000000015C394778 SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE EMPNO=7698 3 3
SQL> SELECT SQL_ID,HASH_VALUE,CHILD_ADDRESS,CHILD_NUMBER,LOADS,EXECUTIONS FROM V$SQL WHERE SQL_ID='da4hr63f5gpzm';
-- 檢視子游標，生成了三個子游標，分別用CHILD_NUMBER值為0和1和2標識
SQL_ID HASH_VALUE CHILD_ADDRES CHILD_NUMBER LOADS EXECUTIONS
------------- ---------- ---------------- ------------ ---------- ----------
da4hr63f5gpzm 3696744435 000000015C84A0F0 0 1 1
da4hr63f5gpzm 3696744435 000000015C84A930 1 1 1
da4hr63f5gpzm 3696744435 000000015C7B8240 2 1 1
SQL> SELECT SQL_ID,CHILD_NUMBER,AUTH_CHECK_MISMATCH,TRANSLATION_MISMATCH,OPTIMIZER_MODE_MISMATCH FROM V$SQL_SHARED_CURSOR WHERE SQL_ID='da4hr63f5gpzm';
-- 通過v$sql_shared_cursor檢視生成新子游標的原因
SQL_ID CHILD_NUMBER A T O
------------- ------------ - - -
da4hr63f5gpzm 0 N N N
da4hr63f5gpzm 1 Y Y N
da4hr63f5gpzm 2 N N Y
相關文章
- 父遊標 子游標和軟硬解析記載-02
- Oracle遊標共享，父遊標和子游標的概念Oracle
- 草稿 - 遊標，硬解析，軟解析 等
- Oracle中的遊標、硬解析、軟解析、軟軟解析、解析失敗Oracle
- Oracle遊標共享（Cursor Sharing）--常規遊標共享和自適應遊標共享（ACS）Oracle
- Oracle自適應共享遊標Oracle
- oracle實驗記錄 （子游標與解析）Oracle
- Oracle遊標Oracle
- Oracle 遊標Oracle
- 1224關於共享池4SQL記憶體結構父子游標補充SQL記憶體
- v$sql v$sqlarea和父遊標 子游標記載01SQL
- 深入理解父遊標，子游標的概念
- 1229關於共享池4-SQL記憶體結構父子游標補充3SQL記憶體
- 20150213關於共享池4-SQL記憶體結構父子游標SQL記憶體
- Oracle遊標示例Oracle
- Oracle遊標大全Oracle
- ORACLE 硬解析和軟解析 軟軟解析Oracle
- 遊標引數shared_cached_cursors和軟軟解析
- Oracle 硬解析與軟解析Oracle
- Oracle動態遊標Oracle
- Oracle自適應共享遊標——Adaptive Cursor Sharing（上）OracleAPT
- Oracle自適應共享遊標——Adaptive Cursor Sharing（下）OracleAPT
- input 獲取游標位置與設定游標位置
- v$sql v$sqlarea v$sql_shared_cursor及父遊標，子游標SQL
- 徹底弄懂oracle硬解析、軟解析、軟軟解析Oracle
- oracle cursor遊標迴圈比較遊標元素是否相同Oracle
- Oracle的硬解析和軟解析Oracle
- 【CURSOR】Oracle 子游標無法共享的原因之V$SQL_SHARED_CURSOROracleSQL
- [20160215]超長sql語句與父子游標.txtSQL
- oracle遊標使用全解Oracle
- Oracle 遊標使用全解Oracle
- [轉]oracle 遊標使用大全Oracle
- Oracle 遊標使用大全(轉)Oracle
- Oracle遊標使用大全(轉)Oracle
- 20151223關於共享池4x-SQL記憶體結構父子游標SQL記憶體
- ORACLE SQL解析之硬解析和軟解析OracleSQL
- Oracle SQL的硬解析和軟解析OracleSQL
- 游標操作快捷鍵