好記性不如爛筆頭之Oracle SQL最佳化（2）

標籤：

 *sql最佳化基於oracle11gR2讀書筆記*三、Oracle裡的Cursor

        Oracle中的Cursor是Oracle資料庫中SQL解析和執行的載體，是c語言的一種資料結構（oracle是用c寫的）。

       Oracle資料庫中的Cursor分為兩種：一種是Shared Cursor，另外一種是Session Cursor。

1、Shared Cursor

      先來瞭解一下什麼是庫緩衝、庫緩衝對象。

      我們知道Oracle中有一個全域記憶體地區SGA，而SGA又可以分為java池、大池、共用池、空池等等。而庫緩衝是共用池中的一塊記憶體地區，它的主要作用是緩衝剛剛執行過的SQL語句和PL/SQL語句，比如預存程序、函數、包、觸發器等所對應的執行計畫、解析樹、Pcode、Mcode等，當同樣的SQL語句和PL/SQL語句再次被執行時，就可以利用已經緩衝在庫緩衝中的那些相關對象而無需再次開始從頭解析，這樣就提高了這些SQL語句和PL/SQL語句在重複執行時的執行效率。

      緩衝在庫緩衝中的對象就是庫緩衝對象。所有的庫緩衝對象又是以一種名為庫緩衝物件控點的c結構儲存在庫緩衝中，oracle通過訪問相關的庫緩衝物件控點來訪問對應的庫緩衝對象。庫緩衝物件控點是以雜湊表的方式儲存在庫緩衝中。雜湊值相同的庫緩衝物件控點以鏈式的結構儲存在同一個雜湊槽中，形成了一個控制代碼鏈。

      言歸正傳。

      Oracle資料庫中的Shared Cursor就是指緩衝在庫緩衝裡的一種庫緩衝對象，說白了就是指緩衝在庫緩衝裡的SQL語句和匿名PL/SQL語句所對應的庫緩衝對象。Shared Cursor裡會儲存目標SQL的SQL文本、解析樹、該SQL所涉及的對象定義、該SQL使用的綁定變數類型和長度，以及該SQL的執行計畫等資訊。

      Shared Cursor又細分為Parent Cursor和Child Cursor，可以通過分別查詢檢視V$SQLAREA和V$SQL來查看當前緩衝在庫緩衝中的Parent Cursor和Child Cursor資訊。

      Parent Cursor和Child Cursor的結構是一樣的，都是庫緩衝物件控點，他們的區別在於Parent Cursor會把目標SQL的SQL文本儲存在Name屬性中，而Child Cursor的Name屬性值為空白，而該目標SQL的解析樹和執行計畫則會儲存在其Child Cursor（一種執行計畫對應一個Child Cursor）中。Parent Cursor會儲存所有該Parent Cursor的Child Cursor的控制代碼地址，即通過Parent Cursor可以找到其所有的Child Cursor. 這種結構決定了在oracle資料庫中，任意一個目標SQL一定會同時對應兩個Shared Cursor，一個Parent Cursor和一個Child Cursor，Parent Cursor會儲存目標SQL的文本，而目標SQL真正能被重用的解析樹和執行計畫則被儲存在Child Cursor裡面。

      上面介紹過庫緩衝物件控點是儲存在一個雜湊map的結構中，那麼庫緩衝物件控點是以什麼來產生雜湊值的呢？庫緩衝物件控點是以其Name屬性和NameSpace屬性來產生雜湊值的，對於Parent Cursor就是目標SQL的SQL文本和NameSpace屬性（值為CRSR）。

      瞭解了Shared Cursor中儲存的資訊及其在庫緩衝中的儲存方式，下面介紹下oracle是如何做到執行計畫的重用的。

       Oracle在解析目標SQL時，首先會去庫緩衝中尋找匹配的庫緩衝對象，其尋找順序如下：

      1) 根據目標SQL的SQL文本（庫緩衝物件控點的Name屬性）做雜湊運算，用得到的雜湊值去庫緩衝中尋找對應的庫緩衝對象。因為對於Shared Cursor來說其緩衝物件控點的NameSpace屬性值為固定的CRSR，所以這裡忽略。

      2) 然後在根據雜湊值尋找到雜湊槽中的庫緩衝對象鏈表中尋找匹配的Parent Cursor。在尋找匹配的Parent Cursor過程中會比對目標SQL的SQL文本，因為不同的SQL文本計算出來的雜湊值有可能相同。

      3) 步驟2中尋找成功，接下來遍曆該Parent Cursor以尋找匹配的Child Cursor。

      4) 步驟2中尋找失敗，意味著此時沒有可以共用的解析樹和執行計畫，oracle會從頭開始解析，產生Parent Cursor和Child Cursor並將其放在對應的雜湊槽中。

      5) 步驟3如果找到了對應的Child Cursor，則Oracle會把儲存在該Child Cursor的解析樹和執行計畫拿出來重用，而不用再從頭開始解析。

      6) 步驟3如果找不到對應的Child Cursor，則意味著沒有可以共用的解析樹和執行計畫，oracle會從頭開始解析，新產生一個Child Cursor，並把這個Child Cursor掛在相應的Parent Cursor下面。既然能找到Parent Cursor說明該SQL的執行計畫已被緩衝，那麼怎麼會出現找不到Child Cursor的情況呢？首先，Shared Cursor對象是一個全域共用的對象；其次oracle中存在多個資料表空間，而不同的資料表空間下面的表可以同名。如果兩個使用者在不同的資料表空間對同名的表做了同樣的操作，此時根據SQL的雜湊值及SQL文本的比較會得到同一個Parent Cursor，顯然此時的執行計畫肯定不能是一樣的（也許連表的定義都不一樣），所以會出現找不到Child Cursor的情況。

硬解析

       硬解析是指在執行目標SQL時，在庫緩衝中找不到可以重用的解析樹和執行計畫，而必須從頭開始解析目標SQL，並產生相應的Parent Cursor和Child Cursor.

       從前面對尋找庫緩衝對象的順序的介紹中，我們很容易得到發生硬解析時的兩種情境:一是找不到Parent Cursor必須進行硬解析；另外一種是找到了Parent Cursor但找不到Child Cursor時也必須進行硬解析。

       硬解析的危害：

       1) 硬解析可能導致Shared Pool Latch的爭用。發生硬解析時至少會產生一個Child Cursor，也就是必須在庫緩衝中分配一塊記憶體來儲存，而庫緩衝又是共用池（Shared Pool）中的一塊記憶體，所以實際上是需要在共用池中分配一塊記憶體，而Shared Pool Latch的作用之一就是在並發時保護共用池記憶體的分配，需要持有Shared Pool Latch才能對共用池記憶體進行分配。

       2) 硬解析可能會導致庫緩衝相關Latch和Mutex的爭用。在尋找庫緩衝物件控點（或者說在尋找Parent Cursor）時需要掃描庫緩衝物件控點鏈，這個動作需要持有Library Cache Latch（11gr1版本及以後用的是Mutex）。

軟解析

      軟解析是指在執行目標SQL時，在Library Cache中找到了匹配的Parent Cursor和Child Cursor，並重用解析樹和執行計畫而無需從頭開始解析的過程。

      首先軟解析不存在Shared Pool Latch爭用，因為不需要分配記憶體。軟解析也需要掃描庫緩衝物件控點鏈以尋找可重用的解析樹和執行計畫，所以不可避免的存在Library Cache Latch的爭用。但相比硬解析而言，一、軟解析的Library Cache Latch的爭用要少得多，對Library Cache Latch的持有時間也會比較短。

總結

如果OLTP類型的系統中在執行目標SQL時能夠廣泛的使用軟解析，則系統的效能和可擴充性就會比全部使用硬解析時有明顯提升，執行目標SQL所消耗的系統資源（主要體現在CPU上）也會顯著降低。

Session Cursor

        Session Cursor是當前session解析和執行SQL的載體。和Shared Cursor一樣，Session Cursor也是一張c語言的複雜結構，它也是以雜湊表的方式緩衝起來的，只不過時緩衝在PGA中，而不是緩衝在共用池的庫緩衝裡。

        關於Session Cursor的幾個要點：

        1) Session Cursor和session是一一對應的,不同的session的Session Cursor不能共用。

        2) Session Cursor是有生命週期的。每個Session Cursor在使用的過程中都至少會經曆一次Open，Parse，Bind，Execute，Fetch和Close中的一個或多個階段，用過的Session緩衝不一定會緩衝在對應Session的PGA中，這取決於SESSION_CACHED_CURSORS的值是否大於0。

        3) Oracle在解析和執行目標SQL時，會先去當前Session的PGA中去尋找是否存在匹配的Session Cursor。當Oracle第一次解析和執行目標SQL的時候，會新產生一個Session Cursor和一對Shared Cursor（Parent和Child），Shared Cursor會儲存能被其他Session共用、重用的內容（比如解析樹和執行計畫），而Session Cursor則會經曆一次Open、Parse、Bind、Execute、Fetch和Close中的一個或多個階段。在11g之前，Session Cursor中會儲存Parent Cursor的庫緩衝物件控點地址，從而通過Session Cursor可以直接定位到Parent Cursor。

       當上述目標SQL已硬解析的方式解析和執行完畢後，這個目標SQL鎖對應的Shared Cursor已經被緩衝在庫緩衝當中，它所對應的Session Cursor也已經使用完畢，這時候會存在以下兩種情況：

       第一種：如果參數SESSION_CACHED_CURSORS的值為0，那麼Session Cursor就會正常執行close操作，這樣當上述SQL再次執行時，在當前session中找不到對應的Session Cursor，但可以在庫緩衝中找到Parent Cursor和Child Cursor，此時oracle還必須為該SQL新產生一個Session Cursor，並且該Session Cursor還會再經曆一次Open,Parse,Bind,Execute,Fetch和Close中的一個或多個，這就是軟解析。

       第二種：如果參數SESSION_CACHED_CURSORS的值大於0，那麼當滿足額外條件時（這個條件後面會提到），oracle就不會對Session Cursor執行Close操作，而是將其標記為Soft Closed，當SQL重複執行時就可以通過Session Cursor直接定位到Parent Cursor，對其解析樹，執行計畫等進行重用，這叫做軟軟解析。和軟解析相比，軟軟解析省掉了建立一個新的Session Cursor的開銷，當然剩下的Open,Parse,Bind,Execute,Fetch還是需要做的。

Session Cursor的相關參數解析

OPEN_CURSORS

       用於設定單個Session中同時能夠以Open狀態並存的Session Cursor的總數。視圖V$OPEN_CURSOR可以用來查詢資料庫中狀態為OPEN或者已經被緩衝在PGA中的Session Cursor的數量和具體資訊（如SQLID和SQL文本）。

SESSION_CACHED_CURSORS

      用於設定單個session中能夠以Soft Closed狀態並存的Session Cursor總數，即用於單個session能夠緩衝在PGA中的Session Cursor總數。oracle用LRU演算法來管理這些Session Cursor。Session Cursor被緩衝在PGA中是有額外條件的，在11gr2中，一個Session Cursor能被緩衝在PGA中的條件是該SQL的解析和執行次數要超過3次。

Session Cursor的種類和用法

       Oracle中的Session Cursor又細分為三種類型：隱式遊標、顯式遊標和參考遊標（Ref Cursor）。這三種類型經常用在SQL，PL/SQL代碼中。

隱式遊標

       最常見的Session Cursor，它無處不在。當執行SQL的時候，oracle自動幫我們建立了隱式遊標來作為該SQL執行的載體，它的生命週期完全由SQL引擎或者PL/SQL引擎負責。即便如此我們還是可以通過如下四個屬性來探測隱式遊標的一些資訊（最近一條SQL的執行相關資訊）：

       SQL%FOUND

       SQL%NOTFOUND

       SQL%ISOPEN 對於隱式遊標來說這個屬性永遠是false

       SQL%ROWCOUNT

顯示遊標

       通常用於PL/SQL代碼中，它的生命週期完全由我們在PL/SQL代碼中來顯式控制。它也有四個屬性：

       CURSORNAME%FOUND

       CURSORNAME%NOTFOUND

       CURSORNAME%ISOPEN

       CURSORNAME%ROWCOUNT

參考遊標

      又名動態資料指標。

綁定變數

      使用綁定變數可以有效降低OLTP類型應用系統的硬解析數量。因為那些除SQL文本中對應的輸入值不同外其他部分一模一樣的同一類型的SQL如果使用了綁定變數，則SQL文本就變得完全相同了，據此計算出來的雜湊值也完全相同，這就意味著此時就具備了可以重用解析樹和執行計畫的基礎條件。

      綁定變數的使用文法是“:variable_name”,即用冒號和自訂變數名稱的組合來替換目標SQL文本中的具體輸入值。這裡的變數名可以是字母、數字或者字母與數位組合。

綁定變數的典型用法

     在SQL語句中使用綁定變數

            var x number;//首先定義變數

            exec :x := 1000;//給變數賦值

            --在SQL中使用綁定變數

             selec × from emp where empno = :x;

        PL/SQL中select語句的綁定變數用法

             execute immediate [帶綁定變數的目標SQL語句] using [對應綁定變數的具體輸入值]，舉例如下：

declare    v_name varchar2(10);begin   execute immediate 'select name from person where name = :1' into v_name using 'sara';  dbms_output.put_line(v_name);end;

       PL/SQL中DML語句的綁定變數用法

declare    v_sql1 varchar2(4000);   v_sql2 varchar2(4000);begin   v_sql1 := 'insert into person (name,dob,home_addr,work_addr) values (:1,:2,:3,:4)';   execute immediate v_sql1 using 'link',to_date('1987-01-28','YYYY-MM-DD'),'SZ','HN';   dbms_output.put_line(sql%rowcount);   v_sql2 := 'insert into person (name,dob,home_addr,work_addr) values (:1,:1,:1,:1)';   execute immediate v_sql1 using 'dd',to_date('1987-01-28','YYYY-MM-DD'),'SZ','HN';   dbms_output.put_line(sql%rowcount);end;

       關鍵字using後面傳入的綁定變數具體輸入值只與對應綁定變數在SQL語句中的位置有關，而與名稱無關。從上述範例可以看出同一條SQL語句中的綁定變數的名稱是可以相同的。

declare    v_sql1 varchar2(4000);   v_name varchar2(10);begin   v_sql1 := 'delete from person where name = :1 returning name into :2';   execute immediate v_sql1 using 'dd' returning into v_name;   dbms_output.put_line(v_name);end;

       關鍵字returning可以和帶綁定變數的SQL連用，其目的是把受該SQL影響的目標行的對應列的值給取出來。

       PL/SQL中大量繫結的典型用法

       大量繫結是一種最佳化後的使用綁定變數的方式，它的核心在於以下兩點：

       1、大量繫結還是會以之前介紹過的方式來使用綁定變數

       2、大量繫結的優勢在於它是一次性處理一批資料，而不是像常規方式那樣一次只處理一條資料，所以它能有效減少PL/SQL引擎和SQL引擎內容相關的切換次數，從而提高執行效率。理論上PL/SQL代碼裡只要執行SQL語句就會發生PL/SQL引擎和SQL引擎的互動，但對PL/SQL代碼效能有影響的互動主要發生在PL/SQL代碼中的如下兩處。

       a) 顯示遊標或者參考遊標需要迴圈執行Fetch操作時。這裡的迴圈操作需要PL/SQL引擎來處理，而Fetch一條記錄對應要執行的SQL語句則需要SQL引擎來處理，所以如果不做最佳化，那麼這裡每Fetch一條記錄，引擎就需要切換一次。

       b) 迴圈內部需要執行SQL操作，和第一種情況一樣，如果不做最佳化則每迴圈一條記錄，引擎就要切換一次。

      批量Fetch對應的文法如下：

      fetch CURSOR_NAME bulk collect into  [自訂數組]  <limit batch_size>

      一次執行一批SQL語句的文法如下：

      forall i in 1..[數組長度]

            execute immediate [帶綁定變數的目標SQL] using [對應綁定變數的具體輸入值]

      關鍵字forall表示一次執行一批SQL語句，可以與INSERT、UPDATE、DELETE語句聯合使用。

綁定變數窺探

       我們知道，隨著具體輸入值的不同，目標SQL的where條件的可選擇率和結果集的行數可能會隨之發生變化，而可選擇率和結果集的行數會直接影響CBO對於相關執行步驟成本值的估算，進而影響CBO對於目標SQL執行計畫的選擇。這就意味著隨著具體輸入值的不同，目標SQL的執行計畫可能會發生變化。對於使用了綁定變數的目標SQL而言，情況就完全不一樣了，因為現在無論對應綁定變數的具體輸入值是什麼，目標SQL的SQL文本都是一模一樣，這種情況下Oracle應該如何來決定目標SQL的執行計畫呢？

       對於使用了綁定變數的目標SQL而言，Oracle可以選擇如下兩種方法來決定其執行計畫：

       1、使用綁定變數窺探

       2、如果不使用綁定變數窺探，則對於那些可選擇率可能會隨著具體輸入值的不同而不同的謂詞條件使用預設的選擇率。

       綁定變數窺探是在9i中引入的，是否啟用綁定變數受隱含參數_OPTIM_PEEK_USER_BINDS的控制，預設為true。啟用綁定變數窺探後，每當Oracle以硬解析的方式解析使用了綁定變數的目標SQL時，Oracle都會實際窺探一下對應綁定變數的具體輸入值，並以這些具體輸入值為標準，來決定這些使用了綁定變數的目標SQL的where條件的可選擇率和結果集行數，並據此來選擇該SQL的執行計畫。但是這個“窺探”動作只有在硬解析的時候才會執行，當使用了綁定變數的目標SQL再次執行時（此時是軟解析或者軟軟解析），即便此時對應綁定變數的具體輸入值和之前硬解析時對應的值不同，oracle也會沿用之前硬解析時所產生的解析樹和執行計畫，而不會重複執行上述的窺探的動作。

       綁定變數窺探的優點是可以避免使用預設的可選擇率，就有更大的可能性得到準確的執行計畫；同樣綁定變數窺探的壞處是對於那些執行計畫會隨著輸入值的不同而不同的SQL而言，一旦啟用的綁定變數窺探，其執行計畫就被固定下來了，至於接下來的執行計畫到底是什麼，則完全依賴於該SQL在硬解析時傳入的對應綁定變數的具體值。

       在Oracle 10g中出現了長條圖統計資訊後，綁定變數窺探的缺點被進一步放大，為了克服綁定變數窺探的缺點，Oracle 11g又引入了自適應遊標共用。自適應遊標共用可以讓使用了綁定變數的目標SQL在啟用了綁定變數窺探的前提條件下，不再只沿用之前硬解析時所產生的解析樹和執行計畫，也就是說自適應遊標共用可以在啟用了綁定變數窺探的前提條件下，讓目標SQL在其可能的多個執行計畫之間“自適應“地做出選擇，而不再像之前那樣必須得刻板地沿用該SQL硬解析時所產生的解析樹和執行計畫。

       如何手動使之前硬解析的執行計畫失效呢？可以對目標SQL中所涉及的表執行DDL操作，庫緩衝中所有在SQL文本中包含了這個表的Shared Cursor都會被標記為失效。另外一種方法是使用DBMS_SHARED_POOL.PURGE。它是從Oracle 10.2.0.4開始引用的一種方法，它可以用來刪除指定的緩衝在庫緩衝中的Shared Cursor。

 綁定變數分級

       綁定變數分級是指Oracle在PL/SQL代碼中會根據文本型綁定變數（對於數字型的不適用）的定義長度而將這些文本型的綁定變數分為四個等級。

       定義在32位元組以內的文本型綁定變數被分在第一個等級；33~128位元組之間為第二個等級；129~2000位元組之間為第三個等級，2000位元組以上為第四個等級。那麼為什麼要分等級呢？因為在執行目標SQL時。對目標SQL中的每一個綁定變數都要用實際的值來替換，所以該Session Cursor必須為這個綁定變數在PGA中預留一定長度的記憶體。最理想的情況是實際的長度為多少就分配多少，但這樣處理起來很麻煩，為了簡化，oracle就分了上述四個等級。對長度在第一個等級內的綁定變數，固定分配32位元組的記憶體；長度在第二個等級內的綁定變數分配128位元組的記憶體；長度在第三個等級內的綁定變數分配2000位元組的記憶體；而長度在第四個等級內的綁定變數，如果變數的實際長度小於等於2000，則分配2000，否則分配4000位元組的記憶體。例如：

----定義變數

v varchar2(28);

v_sql varchar2(2000);

----賦值

v:=‘abcd‘;

----由於v的定義長度為最大28，小於32，則分配32位元組的記憶體

v_sql := ‘select * from table_name where col1 = :1‘;

 execute immediately  v_sql  using v;

這裡提到綁定變數等級的目的是為了引入一個知識點：

對於PL/SQL代碼中那些使用了文本型綁定變數的目標SQL而言，只要其SQL文本中文本型綁定變數的定義長度發生了變化，則oracle為這些綁定變數分配的記憶體空間的大小也會隨著發生變化，這時候之前緩衝在Child Cursor中的解析樹和執行計畫就不能被重用了。因為在Child Cursor中除了會儲存目標SQL的解析樹和執行計畫之外還要儲存該SQL使用的綁定變數的類型和長度，即使SQL的文本沒有發生任何變化，只要其SQL文本中文本型變數的定義長度發生變化，那麼該SQL再次執行時就得做硬解析。

---清理共用池alter system flush shared_pool;declare   v_name_1 varchar2(100);  v_sql varchar2(2000);  v_name_2 varchar2(133);begin   v_name_1 := 'link';  v_name_2 := 'link';  v_sql := 'select * from person where name = :1';  execute immediate v_sql using v_name_1;  execute immediate v_sql using v_name_2;end;--執行了兩次，執行計畫對應兩個版本select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select * from person where name = :1%'select plan_hash_value,child_number from v$sql where sql_text like 'select * from person where name = :1%' 

查看已執行的目標SQL中綁定變數的值

查詢檢視v$sql_bind_capture如果v$sql_bind_capture中查不到，那麼有可能shared cursor已經被age out出了庫緩衝，這時候可以嘗試去AWR Repository相關的資料字典表DBA_HIST_SQLSTAT或DBA_HIST_SQLBIND中尋找。

對於解析和執行含有綁定變數的SQL語句時，如果滿足如下兩個條件之一，則該SQL中的綁定變數的具體輸入值就會被捕獲，並可通過視圖V$SQL_BIND_CAPTURE查詢:

1、當含有綁定變數的SQL以硬解析的方式執行時

2、當含有綁定變數的SQL以軟解析或者軟軟解析執行時，該SQL中的綁定變數的具體輸入值也可能被捕獲，只不過這種捕獲動作每15分鐘才會發生一次。

Oracle只會捕獲那些位於where語句中的綁定變數的具體輸入值。

 

 

好記性不如爛筆頭之Oracle SQL最佳化（2）