Oracle中的遊標、硬解析、軟解析、軟軟解析、解析失敗
Oracle中的遊標、硬解析、軟解析、軟軟解析、解析失敗
一、 遊標的分類及共享遊標
遊標（Cursor）是Oracle資料庫中SQL解析和執行的載體，它可以分為共享遊標（Shared Cursor）和會話遊標（Session Cursor）。共享遊標可以細分為父遊標（Parent Cursor）和子游標（Child Cursor），可以透過檢視V$SQLAREA和V$SQL來檢視當前快取在庫快取（Library Cache）中的父遊標和子游標，其中V$SQLAREA用於檢視父遊標，V$SQL用於檢視子游標。父遊標和子游標的結構是一樣的，它們都是以庫快取物件控制程式碼的方式快取在庫快取中，Namespace屬性的值均為CRSR。由於子游標對應的庫快取物件控制程式碼的Name屬性值為空，所以，只能透過父遊標才能找到相應的子游標。綜上所述，任意一個經過解析的目標SQL一定會同時對應兩個共享遊標（Shared Cursor），一個是父遊標（Parent Cursor），另一個則是子游標（Child Cursor），父遊標會儲存該SQL的SQL文字，而該SQL真正的可以被重用的解析樹和執行計劃則儲存在子游標中。
Oracle中游標的分類如下所示：
圖 3-13 Oracle中的遊標分類
父遊標和子游標的對比如下表所示：
二、 會話遊標
會話遊標（Session Cursor）是當前會話（Session）解析和執行SQL的載體，即會話遊標用於在當前會話（Session）中解析和執行SQL，會話遊標快取在PGA中（Shared Cursor是快取在SGA的庫快取裡）。會話遊標是以雜湊表的方式快取在PGA中，在快取會話遊標的雜湊表的對應Hash Bucket中，Oracle會儲存目標SQL對應的父遊標的庫快取物件控制程式碼地址，所以，Oracle可以透過會話遊標找到對應的父遊標，進而就可以找到對應子游標中目標SQL的解析樹和執行計劃，然後Oracle就可以按照這個解析樹和執行計劃來執行目標SQL了。
會話遊標在目標SQL的執行過程中實際上起到了一個承上啟下的作用，Oracle依靠會話遊標來將目標SQL所涉及的資料從Buffer Cache的對應資料塊讀到PGA裡，然後在PGA裡做後續的處理（如排序、表連線等），最後將最終的處理結果返回給使用者，所以，會話遊標是當前會話解析和執行SQL的載體的原因。
關於會話遊標，需要注意以下幾點：
① 會話遊標（Session Cursor）與會話（Session）是一一對應的，不同會話的會話遊標之間不能共享，這是與共享遊標（Shared Cursor）的本質區別。
② 會話遊標是有生命週期的，每個會話遊標在使用的過程中都至少會經歷一次Open、Parse、Bind、Execute、Fetch和Close中的一個或多個階段，用過的會話遊標不一定會快取在對應會話的PGA中，這取決於引數SESSION_CACHED_CURSORS的值是否大於0。
共享遊標和會話遊標的對比如下表所示：
（二）會話遊標的分類
會話遊標的詳細分類參考下表：
表 3-20 Oracle中會話遊標的分類
在上表中特別說明一下動態遊標，動態遊標是Oracle資料庫中最靈活的一種會話遊標，它的靈活性表現在：①動態遊標的定義方式非常靈活，它可以有多種定義方式。②參考遊標可以作為儲存過程的輸入引數和函式的輸出引數。上表中的各種遊標希望讀者可以透過做大量的練習題來掌握，畢竟遊標是儲存過程開發過程中必不可少的內容。
（三）會話遊標的屬性
會話遊標有4個屬性，見下表：
表 3-21 遊標的屬性
當執行一條DML語句後，DML語句的結果儲存在這四個遊標屬性中，這些屬性用於控制程式流程或者瞭解程式的狀態。當執行DML語句時，PL/SQL開啟一個內建遊標並處理結果。在這些屬性中，SQL%FOUND和SQL%NOTFOUND是布林值，SQL%ROWCOUNT是整數值。需要注意的是，若遊標屬於隱式遊標，則在PL/SQL中可以直接使用上表中的屬性，若遊標屬於顯式遊標，則上表中的屬性裡“SQL%”需要替換為自定義顯式遊標的名稱。上表中的這4個屬性對於動態遊標依然適用。
（四）會話遊標的相關引數
和會話遊標相關的有2個重要引數，分別為OPEN_CURSORS和SESSION_CACHED_CURSORS，下面詳細介紹這兩個引數。
（1）引數OPEN_CURSORS用於設定單個會話中同時能夠以OPEN狀態並存的會話遊標的總數，預設值為50。若該值為300，則表示單個會話中同時能夠以OPEN狀態並存的會話遊標的總數不能超過300，否則Oracle會報錯“ORA-1000：maximum open cursors exceeded”。檢視V$OPEN_CURSOR可以用來查詢資料庫中狀態為OPEN或者己經被快取在PGA中的會話遊標的數量和具體資訊（例如，SQL_ID和SQL文字等）。當然，也可以從檢視V$SYSSTAT中查到當前所有以OPEN狀態存在的會話遊標的總數。
LHR@orclasm > show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 65535
SELECT USERENV('SID') FROM DUAL;
SELECT * FROM V$OPEN_CURSOR WHERE SID=16;
SELECT * FROM V$SYSSTAT D WHERE D.NAME ='opened cursors current';
（2）引數SESSION_CACHED_CURSORS用於設定單個會話中能夠以Soft Closed狀態並存的會話遊標的總數，即用於設定單個會話能夠快取在PGA中的會話遊標的總數。在Oracle 10g中預設為20（注意：在官方文件中該值預設為0，其實是20），11g中預設為50。
LHR@orclasm > show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50
從上述顯示結果可以看出，SESSION_CACHED_CURSORS的值為50，意味著在這個庫裡，單個會話中同時能夠以Soft Closed狀態快取在PGA中的會話遊標的總數不能超過50。
Oracle會用LRU演算法來管理這些已快取的會話遊標（從會話遊標的dump檔案中可以證實這一點），所以即便某個Session以Soft Closed狀態快取在PGA中的會話遊標的總數己經達到了SESSION_CACHED_CURSORS所設定的上限也沒有關係，LRU演算法依然能夠保證那些頻繁反覆執行的SQL所對應的會話遊標的快取命中率要高於那些不頻繁反覆執行的SQL。
這裡需要注意的是，在Oracle 11gR2中，一個會話遊標能夠被快取在PGA中的必要條件是該會話遊標所對應的SQL解析和執行的次數要超過3次。Oracle這麼做的目的是為了避免那些執行次數很少的SQL所對應的會話遊標也被快取在PGA裡，這些SQL很可能只執行一次而且不會重複執行，所以把這些執行次數很少的SQL所對應的會話遊標快取在PGA中是沒有太大意義的。
LHR@orclasm > alter system flush shared_pool;--生產庫慎用
System altered.
--開始第1次執行
LHR@orclasm > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
no rows selected
LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
COUNT(*)
----------
14
LHR@orclasm > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
no rows selected
開始第2次執行：
LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
COUNT(*)
----------
14
LHR@orclasm > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
no rows selected
開始第3次執行：
LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
COUNT(*)
----------
14
LHR@orclasm > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
SQL_ID CURSOR_TYPE
------------- ----------------------------------------------------------------
9r01dt51f46tf DICTIONARY LOOKUP CURSOR CACHED
從結果可以看到，雖然已經快取到PGA中了，但是型別為“DICTIONARY LOOKUP CURSOR CACHED”，並不是“SESSION CURSOR CACHED”，所以下面開始第4次執行：
LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
COUNT(*)
----------
14
LHR@orclasm > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
SQL_ID CURSOR_TYPE
------------- ----------------------------------------------------------------
9r01dt51f46tf SESSION CURSOR CACHED
LHR@orclasm > SELECT a.VERSION_COUNT,a.EXECUTIONS,a.PARSE_CALLS,a.LOADS FROM v$sqlarea a WHERE a.SQL_ID='9r01dt51f46tf';
VERSION_COUNT EXECUTIONS PARSE_CALLS LOADS
------------- ---------- ----------- ----------
1 4 3 1
從結果可以看到，在SQL語句“SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;”第4次執行完畢後，Oracle已經將其對應的會話遊標快取在當前會話的PGA中了，而此時快取的會話遊標的型別為“SESSION CURSOR CACHED”。下面開始第5次執行：
LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
COUNT(*)
----------
14
LHR@orclasm > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
SQL_ID CURSOR_TYPE
------------- ----------------------------------------------------------------
9r01dt51f46tf SESSION CURSOR CACHED
LHR@orclasm > SELECT a.VERSION_COUNT,a.EXECUTIONS,a.PARSE_CALLS,a.LOADS FROM v$sqlarea a WHERE a.SQL_ID='9r01dt51f46tf';
VERSION_COUNT EXECUTIONS PARSE_CALLS LOADS
------------- ---------- ----------- ----------
1 5 3 1
從結果看出，快取的會話遊標的型別依然為“SESSION CURSOR CACHED”，不再改變。
（五）會話遊標的dump檔案
會話遊標的dump檔案可以透過Level值為3的errorstack得到，獲取過程如下所示：
SELECT COUNT(*) FROM SCOTT.EMP;--執行5次，讓其快取在PGA中
SELECT COUNT(*) FROM SCOTT.EMP;
SELECT COUNT(*) FROM SCOTT.EMP;
SELECT COUNT(*) FROM SCOTT.EMP;
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ERRORSTACK LEVEL 3';
SELECT COUNT(*) FROM SCOTT.EMP;
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ERRORSTACK OFF';
SELECT VALUE FROM V$DIAG_INFO;
三、 SQL的解析過程（硬解析、軟解析、軟軟解析）
在Oracle中，每條SQL語句在執行之前都需要經過解析（Parse）。DDL語句是從來不會共享使用的，也就是說DDL語句每次執行都需要進行硬解析。但是，DML語句和SELECT語句會根據情況選擇是進行硬解析，還是進行軟解析或者進行軟軟解析。
Oracle對SQL的處理過程如下所示：
（1）語法檢查（Syntax Check）：檢查此SQL的拼寫是否正確。
（2）語義檢查（Semantic Check）：例如檢查SQL語句中的訪問物件是否存在及該使用者是否具備相應的許可權。
（3）對SQL語句進行解析（Parse）：利用內部演算法對SQL進行解析，生成解析樹（Parse Tree）及執行計劃（Execution Plan）。
（4）執行SQL，返回結果。
解析主要分為3種型別：硬解析（Hard Parse）、軟解析（Soft Parse）和軟軟解析（Soft Soft Parse，也叫快速解析（Fast Parse）），它們的解析過程可以參考下圖：
Oracle在解析和執行目標SQL時，會先去當前會話的PGA中查詢是否存在匹配的快取Session Cursor。當Oracle第一次解析和執行目標SQL時（顯然是硬解析），當前Session的PGA中肯定不存在匹配的Session Cursor，這時Oracle會新生成一個Session Cursor和一對Shared Cursor（即Parent Cursor和Child Cursor），這其中的Shared Cursor會儲存能被所有會話共享、重用的內容（比如目標SQL的解析樹、執行計劃等），而Session Cursor則會經歷一次Open、Parse、Bind、Execute、Fetch和Close中的一個或多個階段。
對會話遊標（Session Cursor）和共享遊標（Shared Cursor）之間的關聯關係如下總結：
l 無論是硬解析、軟解析還是軟軟解析，Oracle在解析和執行目標SQL時，始終會先去當前會話（Session）的PGA中尋找是否存在匹配的快取會話遊標。
l 如果在當前會話的PGA中找不到匹配的快取會話遊標，那麼Oracle就會去SGA的庫快取（Library Cache）中查找是否存在匹配的父遊標。如果在庫快取中找不到匹配的父遊標，那麼Oracle就會新生成一個會話遊標和一對共享遊標（即父遊標和子游標)；如果找到了匹配的父遊標，但找不到匹配的子游標，那麼Oracle就會新生成一個會話遊標和一個子游標（這個子游標會被掛在之前找到的匹配父遊標下）。無論哪一種情況，這兩個過程對應的都是硬解析。
l 如果在當前會話的PGA中找不到匹配的快取會話遊標，但在庫快取中找到了匹配的父遊標和子游標，那麼Oracle會新生成一個會話遊標並重用剛剛找到的匹配父遊標和子游標，這個過程對應的就是軟解析。
l 如果在當前會話的PGA中找到了匹配的快取會話遊標，那麼此時Oracle就不再需要新生成一個會話遊標，並且也不再需要像軟解析那樣得去SGA的庫快取中查詢匹配的父遊標了，因為Oracle此時可以重用找到的匹配會話遊標，並且可以透過這個會話遊標直接訪問到該SQL對應的父遊標，這個過程就是軟軟解析。
下面詳解介紹硬解析（Hard Parse）、軟解析（Soft Parse）和軟軟解析（Soft Soft Parse）：
（一）硬解析（Hard Parse）
硬解析（Hard Parse）是指Oracle在執行目標SQL時，在庫快取（Library Cache）中找不到可以重用的解析樹和執行計劃，而不得不從頭開始解析目標SQL並生成相應的父遊標（Parent Cursor）和子游標（Child Cursor）的過程。
硬解析實際上有兩種型別：一種是在庫快取中找不到匹配的父遊標（Parent Cursor），此時Oracle會從頭開始解析目標SQL，新生成一個父遊標和一個子遊標，並把它們掛在對應的HashBucket中；另外一種是找到了匹配的父遊標但未找到匹配的子游標，此時Oracle也會從頭開始解析該目標SQL，新生成一個子游標，並把這個子游標掛在對應的父遊標下。
硬解析大致可以分為5個執行步驟：
（1）語法分析。
（2）許可權與物件檢查。
（3）在共享池中檢查是否有完全相同的之前完全解析好的。如果存在，則直接跳過步驟（4）和步驟（5），執行SQL，此時算SOFT PARSE。
（4）選擇執行計劃。
（5）產生執行計劃。
需要注意的是，建立解析樹、生成執行計劃對於SQL的執行來說是開銷昂貴的動作，所以，應當極力避免硬解析，儘量使用軟解析。這就是在很多專案中，倡導開發設計人員對功能相同的程式碼要努力保持程式碼的一致性，以及要在程式中多使用繫結變數的原因。
在硬解析時，需要申請閂的使用，而閂的數量在有限的情況下需要等待。大量的閂的使用由此造成需要使用閂的程式排隊越頻繁，效能則逾低下。具體來說，硬解析的危害性體現在以下幾點上：
① 硬解析可能會導致Shared Pool Latch的爭用。無論是哪種型別的硬解析，都至少需要新生成一個Child Cursor，並把目標SQL的解析樹和執行計劃載入該Child Cursor裡，然後把這個Child Cursor儲存在庫快取中。這意味著Oracle必須在Shared Pool中分配出一塊記憶體區域用於儲存上述Child Cursor，而在Shared Pool中分配記憶體這個動作是要持有Shared Pool Latch的（Oracle資料庫中Latch的作用之一就是保護共享記憶體的分配），所以如果有一定數量的併發硬解析，可能就會導致Shared Pool Latch的爭用，而一旦發生大量的Shared Pool Latch爭用，系統的效能和可擴充套件性是會受到嚴重影響的（常常表現為CPU的佔用率居高不下，接近100％）。
② 硬解析可能會導致庫快取相關Latch（如Library Cache Latch）和Mutex的爭用。無論是哪種型別的硬解析，都需要掃描相關Hash Bucket中的庫快取物件控制程式碼連結串列，而掃描庫快取物件控制程式碼連結串列這個動作是要持有Library Cache Latch的（Oracle資料庫中Latch的另外一個作用就是用於共享SGA記憶體結構的併發訪問控制），所以如果有一定數量的併發硬解析，則也可能會導致Library Cache Latch的爭用。和Shared Pool Latch一樣，一旦發生大量的Library Cache Latch的爭用，系統的效能和可擴充套件性也會受到嚴重影響。這裡需要注意的是，從11gR1開始，Oracle用Mutex替換了庫快取相關Latch，所以在Oracle 11gR1及其後續的版本中，將不再存在庫快取相關Latch的爭用，取而代之的是Mutex的爭用（你可以簡單地將Mutex理解成是一種輕量級的Latch，Mutex主要也是用於共享SGA記憶體結構的併發訪問控制），Oracle也因此引入了一系列新的等待事件來描述這種Mutex的爭用，比如“Cursor:pinS”、“Cursor:pinX”、“Cursor:pin S wait on X”、"Cursor:mutex S'、“Cursor:mutex X”、“Library cache：mutex X”等。
正是因為大量的硬解析可能會導致Shared Pool Latch、庫快取相關Latch/Mutex的爭用，進而會嚴重影響系統的效能和可擴充套件性，所以才有“硬解析是萬惡之源”這樣的說法，但實際上，這種說法是不準確的。硬解析是非常不好，它的危害性也有目共睹，但硬解析是否會對系統造成損壞實際上取決於系統的型別，對於高併發的OLTP型別的系統而言，硬解析確實會嚴重影響系統的效能和可擴充套件性；但對於OLAP/DSS型別的系統而言，併發的數量很少，目標SQL也很少被併發重複執行，而且在執行目標SQL時硬解析所耗費的時間和資源與該SQL總的執行時間和資源消耗相比是微不足道的，這種情況下用硬解析是沒問題的，此時硬解析對系統效能的影響微乎其微，可以忽略不計。所以更為準確的說法應該是一一對於OLTP型別的系統而言，硬解析是萬惡之源！
（二）軟解析（Soft Parse）
軟解析（Soft Parse)是指Oracle在執行目標SQL時，在Library Cache中找到了匹配的父遊標（Parent Cursor）和子游標（Child Cursor），並將儲存在子游標中的解析樹和執行計劃直接拿過來重用而無須從頭開始解析的過程。和硬解析相比，軟解析的優勢主要體現在如下這幾個方面：
（1）軟解析不會導致Shared Pool Latch的爭用。因為軟解析能夠在庫快取中找到匹配的Parent Cursor和Child Cursor，所以它不需要生成新的Parent Cursor和Child Cursor.這意味著軟解析根本就不需要持有Shared Pool Latch以便在Shared Pool中申請分配一塊共享記憶體區域，既然不需要持有Shared Pool Latch，自然不會有Shared Pool Latch的爭用，即Shared Pool Latch的爭用所帶來的系統效能和可擴充套件性的問題對軟解析來說並不存在。
（2）軟解析雖然也可能會導致庫快取相關Latch（如Library Cache Latch）和Mutex的爭用，但軟解析持有庫快取相關Latch的次數要少，而且軟解析對某些Latch（如Library Cache Latch）的持有時間會比硬解析短，這意味著即使產生了庫快取相關Latch的爭用，軟解析的爭用程度也沒有硬解析那麼嚴重，即庫快取相關Latch和Mutex的爭用所帶來的系統效能和可擴充套件性的問題對軟解析來說要比硬解析少很多。我們在3.1.12節中己經介紹過：硬解析會先持有LibraryCacheLatch，並且在不釋放LibraryCacheLatch的情況下持有Shared Pool Latch以便從Shared Pool中申請分配記憶體，成功申請後就會釋放Shared Pool Latch，最後再釋放Library Cache Latch。而軟解析是不需要持有Shared Pool Latch的，所以與軟解析比起來，硬解析持有Library Cache Latch的時間會更長，當然對Library Cache Latch爭用的程度就會更嚴重。
正是基於上述兩個方面的原因，如果OLTP型別的系統在執行目標SQL時能夠廣泛使用軟解析，那麼系統的效能和可擴充套件性就會比全部使用硬解析時有顯著的提升，執行目標SQL時需要消耗的系統資源（主要體現在CPU上）也會顯著降低。
（三）軟軟解析（Soft Soft Parse）
軟軟解析（Soft Soft Parse）是指若引數SESSION_CACHED_CURSORS的值大於0，並且該會話遊標所對應的目標SQL解析和執行的次數超過3次，則此時該會話遊標會被直接快取在當前會話的PGA中的。若該SQL再次執行的時候，則只需要對其進行語法分析、許可權物件分析之後就可以直接從當前會話的PGA中將之前快取的匹配會話遊標直接拿過來用就可以了，這就是軟軟解析。
當一個SQL語句以硬解析的方式解析和執行完畢後，這個目標SQL所對應的共享遊標（Shared Cursor）就己經被快取在庫快取中，它所對應的會話遊標（Session Cursor）也已使用完畢，這時候會根據引數SESSION_CACHED_CURSORS的不同而存在如下這兩種情況：
① 如果引數SESSION_CACHED_CURSORS的值等於0，那麼會話遊標就會正常執行Close操作。在這種情況下，當同一條目標SQL再次重複執行時（顯然是軟解析），此時是可以找到匹配的共享遊標的，但依然找不到匹配的會話遊標（因為之前硬解析時對應的會話遊標己經被Close掉了），這意味著Oracle還必須為該SQL新生成一個會話遊標，並且該會話遊標還會再經歷一次Open、Parse、Bind、Execute、Fetch和Close中的一個或多個階段。
② 如果引數SESSION_CACHED_CURSORS的值大於0，並且該會話遊標所對應的目標SQL解析和執行的次數超過3次，那麼Oracle就不會對會話遊標執行Close操作，而是會將其標記為Soft Closed，同時將其快取在當前會話的PGA中。這樣做的好處是，當目標SQL再次被重複執行時，此時共享遊標和會話遊標就都能夠找到匹配記錄了，這意味著Oracle己經不需要為該SQL再新生成一個會話遊標，而是隻需要從當前會話的PGA中將之前己經被標記為Soft Closed的匹配會話遊標直接拿過來用就可以了。顯然，和軟解析比，此時Oracle就省掉了Open一個新的會話遊標所需要耗費的資源和時間。另外，Close一個現有會話遊標也不需要做了（只需要將其標記為Soft Closed，同時將其快取在當前會話的PGA中就可以了）。當然，剩下的Parse、Bind、Execute、Fetch還是需要做的，這個過程就是所謂的“軟軟解析”。
從上述分析過程可以看出，軟軟解析與軟解析比起來，其好處主要體現在如下兩個方面：
① 和軟解析比，軟軟解省去了OPEN一個新的會話遊標和CLOSE一個現有會話遊標所需要耗費的資源和時間。
② 和軟解析比，軟軟解析在持有庫快取相關Latch的次數方面會更少。這是因為快取在PGA中的會話遊標所在的Hash Bucket中己經儲存了目標SQL的父遊標的庫快取物件控制程式碼地址，Oracle根據這個庫快取物件控制程式碼地址就可以直接去庫快取中訪問對應的父遊標了，而不再需要先持有庫快取相關Latch，再去庫快取的相應Hash Bucket的父遊標所在的庫快取物件控制程式碼連結串列中查詢匹配的父遊標了，所以軟軟解析在持有庫快取相關Latch的次數方面會比軟解析要少。
如果要徹底搞懂硬解析、軟解析和軟軟解析，那麼請閱讀崔華大師的書《基於Oracle的SQL最佳化》！！！小麥苗把裡邊的一些重點內容摘抄出來，共享給大家。
當客戶端程式，將SQL語句透過監聽器傳送到Oracle時, 會觸發一個Server process生成，來對該客戶程式服務。Server process得到SQL語句之後，對SQL語句進行Hash運算，然後根據Hash值到library cache中查詢，如果存在，則直接將library cache中的快取的執行計劃拿來執行，最後將執行結果返回該客戶端，這種SQL解析叫做軟解析；如果不存在，則會對該SQL進行解析parse，然後執行，返回結果，這種SQL解析叫做硬解析。
1.硬解析的步驟
硬解析一般包括下面幾個過程：
1）對SQL語句進行語法檢查，看是否有語法錯誤。比如select from where 等的拼寫錯誤，如果存在語法錯誤，則退出解析過程；
2）透過資料字典(row cache)，檢查SQL語句中涉及的物件和列是否存在。如果不存在，則退出解析過程。
3）檢查SQL語句的使用者是否對涉及到的物件是否有許可權。如果沒有則退出解析；
4）透過最佳化器建立一個最優的執行計劃。這個過程會根據資料字典中的物件的統計資訊，來計算多個執行計劃的cost，從而得到一個最優的執行計劃。這一步涉及到大量的資料運算，從而會消耗大量的CPU資源；(library cache最主要的目的就是透過軟解析來減少這個步驟)；
5）將該遊標所產生的執行計劃，SQL文字等裝載進library cache中的heap中。
2.軟解析
所謂軟解析，就是因為相同文字的SQL語句存在於library cache中，所以本次SQL語句的解析就可以去掉硬解析中的一個活多個步驟。從而節省大量的資源的耗費。
3.軟軟解析
所謂的軟軟解析，就是不解析。當設定了session_cached_cursors引數時，當某個session第三次執行相同的SQL語句時，則會把該SQL語句的遊標資訊轉移到該session的PGA中。這樣，當該session在執行該SQL語句時，會直接從PGA中取出執行計劃，從而跳過硬解析的所有步驟。
SQL> show parameter cursor;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 20
open_cursors設定每個session(會話)最多能夠同時開啟多少個cursors(遊標)。
================================================
摘自：http://www.itpub.net/thread-796685-1-1.html
SESSION_CACHED_CURSORS的值就是說的是一個session可以快取多少個cursor，讓後續相同的SQL語句不再開啟遊標,從而避免軟解析的過程來提高效能。(繫結變數是解決硬解析的問題)，軟解析同硬解析一樣,比較消耗資源.所以這個引數非常重要。
oracle有一個概念，那就是session cursor cache,中文描述就是有一塊記憶體區域，用來儲存關閉了的cursor。當一個cursor關閉之後，oracle會檢查這個cursor的request次數是否超過3次，如果超過了三次，就會放入session cursor cache，這樣在下次parse的時候，就可以從session cursor cache中找到這個statement, session cursor cache的管理也是使用LRU。
session_cached_cursors這個引數是控制session cursor cache的大小的。session_cached_cursors定義了session cursor cache中儲存的cursor的個數。這個值越大，則會消耗的記憶體越多。
另外檢查這個引數是否設定的合理，可以從兩個statistic來檢查。
SQL> select name,value from v$sysstat where name like '%cursor%';
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 16439
opened cursors current 55
session cursor cache hits 8944
session cursor cache count 101
cursor authentications 353
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 0
parse time elapsed 0
parse count (total) 17211
parse count (hard) 1128
parse count (failures) 2
parse count（total）就是總的parse次數中，session cursor cache hits就是在session cursor cache中找到的次數，所佔比例越高，效能越好。如果比例比較低，並且有剩餘記憶體的話，可以考慮加大該引數。
Oracle 9i及以前,該引數預設是0,10G上預設是20，11g上預設為50
Oracle 硬解析與軟解析
--=======================
-- Oracle 硬解析與軟解析
--=======================
Oracle 硬解析與軟解析是我們經常遇到的問題，什麼情況會產生硬解析，什麼情況產生軟解析，又當如何避免硬解析？下面的描述將給出
軟硬解析的產生，以及硬解析的弊端和如何避免硬解析的產生。
一、SQL語句的執行過程
當釋出一條SQL或PL/SQL命令時，Oracle會自動尋找該命令是否存在於共享池中來決定對當前的語句使用硬解析或軟解析。
通常情況下，SQL語句的執行過程如下：
a.SQL程式碼的語法(語法的正確性)及語義檢查(物件的存在性與許可權)。
b.將SQL程式碼的文字進行雜湊得到雜湊值。
c.如果共享池中存在相同的雜湊值，則對這個命令進一步判斷是否進行軟解析，否則到e步驟。
d.對於存在相同雜湊值的新命令列，其文字將與已存在的命令列的文字逐個進行比較。這些比較包括大小寫，字串是否一致，空格，註釋
等，如果一致，則對其進行軟解析，轉到步驟f。否則到d步驟。紅色字型描述有誤應該轉到步驟e(更正@20130905，謝網友指出)
e.硬解析，生成執行計劃。
f.執行SQL程式碼，返回結果。
二、不能使用軟解析的情形
1.下面的三個查詢語句，不能使用相同的共享SQL區。儘管查詢的表物件使用了大小寫，但Oracle為其生成了不同的執行計劃
select * from emp;
select * from Emp;
select * from EMP;
2.類似的情況，下面的查詢中，儘管其where子句empno的值不同，Oracle同樣為其生成了不同的執行計劃
select * from emp where empno=7369
select * from emp where empno=7788
3.在判斷是否使用硬解析時，所參照的物件及schema應該是相同的，如果物件相同，而schema不同，則需要使用硬解析,生成不同的執行計劃
sys@ASMDB> select owner,table_name from dba_tables where table_name like 'TB_OBJ%';
OWNER TABLE_NAME
------------------------------ ------------------------------
USR1 TB_OBJ --兩個物件的名字相同，當所有者不同
SCOTT TB_OBJ
usr1@ASMDB> select * from tb_obj;
scott@ASMDB> select * from tb_obj; --此時兩者都需要使用硬解析以及走不同的執行計劃
三、硬解析的弊端
硬解析即整個SQL語句的執行需要完完全全的解析，生成執行計劃。而硬解析，生成執行計劃需要耗用CPU資源，以及SGA資源。在此不
得不提的是對庫快取中閂的使用。閂是鎖的細化，可以理解為是一種輕量級的序列化裝置。當程式申請到閂後，則這些閂用於保護共享記憶體
的數在同一時刻不會被兩個以上的程式修改。在硬解析時，需要申請閂的使用，而閂的數量在有限的情況下需要等待。大量的閂的使用由此
造成需要使用閂的程式排隊越頻繁，效能則逾低下。
四、硬解析的演示
下面對上面的兩種情形進行演示
在兩個不同的session中完成，一個為sys帳戶的session，一個為scott賬戶的session，不同的session，其SQL命令列以不同的帳戶名開頭
如" sys@ASMDB> " 表示使用時sys帳戶的session，" scott@ASMDB> "表示scott帳戶的session
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --當前的硬解析值為569
parse count (hard) 64 569
scott@ASMDB> select * from emp;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一個查詢後硬解析值為570，解析次數增加了一次
parse count (hard) 64 570
scott@ASMDB> select * from Emp;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一個查詢後硬解析值為571
parse count (hard) 64 571
scott@ASMDB> select * from EMP;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一個查詢後硬解析值為572
parse count (hard) 64 572
scott@ASMDB> select * from emp where empno=7369;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一個查詢後硬解析值為573
parse count (hard) 64 573
scott@ASMDB> select * from emp where empno=7788; --此處原來empno=7369，複製錯誤所致，現已更正為7788@20130905
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一個查詢後硬解析值為574
parse count (hard) 64 574
從上面的示例中可以看出，儘管執行的語句存在細微的差別，但Oracle還是為其進行了硬解析，生成了不同的執行計劃。即便是同樣的SQL
語句，而兩條語句中空格的多少不一樣，Oracle同樣會進行硬解析。
五、編碼硬解析的改進方法
1.更改引數cursor_sharing
引數cursor_sharing決定了何種型別的SQL能夠使用相同的SQL area
CURSOR_SHARING = { SIMILAR | EXACT | FORCE }
EXACT --只有當釋出的SQL語句與快取中的語句完全相同時才用已有的執行計劃。
FORCE --如果SQL語句是字面量，則迫使Optimizer始終使用已有的執行計劃,無論已有的執行計劃是不是最佳的。
SIMILAR --如果SQL語句是字面量，則只有當已有的執行計劃是最佳時才使用它，如果已有執行計劃不是最佳則重新對這個SQL
--語句進行分析來制定最佳執行計劃。
可以基於不同的級別來設定該引數，如ALTER SESSION, ALTER SYSTEM
sys@ASMDB> show parameter cursor_shar --檢視引數cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
sys@ASMDB> alter system set cursor_sharing='similar'; --將引數cursor_sharing的值更改為similar
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --當前硬解析的值為865
parse count (hard) 64 865
scott@ASMDB> select * from dept where deptno=10;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一條SQL查詢後，硬解析的值變為866
parse count (hard) 64 866
scott@ASMDB> select * from dept where deptno=20;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一條SQL查詢後，硬解析的值沒有發生變化還是866
parse count (hard) 64 866
sys@ASMDB> select sql_text,child_number from v$sql -- 在下面的結果中可以看到SQL_TEXT列中使用了繫結變數:"SYS_B_0"
2 where sql_text like 'select * from dept where deptno%';
SQL_TEXT CHILD_NUMBER
-------------------------------------------------- ------------
select * from dept where deptno=:"SYS_B_0" 0
sys@ASMDB> alter system set cursor_sharing='exact'; --將cursor_sharing改回為exact
--接下來在scott的session 中執行deptno=40 和的查詢後再檢視sql_text，當cursor_sharing改為exact後，每執行那個一次
--也會在v$sql中增加一條語句
sys@ASMDB> select sql_text,child_number from v$sql
2 where sql_text like 'select * from dept where deptno%';
SQL_TEXT CHILD_NUMBER
-------------------------------------------------- ------------
select * from dept where deptno=50 0
select * from dept where deptno=40 0
select * from dept where deptno=:"SYS_B_0" 0
注意當該引數設定為similar，會產生不利的影響，可以參考這裡：cursor_sharing參數對於expdp的性能影響
2.使用繫結變數
繫結變數要求變數名稱，資料型別以及長度是一致，否則無法使用軟解析
繫結變數（bind variable）是指在DML語句中使用一個佔位符，即使用冒號後面緊跟變數名的形式，如下
select * from emp where empno=7788 --未使用繫結變數
select * from emp where empono=:eno --:eno即為繫結變數
在第二個查詢中，變數值在查詢執行時被提供。該查詢只編譯一次，隨後會把查詢計劃儲存在一個共享池（庫快取）中，以便以後獲取
和重用這個查詢計劃。
下面使用了繫結變數，但兩個變數其實質是不相同的，對這種情形，同樣使用硬解析
select * from emp where empno=:eno;
select * from emp where empno=:emp_no
使用繫結變數時要求不同的會話中使用了相同的回話環境，以及最佳化器的規則等。
使用繫結變數的例子(參照了TOM大師的Oracle 9i&10g程式設計藝術)
scott@ASMDB> create table tb_test(col int); --建立表tb_test
scott@ASMDB> create or replace procedure proc1 --建立儲存過程proc1使用繫結變數來插入新記錄
2 as
3 begin
4 for i in 1..10000
5 loop
6 execute immediate 'insert into tb_test values(:n)' using i;
7 end loop;
8 end;
9 /
Procedure created.
scott@ASMDB> create or replace procedure proc2 --建立儲存過程proc2，未使用繫結變數，因此每一個SQL插入語句都會硬解析
2 as
3 begin
4 for i in 1..10000
5 loop
6 execute immediate 'insert into tb_test values('||i||')';
7 end loop;
8 end;
9 /
Procedure created.
scott@ASMDB> exec runstats_pkg.rs_start
PL/SQL procedure successfully completed.
scott@ASMDB> exec proc1;
PL/SQL procedure successfully completed.
scott@ASMDB> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
scott@ASMDB> exec proc2;
PL/SQL procedure successfully completed.
scott@ASMDB> exec runstats_pkg.rs_stop(1000);
Run1 ran in 1769 hsecs
Run2 ran in 12243 hsecs --run2執行的時間是run1的/1769≈倍
run 1 ran in 14.45% of the time
Name Run1 Run2 Diff
LATCH.SQL memory manager worka 410 2,694 2,284
LATCH.session allocation 532 8,912 8,380
LATCH.simulator lru latch 33 9,371 9,338
LATCH.simulator hash latch 51 9,398 9,347
STAT...enqueue requests 31 10,030 9,999
STAT...enqueue releases 29 10,030 10,001
STAT...parse count (hard) 4 10,011 10,007 --硬解析的次數，前者只有四次
STAT...calls to get snapshot s 55 10,087 10,032
STAT...parse count (total) 33 10,067 10,034
STAT...consistent gets 247 10,353 10,106
STAT...consistent gets from ca 247 10,353 10,106
STAT...recursive calls 10,474 20,885 10,411
STAT...db block gets from cach 10,408 30,371 19,963
STAT...db block gets 10,408 30,371 19,963
LATCH.enqueues 322 21,820 21,498 --閂的佇列數比較
LATCH.enqueue hash chains 351 21,904 21,553
STAT...session logical reads 10,655 40,724 30,069
LATCH.library cache pin 40,348 72,410 32,062 --庫快取pin
LATCH.kks stats 8 40,061 40,053
LATCH.library cache lock 318 61,294 60,976
LATCH.cache buffers chains 51,851 118,340 66,489
LATCH.row cache objects 351 123,512 123,161
LATCH.library cache 40,710 234,653 193,943
LATCH.shared pool 20,357 243,376 223,019
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
157,159 974,086 816,927 16.13% --proc2使用閂的數量也遠遠多於proc1，其比值是.13%
PL/SQL procedure successfully completed.
由上面的示例可知，在未使用繫結變數的情形下，不論是解析次數，閂使用的數量，佇列，分配的記憶體，庫快取，行快取遠遠高於繫結
變數的情況。因此儘可能的使用繫結變數避免硬解析產生所需的額外的系統資源。
繫結變數的優點
減少SQL語句的硬解析，從而減少因硬解析產生的額外開銷(CPU,Shared pool,latch)。其次提高程式設計效率，減少資料庫的訪問次數。
繫結變數的缺點
最佳化器就會忽略直方圖的資訊，在生成執行計劃的時候可能不夠最佳化。SQL最佳化相對比較困難
六、總結
1.儘可能的避免硬解析，因為硬解析需要更多的CPU資源，閂等。
2.cursor_sharing引數應權衡利弊，需要考慮使用similar與force帶來的影響。
3.儘可能的使用繫結變數來避免硬解析。
父遊標、子游標及共享遊標
遊標是資料庫領域較為複雜的一個概念，因為遊標包含了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都將位於該檢視。
二、遊標的生命週期(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: robinson_0612@hotmail.com */
- /* 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值不同 **************/
- /***************不同的子游標有不同的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文字、執行環境完全一致的情形下，子游標能夠被共享，否則如果執行環境不一致則生成新的子游標
怎麼找出解析失敗的SQL語句？
很多時候我們會有這樣一個誤區，語法錯誤或者物件不存在應該在語法語義檢查這個步驟就結束了，怎麼還會存在共享池裡面呢？帶著這個幾個問題我們做幾個簡單的測試。
我們先了解下什麼是解析失敗的 SQL？
1、SQL語法錯誤
2、訪問的物件不存在
3、沒有許可權
那麼怎麼證明有哪些解析失敗的SQL
我們知道 SQL 語句必須至少是一個父遊標一個子遊標存在的，當然生產中很多情況下都是一父多子的情況。
父遊標與子游標結構是一樣的，區別在於sql解析相關資訊儲存在父遊標對應的heap 0中，而sql的執行計劃等資訊儲存在子
遊標對應的庫快取物件heap 6記憶體空間中。另外父遊標的 heap 0中儲存著子游標的控制程式碼地址。如果解析錯誤的SQL在共
享池中儲存的話那麼必然要產生一個父遊標然後父遊標裡面儲存的有相關的解析資訊，但是子游標的？既然解析失敗那麼就
沒有產生執行計劃。
則利用這一點可以找到解析失敗的語句。
父遊標控制程式碼對地址可以在 x$kglob 檢視中查詢到，KGLHDPAR=KGLHDADR 的記錄為父遊標，
而KGLHDPAR<>KGLHDADR為子游標
X$KGLOB
該檢視定義為 [K]ernel[G]eneric [L]ibrary Cache Manager
KGLHDADR RAW(4|8) Address of kglhd for this object
可以看到：
KGLOBHD0 RAW(4|8) Address of heap 0 descriptor
KGLOBHD6 RAW(4|8) Address of heap 6 descriptor
SQL> select * from scott.emp;
SQL> col kglnaobj for a50;
SQL> select kglnaobj,kglnatim,kglhdpar,kglhdadr,KGLOBHD0,KGLOBHD6 from x$kglob where KGLNAOBJ='select * from scott.emp';
KGLNAOBJ KGLNATIM KGLHDPAR KGLHDADR KGLOBHD0 KGLOBHD6
-------------------------------------------------- ------------------- ---------------- ---------------- ---------------- ----------------
select * from scott.emp 2017-07-07 14:54:52 0000000096AE88B0 00000000958B9A40 0000000096AE85D8 000000007713C758
select * from scott.emp 2017-07-07 14:54:52 0000000096AE88B0 0000000096AE88B0 0000000095871858 00
x$kglcursor_child_sqlid (只包含子游標資訊)
SQL> select kglnaobj,kglnatim,kglhdpar,kglhdadr,KGLOBHD0,KGLOBHD6 from x$kglcursor_child_sqlid where KGLNAOBJ='select * from scott.emp';
KGLNAOBJ KGLNATIM KGLHDPAR KGLHDADR KGLOBHD0 KGLOBHD6
-------------------------------------------------- ------------------- ---------------- ---------------- ---------------- ----------------
select * from scott.emp 2017-07-07 14:54:52 0000000096AE88B0 00000000958B9A40 0000000096AE85D8 000000007713C758
0000000096AE88B0 為select * from scott.emp; 父遊標控制程式碼地址，00000000958B9A40為子游標控制程式碼地址
子游標heap 6（KGLOBHD6）的地址為000000007713C758，控制程式碼中儲存的也就是執行計劃相關的資訊。
透過以上測試我們很容易找到sql的父遊標的控制程式碼還有子游標的控制程式碼在記憶體中的地址。
下面做另外一個簡單的測試解析錯誤的SQL是否有父遊標和子游標生成。
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select kglnaobj,kglnatim,kglhdpar,kglhdadr,KGLOBHD0,KGLOBHD6 from x$kglob where KGLNAOBJ='select * from test';
KGLNAOBJ KGLNATIM KGLHDPAR KGLHDADR KGLOBHD0 KGLOBHD6
-------------------------------------------------- ------------------- ---------------- ---------------- ---------------- ----------------
select * from test 2017-07-07 15:06:28 0000000097DDC190 0000000097F941F8 00 00
select * from test 2017-07-07 15:06:28 0000000097DDC190 0000000097DDC190 000000009E035698 00
SQL> select kglnaobj,kglnatim,kglhdpar,kglhdadr,KGLOBHD0,KGLOBHD6 from x$kglcursor_child_sqlid where KGLNAOBJ='select * from test';
no rows selected
可以看到沒有子游標生成，因為該SQL執行錯誤不會有執行計劃相關資訊。
從x$kglob 也可以查到 kglobhd0、kglobhd6 都為空(NULL)。
在 x$kglcursor_child 檢視也查不到任何資訊的，v$sql v$sqlare 類似的檢視也就查不到解析錯誤的 SQL 了。
關於解析失敗的SQL還是需要獲取latch，其實從上面的測試已經證明了還是要獲取 shared pool 的 latch的，因為生成了父遊標。
透過以上測試說明解析失敗的sql只生成了父遊標，而沒有生成子游標和執行計劃資訊。
也可以用一下sql查出當前資料庫中所有解析失敗的sql
select kglnaobj,kglnatim,kglhdpar,kglhdadr,KGLOBHD0,KGLOBHD6 from x$kglob where kglhdpar<>kglhdadr
and KGLOBHD6='00' and KGLOBHD0='00' order by kglnatim desc;
從整個過程來看即使解析失敗父遊標是需要分配空間的，如果沒有使用繫結變數的情況下需要大量的分配
記憶體空間來儲存這些解析失敗語句的父遊標，它不僅會持有latch:libary cache而且會持有latch:shared
pool.
最後猜測一下：
KGLNAOBJ KGLNATIM KGLHDPAR KGLHDADR KGLOBHD0 KGLOBHD6
-------------------------------------------------- ------------------- ---------------- ---------------- ---------------- ----------------
select * from scott.emp 2017-07-07 14:54:52 0000000096AE88B0 00000000958B9A40 0000000096AE85D8 000000007713C758
SQL> select ksmchptr,ksmchcom,ksmchcls,ksmchsiz from x$ksmsp where KSMCHPAR='0000000096AE85D8';
KSMCHPTR KSMCHCOM KSMCHCLS KSMCHSIZ
---------------- ------------------------------------------------ ------------------------ ----------
000000007713AFD8 KGLH0^31fa0cc recr 4096（chunk 大小）
如上：
KGLHDADR:應該為整個遊標結構體(控制程式碼)的虛擬記憶體地址其地址為00000000958B9A40
KGLHDPAR:為父遊標構體(控制程式碼的)的虛擬記憶體地址其地址為0000000096AE88B0
而父遊標的KGLHDPAR和KGLHDADR相等，子游標KGLHDPAR為父遊標構體(控制程式碼的)的虛擬記憶體地址，KGLHDADR為自己的
遊標構體(控制程式碼的)的虛擬記憶體地址
在這個結構體中有一根指標指向void* p 指向heap 0 ds描述符的記憶體空間，虛擬記憶體地址為0000000096AE85D8
ds描述符:應該也是一個結構體其中又有一根指標void* p 指向heap 0實際的虛擬記憶體地址為000000007713AFD8
那麼heap0實際的地址為000000007713AFD8
比如：
struct ds
{
KSMCHCOM;
KSMCHCLS;
KSMCHSIZ;
.........
void* p;
}
如果進行dump可以確實可以看到這根指標儲存確實儲存在記憶體中
About Me
...............................................................................................................................
● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 聯絡我請加QQ好友(646634621)，註明新增緣由
● 於 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2140502/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 草稿 - 遊標，硬解析，軟解析 等
- ORACLE 硬解析和軟解析 軟軟解析Oracle
- 徹底弄懂oracle硬解析、軟解析、軟軟解析Oracle
- Oracle - 共享遊標、父子游標、硬軟解析Oracle
- Oracle 硬解析與軟解析Oracle
- Oracle的硬解析和軟解析Oracle
- ORACLE SQL解析之硬解析和軟解析OracleSQL
- Oracle SQL的硬解析和軟解析OracleSQL
- 軟解析和硬解析
- Oracle的軟解析(soft prase)和硬解析(hard prase)Oracle
- ORACLE的軟 軟 軟 解析!Oracle
- 共享池之八：軟解析、硬解析、軟軟解析 詳解一條SQL在library cache中解析涉及的鎖SQL
- soft parse(軟解析),hard parse(硬解析)
- 父遊標 子游標和軟硬解析記載-02
- 軟解析、硬解析的一個小測試
- SQL大致流程、SPM、軟軟、軟、硬解析SQL
- 在oracle 10.2.0.5分析硬解析及軟解析及軟軟解析獲取shared pool latch機制系列五Oracle
- oracle實驗記錄 （分析oracle硬解析&軟解析&fast soft parse）OracleAST
- oracle實驗記錄 (分析oracle硬解析&軟解析&fast soft parse(2))OracleAST
- 遊標引數shared_cached_cursors和軟軟解析
- 硬解析物理讀VS軟解析邏輯讀 測試
- 【體系結構】sql語句解析過程小實驗 軟解析、硬解析SQL
- 硬解析和物理讀取與軟解析和邏輯讀取
- Linux 核心的軟中斷深入解析Linux
- 深入解析Laravel的中介軟體Laravel
- 關於軟解析(soft parse)與硬解析(hard parse),以及session cached cursors (asktom)Session
- 【軟考之線性表解析】
- 解析軟體專案管理(轉)專案管理
- 怎麼找出解析失敗的sqlSQL
- 中介軟體的引數解析過程
- 專案管理軟體功能全解析專案管理
- 解析訊息中介軟體之RabbitMQMQ
- 軟體測試面試過程解析面試
- 等待模擬-library cache 軟解析
- 深入解析：由SQL解析失敗看開發與DBA的效能之爭SQL
- TP 框架解析 JSON 失敗原因框架JSON
- DNS解析失敗了怎麼辦DNS
- 國外好用的企業CRM軟體解析