【DB筆試面試579】在Oracle中，SQL的解析過程的硬解析、軟解析和軟軟解析的區別有哪些？

• 2019 年 10 月 10 日
• 筆記

♣

題目部分

在Oracle中，SQL的解析過程的硬解析、軟解析和軟軟解析的區別有哪些？

♣

答案部分

在Oracle中，每條SQL語句在正式執行之前都需要經過解析（Parse），根據解析的過程可以分為3種類型：硬解析（Hard Parse）、軟解析（Soft Parse）和軟軟解析（Soft Soft Parse），軟軟解析也叫快速解析（Fast Parse）。DDL語句是從來不會共享使用的，也就是說DDL語句每次執行都需要進行硬解析。但是，DML語句和SELECT語句會根據情況選擇是進行硬解析，還是進行軟解析或者進行軟軟解析。SQL的解析過程大致可以參考下圖：

Oracle在解析和執行目標SQL時，會先去當前會話的PGA中查找是否存在匹配的快取會話游標（Session Cursor）。當Oracle第一次解析和執行目標SQL時（顯然是硬解析），當前會話的PGA中肯定不存在匹配的會話游標，這時Oracle會新生成一個會話游標和一對共享游標（包含一個父游標和一個子游標），這其中的共享游標會存儲能被所有會話共享、重用的內容（比如目標SQL的解析樹、執行計劃等），而會話游標則會經歷一次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）、軟解析（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的次數方面會比軟解析要少。

& 說明：

有關SQL解析的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140502/

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。