866
技術社區[雲棲]
Library Cache優化與SQL遊標
Dear
跟著小編一起讀好書運動開始啦！咱們作為DBA不僅要外部打扮自己，更要從內部武裝。近期，小編將分享冷菠老師的《Oracle高性能自動化運維》一部分精選章節分享給大家。如果你對內容很感興趣，還是要去買一本比較好哦。
冷菠
冷菠，網名悠然（個人主頁https://www.orasky.net ），資深DBA，著有《Oracle高性能自動化運維》，有近10年的數據庫運維、團隊管理以及培訓經驗。曾擔任美資企業Senior DBA職務、支付公司數據庫團隊負責人，現為培訓機構重慶優唯佳科技公司技術合夥人。
擅長數據庫備份恢複、數據庫性能診斷優化以及數據庫自動化運維等，對主機存儲、網絡、係統業務架構設計優化、大數據等領域有較為深入的研究。目前致力於大數據、智能一體化、開源雲計算等領域的佳實踐探索。
Library Cache主要用於存放SQL遊標，而SQL遊標最大化共享是Library Cache優化的重要途徑，可以使SQL運行開銷最低、性能最優。
在PL/SQL中，遊標（Cursor）是數據集遍曆的內存集合。而從廣義上講， 遊標是SQL語句在Library Cache中的內存載體。
SQL語句與遊標關係如下：
1.一條SQL語句包含一個父遊標（Parent Cursor）和一到多個子遊標（Child Cursors），如圖2-2所示。
圖2-2 SQL語句與遊標
2.SQL語句通過SQL_ID唯一標識父遊標，如下所示：
從上述示例可以看出，SQL語句使用SQL_ID唯一標識父遊標（V$SQLAREA），同時該SQL語句僅包含一父遊標和一個子遊標。
3.不同的SQL語句的父遊標也不同，如下所示：
可以看出，2個不同SQL語句對應的SQL_ID也不相同，產生了不同的父遊標。
小提示
當SQL語句父遊標不相同，其對應的子遊標也肯定不同。
父遊標的主要特點如下：
q 父遊標是由SQL語句決定；
q 父遊標使用SQL語句的SQL_ID唯一標識；
q 父遊標包含一到多個子遊標；
q 父遊標與參數cursor_sharing緊密相關。
父遊標的主要組成結構如表2-2所示：
表2-2 父遊標組成結構
父遊標組成結構單元之間的關係，如圖2-3所示：
圖2-3 父遊標組成結構
父遊標信息可以通過V$SQLAREA視圖進行查詢。
V$SQLAREA主要特點有：
- V$SQLAREA中一條記錄表示一個父遊標，如下所示：
可以看出在V$SQLAREA視圖中，SQL_ID是唯一的，從側麵也可以說V$SQLAREA中一條記錄代表一個父遊標。
- V$SQLAREA隻包含父遊標的相關信息。
參數cursor_sharing決定父遊標被共享的模式，用於減少解析帶來的開銷，提升SQL執行效率。
cursor_sharing的3種模式：
- EXACT （默認模式），如下所示：
- FORCE
- SIMILAR
接下來對3種模式進行詳細介紹。
- cursor_sharing= EXACT
默認模式。隻有SQL語句內容完全一樣，才會共享父遊標（SQL語句之間才會共享）。也就是說，當用戶端發起的SQL語句隻要有一點不相同，就會產生不同的父遊標，從而不會共享SQL父遊標。如下所示：
- cursor_sharing = FORCE
當模式設置為FOCE時，將會強製優化器共享父遊標，而不管執行計劃是否最優。當條件允許時，可以采用這種方式來減少解析開銷。如下所示：
可以看出，在FORCE模式下，將2條內容不同的SQL強製共享父遊標（使用係統綁定變量）。
小提示
FORCE模式建議不要過度使用，雖然這種模式會強製SQL共享父遊標，但是這樣可能會忽略CBO優化器最優的執行計劃，使得SQL執行不是最優化的。
- cursor_sharing = SIMILAR
模式SIMILAR表示優化器在一定條件下會自動選擇共享遊標：
- 當SQL語句幾乎完全相同時；
- 當執行計劃相同或者執行計劃更優時；
- 當忽略SQL語句文字內容差異共享遊標
可以通過以下示例進行驗證：
- 示例1：參數變化導致遊標共享差異。
可以看出，當模式設置為SIMILAR時，隻要SQL語句相似就可以共享遊標 。
- 示例2：父子遊標。
示例2可以概括為圖2-4：
圖2-4 父子遊標與cursor_sharing
通過圖2-4可以看到，一個父遊標可以包含多個子遊標，驗證了圖2-2的正確性。
子遊標的主要特點有：
- V$SQL中一條記錄對應一個子遊標
- 子遊標與綁定變量（Bind Variable）、NLS參設置等相關
- 子遊標與參數optimizer_mode緊密相關
子遊標的主要組成結構如表2-3所示：
表2-3 子遊標組成結構
子遊標組成結構單元之間的關係，如圖2-5所示：
圖2-5 子遊標組成結構
子遊標信息可以通以V$SQL（X$KGLCURSOR_CHILD視圖進行查詢。
V$SQL主要特點有：
- V$SQL中一條記錄代表一個子遊標。如下所示：
可以看到，一個SQL_ID（父遊標）包含了多條記錄，每條記錄代表一個子遊標。
V$SQL包含了父遊標和子遊標信息。
參數optimizer_mode用於設置子遊標的CBO優化器模式。
可以通過查詢V$SQL_SHARED_CURSOR. OPTIMIZER_MISMATCH驗證子遊標不匹配（missmatch）原因：是否由參數optimizer_mode導致的。如下所示：
可以將上麵內容可以概括如圖2-6所示：
圖2-6 父子遊標與optimizer_mode
來源：數據和雲
原文鏈接
最後更新：2017-08-30 12:02:41