草稿 - 遊標，硬解析，軟解析 等

 

六， Library Cache中GET, PIN, LOCK概念

 

 

2.父遊標和子游標
每種型別的dml語句都需要如下階段：
Create a Cursor         建立遊標
Parse the Statement     分析語句
Bind Any Variables      繫結變數
Run the Statement       執行語句
Close the Cursor        關閉遊標

當資料庫第一次對一條SQL語句進行硬解析的時候，會在庫快取中分配一些記憶體，並將新產生的父遊標儲存進去。與父遊標有關的關鍵資訊是這個SQL語句的文字，這個時候，會在v$sqlarea裡面插入一條記錄。那麼，在什麼情

況下會產生子游標呢，當資料庫又碰到一條完全相同SQL語句，但是語句的執行計劃和執行環境發生了變化，比如由於繫結變數窺測而產生的不一致的執行計劃，由於SQL的初始化引數optimizer_mode的不同以及繫結變數分級

的情況都會產生子游標，當產生子游標的時候，會在v$sql裡面插入一條記錄。並且v$sqlarea裡的VERSION_COUNT欄位的值會加1。

 

eg：
--多個SQL語句只有在它們的文字完全一致的情況下才能共享一個父遊標，這是最基本的要求。
--視窗1執行
sys/SYS>select * from t;
no rows selected

sys/SYS>select * from t;
no rows selected

sys/SYS>SELECT * from t;
no rows selected

--視窗2執行
sys/SYS>select hash_value,sql_text,executions from v$sqlarea where sql_text like '%from t';

HASH_VALUE SQL_TEXT                                 EXECUTIONS
---------- ---------------------------------------- ----------
3542409071 SELECT * from t                                   1
 520543201 select * from t                                   2

可以看到由於SQL文字不同產生了兩個父遊標。
sys/SYS>select HASH_VALUE,child_number,sql_text from v$sql where sql_text like '%from t';

HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- ------------ ----------------------------------------
 520543201            0 select * from t
3542409071            0 SELECT * from t
在v$sql裡面也插入了兩條子游標的記錄，但是CHILD_NUMBER都是0，其實對應的就是父遊標。
現在再來看下由於optimizer_mode的不同而產生子游標的情況。

--視窗1執行

sys/SYS>alter session set optimizer_mode=all_rows;
Session altered.

sys/SYS>select * from tt;
no rows selected

sys/SYS>alter session set optimizer_mode=first_rows_10;
Session altered.

sys/SYS>select * from tt;
no rows selected

--視窗2執行

sys/SYS>select hash_value,sql_text,executions,VERSION_COUNT from v$sqlarea where sql_text like '%from tt';

HASH_VALUE SQL_TEXT                                 EXECUTIONS VERSION_COUNT
---------- ---------------------------------------- ---------- -------------
3762890390 select * from tt                                  2             2

sys/SYS>select HASH_VALUE,child_number,sql_text from v$sql where sql_text like '%from tt';

HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- ------------ ----------------------------------------
3762890390            0 select * from tt
3762890390            1 select * from tt

可以看到，SQL文字是完全相同的，所以兩個子游標共享了一個父遊標。但是由於optimizer_mode的不同，所以生成了2個子遊標。
如果產生了子游標，那麼說明肯定產生了某種mismatch，那麼如何來檢視是何種原因產生了mismatch呢？這就要透過v$sql_shared_cursor了。

sys/SYS>select kglhdpar, address,auth_check_mismatch, translation_mismatch,OPTIMIZER_MISMATCH
  2  from v$sql_shared_cursor
  3  where kglhdpar in
  4  ( select address
  5    from v$sql
  6    where sql_text like '%from tt');

KGLHDPAR ADDRESS  A T O
-------- -------- - - -
89BB8948 89BB83CC N N N
89BB8948 89BB5E78 N N Y　　　

可以看到OPTIMIZER_MISMATCH列第二行的值為Y，這說明了正是由於optimizer_mode的不同而產生了子游標。
最後，父遊標和子游標的意義何在？其實一切都是為了共享。以減少再次解析的資源浪費。

 

 

 

 

 

這裡如果是不同使用者或者期間物件許可權更改，就算是SQL文字一樣，也是無法共享子游標的，

生成handle，其實就是要想共享池申請空閒的記憶體，期間獲得shared pool latch ，那麼很容易產生衝突。handle其實就是存放的父遊標，真正的執行計劃是存放在子游標上的，也就是heap6上，一個父遊標可能對應多個子遊

標，比如，不同使用者下的相同SQL，就會造成1個父對多個子遊標，這種父遊標存在，而要重新生成子游標，就是relaod，需要耗硬體資源，資料庫效能也就十分低下，所以我們要避免硬解析和reload，解析過程中很消耗資源

，而且容易造成 latch的衝突，父遊標裡主要包含SQL文字，父遊標開啟時，是被鎖住的，也就是不能交換出library cache ，

 

 

 

 

只要是對錶做DDL操作都會重新解析原來的DML SQL . 
http://space.itpub.net/22034023/viewspace-664511

 

 

 

 

無法共享父遊標  

沒有共享的原因就是因為大小寫或者空格資料不一樣，但是這裡最大的原因是沒有使用繫結變數。

可以共享父遊標，但不可共享子游標：
子游標中最關鍵的資訊就是執行計劃和執行環境，執行環境
很重要，如果執行環境改變了，則執行計劃也會相應的有變動；

只有共享了父遊標和執行環境一樣了，那麼才可能共享子游標。

例如我們alter session set optimizer_mode=choose/first_rows_n/rule/first_rows/all_rows，這樣都會產生相應的子游標，
因為執行環境變了；

可以透過如下語句發現變化：
select sql_id,child_number,sql_text,executions,plan_hash_value，optimizer_mode from v$sql where lower(sql_text) like ;
select sql_id,child_number,sql_text,optimizer_mode,plan_hash_value from v$sql where sql_id=(select prev_sql_id from v$session
where sid=sys_context('userenv','sid'));

有些子游標，緊緊是因為環境改變了，而執行計劃是相同的，這個我們可以透過plan_hash_value看出；

而透過查詢v$sql_shared_cursor可以看出是什麼原因導致了子游標不能被共享：

select child_number,optimizer_mode,optimizer_mode_mismatch from v$sql_shared_cursor
where sql_id='kdfj';

而硬解析通常是由於 不能共享父遊標造成的，而非子游標。

 

 

 

Shared pool深入分析及效能調整(二)
2007年11月26日 11:58
2.2 轉儲library cache
oracle提供了命令可以對library cache中的內容進行轉儲。於是我們可以對library cache進行轉儲，從而對上面所說的library cache的內容進行驗證。
ALTER SESSION SET EVENTS 'immediate trace name library_cache level N';
這裡的N可以取的值分別為：
1 轉儲library cache的統計資訊
2 轉儲hash表的彙總資訊
4 轉儲library cache object的基本資訊
8 轉儲library cache object的詳細資訊
16 轉儲heap size的資訊
32 轉儲heap的詳細資訊
在測試之前，我們先建立一個測試表，然後再顯示該表的資料。從而在library cache中放入一些資料。
SQL> create table sharedpool_test as select * from dba_objects where rownum<10;
SQL> select object_id,object_name from sharedpool_test;
以level 1轉儲整個library cache。
SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 1';

    開啟跟蹤檔案可以看到類似這樣的資訊，這實際就是v$librarycache裡記錄的資訊，只不過v$librarycache中記錄的是根據下面的資訊合併彙總以後得到的。

namespace gets hit ratio pins hit ratio reloads invalids
-------------- --------- --------- --------- --------- ---------- ----------
CRSR 563 0.815 2717 0.916 15 0
TABL/PRCD/TYPE 403 0.730 568 0.653 0 0
BODY/TYBD 2 0.000 2 0.000 0 0
……………………
    然後，我們分別以level 4、8、16、32分別對library cache進行轉儲，生成的轉儲檔案分別以4#、8#、16#和32#來表示。
打 開4#檔案，然後直接查詢“select object_id,object_name from sharedpool_test”，因為我們前面說到過，對於SQL語句來說，整個SQL語句的文字就是library cache object的名稱。於是，我們可以發現類似下圖四所

示的內容：

 

      圖四
    這裡的BUCKET 62658就相當於圖二中的2號bucket。該bucket上只掛了一個物件，其物件控制程式碼號為6758cdbc。在這個物件控制程式碼裡存放了很多資訊，這裡 可以看到該物件的namespace為CRSR，也就是SQL AREA。可以看到該

SQL語句的hash值為541cf4c2，將其轉換為十進位制以後可以直接到v$sql中找到該SQL語句。我們還可以看到很複雜的 flags欄位，它會包括很多標記，比如RON表示只讀（Read Only），SML表示當前控制程式碼尺寸比較小（Small）等

。而下面的lwt則表示正在等待lock的物件列表（Lock Waiters），對應圖三中的“Lock Waiters”；ltm則表示臨時正在持有lock的物件列表（Lock Temporary），對應圖三中的“Lock Owners”；pwt則表示正在等待pin的對

象列表（Pin Waiters）對應圖三中的“Pin Waiters”；ptm則表示臨時正在持有pin的物件列表（Pin Temporary），對應圖三中的“Pin Owners”。再往下看，可以看到CHILDREN部分，這部分就是前面所說過的子游標的資訊

了。實際上，指向heap 0的指標也位於這一部分，這個指標也就是6758c840。

SQL> select sql_text from v$sql where hash_value=to_number('541cf4c2','xxxxxxxx');
SQL_TEXT
--------------------------------------------------------------------------------
select object_id,object_name from sharedpool_test

     然後，我們開啟8#檔案，查詢6758c840，可以看到如下圖五所示的內容。這就是heap 0中所包含的內容。可以看到該heap 0的handle正是6758c840，type為CRSR。還可以看到幾個重要的table，這些table都是我們前面介

紹過的，包括 DEPENDENCIES、ACCESSES、TRANSACTIONS。從前面我們已經知道dependency table記錄的是SQL語句所依賴的物件，這裡我們可以看到我們的SQL語句依賴一個物件，同時該物件的handle為 675d0d74，很明顯，

它一定指向sharedpool_test表。同時，我們可以看到transaction table所記錄的oracle底層解析的物件的handle也是675d0d74，它與dependency table所記錄的物件是一樣的，說明這個表是實實在在的表，而不是一個同名

詞。

                 圖五
    於是我們繼續在8#檔案裡查詢675d0d74，也就是找到library cache中記錄SQL所引用的物件的部分。
我們可以看到類似下圖六所示的內容。從name列中可以看到，該物件正是sharedpool_test表，同時該表所在的schema為COST。而且從type為TABL也可以看到，物件sharedpool_test是一個表。

   

     圖六
    我們再次回到圖五，也就是記錄heap 0的部分。我們可以看到最後一部分是DATA BLOCKS，從我們前面介紹過的內容可以知道這部分的記錄指向了其他的heap記憶體塊。我們從data#列上可以知道，該SQL存在兩個相關的

heap，編號為0和6。我們知道，heap 0存放了SQL語句本身所涉及到的物件以及若干種表等的資訊，而heap 6則存放了SQL語句的文字、執行計劃等。於是，我們可以到32#檔案中查詢6758c7d0（heap 0）和67587c34（heap 6）

，如下圖七所示。我們同時可以看到owner的值，實際上這正是在圖五中的object的代號。同時從heap的name處也可以看到，heap 0為library cache，而heap 6為sql area，這也說明了這兩個不同的heap所存放的不同內容。

   

    圖七
2.3 dictionary cache概述
    dictionary cache專門用來存放SYS schema所擁有的物件的記憶體區域。使用dictionary cache時以行為單位，而不像其他比如buffer cache以資料塊為單位，因此dictionary cache也叫做row cache。構造dictionary

cache的目的是為了加快解析SQL語句的速度，因為dictionary cache裡存放了所有表的定義、Storage資訊、使用者許可權資訊、約束定義、回滾段資訊、表的統計資訊等。
    而這些資訊都是在解析過程中必須用到的。假設oracle在解析SQL的過程中，發現dictionary cache裡沒有該SQL所引用的表的定義資訊，則oracle必須到磁碟上system表空間裡找到這個引用表的定義資訊，並將這些定義

資訊載入到 dictionary cache裡。這個從磁碟上獲取資料字典資料的過程就叫做遞迴SQL（Recursive SQL）。通常來說，當我們執行一條新的SQL語句時，都會產生很多次的遞迴呼叫，也會產生很多的遞迴SQL。比如我們來下

面這個例子。
   SQL> set autotrace traceonly stat;
   SQL> select * from sharedpool_test;
   Statistics
   ----------------------------------------------------------
    185 recursive calls
    0 db block gets
    25 consistent gets
   …………

    從這裡可以很明顯看到執行該SQL產生了185次的遞迴呼叫，這185次的遞迴呼叫將表sharedpool_test相關的資訊，比如列定義、統計資訊 等，都載入到了dictionary cache裡。當我們再次執行該SQL時，會發現recursive

calls變成了0，因為dictionary cache裡已經包含解析SQL所需要參照的資料字典了。
  

轉儲dictionary cache
我們可以使用如下命令對dictionary cache進行轉儲。
ALTER SESSION SET EVENTS 'immediate trace name row_cache level N';

這裡的N可以取的值分別為：

1 轉儲dictionary cache的統計資訊 ;
2 轉儲hash表的彙總資訊 ;
8 轉儲dictionary cache中的物件的結構資訊;

如果對level 1進行轉儲，可以看到轉儲出來的內容，很明顯，就是v$rowcache裡的內容。每一種資料字典都有一行記錄來表示。比如有tablespace相關的資料字典等。

如果以level 2轉儲的話，可以看到類似如下的內容。這裡有23個hash表對dictionary cache中的物件進行管理，每個hash表都對應了一種資料字典，同時有一個名為row cache objects的latch來控制併發訪問。可以看到，

v$latch_children里名為“row cache objects”的記錄數量也是23。

ROW CACHE HASH TABLE: cid=0 ht=66BD90B0 size=32

…………

ROW CACHE HASH TABLE: cid=1 ht=66BD78B0 size=256

…………

ROW CACHE HASH TABLE: cid=22 ht=66DA5590 size=512
shared pool的內部管理機制
3.1解析SQL語句的過程
為了將使用者寫的可讀的SQL文字轉化為oracle認識的且可執行的語句，這個過程就叫做解析過程。
解析分為硬解析和軟解析。當一句SQL第一次被執行時必須進行硬解析。
當客戶端發出一條SQL語句（也可以是一個儲存過程或者一個匿名PL/SQL塊）進入shared pool時
（注 意，我們從前面已經知道，oracle對這些SQL不叫做SQL語句，而是稱為遊標（cursor）。因為oracle在處理SQL時，需要很多相關的輔 助資訊，這些輔助資訊與SQL語句一起組成了遊標），oracle首先將SQL文字轉化為

ASCII字元，然後根據hash函式計算其對應的hash值 （hash_value）。根據計算出的hash值到library cache中找到對應的bucket，然後比較bucket裡是否存在該SQL語句。
如果不存在，則需要按照我們前面所描述的，獲得 shared pool latch，然後在shared pool中的可用chunk連結串列（也就是bucket）上找到一個可用的chunk，然後釋放shared pool latch。在獲得了chunk以後，這塊chunk就可

以認為是進入了library cache。然後，進行硬解析過程。硬解析包括以下幾個步驟：
1) 對SQL語句進行語法檢查，看是否有語法錯誤。比如沒有寫from等。如果有，則退出解析過程。
2) 到資料字典裡校驗SQL語句涉及的物件和列是否都存在。如果不存在，則退出解析過程。
3) 將物件進行名稱轉換。比如將同名詞翻譯成實際的物件等。如果轉換失敗，則退出解析過程。
4) 檢查遊標裡使用者是否具有訪問SQL語句裡所引用的物件的許可權。如果沒有許可權，則退出解析過程。
5) 透過最佳化器建立一個最優的執行計劃。這一步是最消耗CPU資源的。
6) 將該遊標所產生的執行計劃、SQL文字等裝載進library cache的若干個heap中。
在 硬解析的過程中，程式會一直持有library cach latch，直到硬解析結束。硬解析結束以後，會為該SQL產生兩個遊標，一個是父遊標，另一個是子游標。父遊標裡主要包含兩種資訊：SQL文字以及最佳化 目標（optimizer

goal）。父遊標在第一次開啟時被鎖定，直到其他所有的session都關閉該遊標後才被解鎖。當父遊標被鎖定的時候是不能被交換出library cache的，只有在解鎖以後才能被交換出library cache，這時該父遊標對應的所有子

遊標也被交換出library cache。子游標包括遊標所有的資訊，比如具體的執行計劃、繫結變數等。前面圖四中看到的CHILDREN部分就是子游標所對應的handle的資訊。 子游標隨時可以被交換出library cache，當子游標被交

換出library cache時，oracle可以利用父遊標的資訊重新構建出一個子遊標來，這個過程叫reload。可以使用下面的方式來確定reload的比率：
SELECT 100*sum(reloads)/sum(pins) Reload_Ratio FROM v$librarycache;
一 個父遊標可以對應多個子遊標。子游標具體的個數可以從v$sqlarea的version_count欄位體現出來。而每個具體的子游標則全都在v$ sql裡體現。當具體的繫結變數的值與上次的繫結變數的值有較大差異（比如上次執行的

繫結變數的值的長度是6位，而這次執行的繫結變數的值的長度是200 位）時或者當SQL語句完全相同，但是所引用的物件屬於不同的schema時，都會建立一個新的子游標。
如果在bucket中找到了該SQL語句，則說明該SQL語句以前執行過，於是進行軟解析。軟解析是相對於硬解析而言的，如果解析過程中，可以從硬解析的步驟中去掉一個或多個的話，這樣的解析就是軟解析。軟解析分為以下三

種型別。
1) 第一種是某個session發出的SQL語句與library cache裡其他session發出的SQL語句一致。這時，該解析過程中可以去掉硬解析中的5和6這兩步，但是仍然要進行硬解析過程中的2、3、4步驟：也就是表名和列名檢查、名稱

轉換和許可權檢查。
2) 第二種是某個session發出的SQL語句與library cache裡該同一個session之前發出的SQL語句一致。這時，該解析過程中可以去掉硬解析中的2、3、5和6這四步，但是仍然要進行許可權檢查，因 為可能透過grant改變了該

session使用者的許可權。
3) 第三種是當設定了初始化引數session_cached_cursors時，當某個session對相同的cursor進行第三次訪問時，將在該 session的PGA裡建立一個標記，並且該遊標即使已經被關閉也不會從library cache中交換出去。這樣

，該session以後再執行相同的SQL語句時，將跳過硬解析的所有步驟。這種情況下，是最高效的解析方式，但是會消耗很大 的記憶體。
我們先來舉一個例子說明如果在解析過程中發生語法或語義錯誤時，在shared pool中是怎樣體現的。
SQL> select object_type fromm sharedpool_test111;
ORA-00942: 表或檢視不存在
然後我們以level 16轉儲library cache，並開啟轉儲檔案，找到相應的部分，如下圖八所示。可以看到，
該SQL 語句在語法上是錯誤的（from寫成了fromm），oracle仍然在shared pool中為其分配了一個chunk，然後該chunk進入library cache，並在library cache中分配了一個bucket，同時也生成了heap 0，但是該heap 0中不

存在相應的一些如dependency table等table的部分，以及data block的部分。我看到有些資料上說SQL語句是先進行語法分析，如果透過語法分析以後，則應用hash函式生成hash值，然後再去shared pool中分配chunk。實際

上從這個例項已經可以看出，這個說法是錯誤的。oracle始終都是先對SQL生成hash值（不論該SQL語法上是否正 確），再根據hash值到對應的可用chunk連結串列（也就是bucket）裡分配chunk，然後進入語法解析等解析過程。