Shared Pool 的轉儲與分析

使用如下命令可以對共享池Library Cache資訊進行轉儲分析：

ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level LL';

其中LL代表Level級別，對於9.2.0及以後版本，不同Level含義如下：

·Level=1，轉儲Library Cache統計資訊；
·Level=2，轉儲Hash Table概要；
·Level=4，轉儲Library Cache物件，只包含基本資訊；
·Level=8，轉儲Library Cache物件，包含詳細資訊（如：child references、pin waiters等）；
·Level=16，增加heap sizes資訊；
·Level=32，增加heap資訊。

Library Cache由一個Hash表組成，而Hash表是一個由Hash Buckets組成的陣列，每個Hash Bucket都包含Library Cache Handle的一個雙向連結串列。Library Cache Handle指向Library Cache Object和一個引用列表。Library Cache物件進一步分為依賴表、子表和授權表等。

通過以下命令對Library Cache進行轉儲：

alter session set events 'immediate trace name LIBRARY_CACHE level 4';

接下來進一步討論一下Shared Pool的內容儲存。先進行相應的查詢，獲得測試資料：

sys@NEI> conn tq/tq
Connected.
tq@NEI> create table emp as select * from scott.emp;
Table created.
tq@NEI> conn / as sysdba
Connected.
tq@NEI> startup force   
sys@NEI> conn scott/tiger
Connected.
scott@NEI> select * from emp;
scott@NEI> conn tq/tq
Connected.
tq@NEI> select * from emp;

tq@NEI> col sql_text for a30
tq@NEI> select sql_text,version_count,hash_value,to_char(hash_value,'xxxxxxxxxx') HEX,address
  2  from v$sqlarea where sql_text like 'select * from emp%';
SQL_TEXT                       VERSION_COUNT HASH_VALUE HEX         ADDRESS
------------------------------ ------------- ---------- ----------- --------
select * from emp                          2 1745700775    680d47a7 43A673D0

tq@NEI> select sql_text,username,address,hash_value,to_char(hash_value,'xxxxxxxxxx') HEX_HASH_VALUE,
  2  child_number,child_latch
  3  from v$sql a,dba_users b
  4  where a.parsing_user_id=b.user_id and sql_text like 'select * from emp%';
SQL_TEXT                       USERNAME   ADDRESS  HASH_VALUE HEX_HASH_VA CHILD_NUMBER CHILD_LATCH
------------------------------ ---------- -------- ---------- ----------- ------------ -----------
select * from emp              TQ         43A673D0 1745700775    680d47a7            1           1
select * from emp              SCOTT      43A673D0 1745700775    680d47a7            0           1

這裡可以看出v$sqlarea和v$sql兩個檢視的不同之處，v$sql中為每一條SQL保留一個條目，而v$sqlarea中根據sql_text進行group by，通過version_count計運算元指標的個數。

在以上兩次查詢中，兩條SQL語句因為其程式碼完全相同，所以其ADDRESS、HASH_VALUE也完全相同。這就意味著，這兩條語句在共享池中的儲存位置是相同的（儘管其執行計劃可能不同），程式碼得以共享。在SQL解析過程中，Oracle將SQL文字轉換為相應的ASCII數值，然後根據數值通過Hash函式計算其HASH_VALUE，再通過HASH_VALUE在Shared Pool中尋找是否存在相同的SQL語句，如果存在則進入下一步驟；如果不存在則嘗試獲取Shared Pool Latch，請求記憶體，儲存該SQL程式碼。

在這裡有一個問題需要說明一下，因為大小寫字母ASCII值是不同的，所以Oracle會把大小寫不同的程式碼作為不同的SQL來處理。看一下測試：

tq@NEI> select * from scott.dept;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

tq@NEI> select * from scott.DEPT;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

tq@NEI> select sql_text,hash_value from v$sql where sql_text like 'select * from scott%';
SQL_TEXT                       HASH_VALUE
------------------------------ ----------
select * from scott.DEPT       3411718958
select * from scott.dept        911793802

注意到以上的輸出，僅僅是大小寫的不同使得原本相同的SQL語句變成了兩條“不同的程式碼”，所以從這裡可以看出，SQL的規範編寫非常重要。

SQL解析首先要進行的是語法解析，語法無誤後進行下一個步驟，進行語義分析，在此步驟中，Oracle需要驗證物件是否存在、相關使用者是否具有許可權、引用的是否是相同的物件。

對於先前的查詢，實際上emp表來自不同的使用者，那麼SQL的執行計劃也就不同了（當然影響SQL執行計劃的因素還有很多，包括優化器模式等），通過物件依賴關係可以看到這個不同：

tq@NEI> col SQL_TEXT for a30
tq@NEI> col TO_OWNER for a10
tq@NEI> col TO_NAME for a10
tq@NEI> select a.*,to_char(to_hash,'xxxxxxxxxx') Hex_HASH_VALUE
  2  from v$object_dependency a where to_name='EMP';
FROM_ADD  FROM_HASH TO_OWNER   TO_NAME    TO_ADDRE    TO_HASH    TO_TYPE HEX_HASH_VA
-------- ---------- ---------- ---------- -------- ---------- ---------- -----------
43A5FD00 3494121331 TQ         EMP        43A604F8  127687888          2     79c5cd0
43A673D0 1745700775 SCOTT      EMP        43A67194 3371416969          2    c8f3bd89

回憶一下前面介紹過的Buffer Cache的管理，其中Bucket→BH（buffer header）→Buffer的管理方式與以上Library Cache的管理原理完全類似。

Library Cache Handle可以被看作庫快取物件的概要資訊，Handle上存有指標指向Library Cache Object，Handle中還包含物件名、namespace、時間戳、引用列表、鎖定物件及pin物件列表等資訊。這裡還需要說明的是Handle上的指標指向的是Library Cache Object的Heap 0，庫快取物件可能佔用多個記憶體Heap，Heap 0則記錄了控制資訊，包括物件型別、物件依賴表、指向其他Heap的指標等。

至於Dictionary Cache資訊則可以通過如下命令進行轉儲：

ALTER SESSION SET EVENTS 'immediate trace name row_cache level N';

這裡的N可以取的值如下：

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8183550/viewspace-659598/，如需轉載，請註明出處，否則將追究法律責任。