SHARED POOL總結

本文試圖對shared pool的內部機制做一個總結，目的是加強對相關內容的瞭解。
shared pool記憶體的幾個特徵：
a.以CHUNK為單位，CHUNK大小不等，每個chunk跟x$ksmsp中每條記錄有一一對應關係
b.當會話結束，相關記憶體空間並不全部釋放，因為無法預知這些資訊會不會被後來的會話用到
c.不能被PAGE OUT到系統的磁碟交換區，AGE OUT的是一些可重建的資訊

[@more@]

1、shared pool記憶體結構及記憶體分配方式
shared pool 包含dictionary & sql區、保留區、永久區
保留區儲存大物件，大小由shared_pool_reserved_size決定，預設為shared pool大小的5%，當物件大小大於引數_shared_pool_reserved_min_alloc值時，物件將被儲存在保留區。
永久區存放程式、會話、ENQUEUE和事務等資訊
shared pool記憶體邏輯上由多個extent組成，每個extent由多個chunk組成，chunk大小不同，這一點不同於data block
當需要新的記憶體空間的時候，oracle就分配新的chunk
shared pool的記憶體管理依賴於三個list:free list(空閒chunk)、LRU list（沒有被pin住的可重建的chunk）和reserved free list(保留區的freelist)
shared pool如何分配空閒chunk?
首先在freelist上搜尋所需要大小的chunk，如果沒有空閒空間，將在LRU list上搜尋所需要大小的chunk,如果物件比較大，將搜尋reserved free list
當這三個搜尋都失敗的時候，就出ORA-04031的錯誤。有時候shared pool有空閒空間仍然有可能出現ORA-04031的錯誤。
shared pool的這三個連結串列將所有的chunk組織起來。
2、library cache記憶體結構
基本結構：hash bucket->chain->handle->object
對於每個SQL語句，使用HASH函式得到HASH值，再透過HASH值找到相關的hash bucket
library cache由多個HANDLE+OBJECT組成
handle儲存LCO的定義，通常是SQL語句本身以及相關資訊
LCO儲存依賴表（所有的依賴資訊），子表（子LCO資訊，也是由HANDLE和LCO組成，代表了相同的SQL語句，但是不同的執行計劃,對應於v$sqlarea的version_count）和DATA BLOCKS（與執行相關的資訊，比如執行計劃）
3、軟解析和硬解析
軟解析：對於提交的語句，在語法和許可權檢查後，運用hash函式找到相關的hash bucket，獲取library cache latch來保護hash bucket，然後搜尋LCO，找到後獲取library cache lock或者library cache pin,並執行相關語句。
硬解析：當軟解析失敗，註冊新的LCO，並獲取library cache lock/pin，生成執行計劃，獲取shared pool latch，找到相關的空閒chunk.
硬解析多會導致很長的hash chain,記憶體碎片。
軟解析多會導致對hash chain的過多訪問和library cache latch的爭用。
如何查詢parse次數多的SQL語句？
select sql_text, parse_calls, executions from v$sqlarea
where parse_calls > 100 and executions < 2*parse_calls;
4、library cache latch和shared pool latch
當event為library cache latch,v$session_wait的p1為latch的地址，P2為LATCH#，P3為請求次數。
_KGL_LATCH_COUNT用於控制library cache latches的個數，其數量通常為大於cpu_count的最小質數
檢視Library cache latch的個數
SQL> select count(*)from v$latch_children where name='library cache';
Library cache latch爭用有時候出現在具有高version_count的sql語句中，這些sql的表面字義是相同的如select * from test，但可能執行計劃不同，這些sql具有相同的雜湊值和不同的版本，oracle需要去比較該語句和現有版本，這期間是一直持有latch的，這可能會造成其他程式無法獲得該latch。-------見第2小節：library cache記憶體結構
檢視高version_count的sql語句：
select substr(sql_text,1,40),version_count
from v$sqlarea where version_count > 10 order by version_count desc;
5、library cache lock和library cache pin
library cache lock:當訪問和修改LCO時獲取該鎖，由LCO的handle獲取。
DDL操作需要排它的library cache lock，library cache lock的獲取先於library cache pin
SQL PARSE階段為共享鎖
SQL執行階段為NULL模式的鎖
library cache pin:當SQL執行的時候保護LCO中的與執行相關的資訊，由LCO本身獲取
語句執行階段為共享鎖
DDL或者HARD PARSE為排它鎖

相關檢視：DBA_KGLLOCK(X$KGLLK+X$KGLPN) DBA_DDL_LOCKS(X$KGLLK+V$SESSION+X$KGLOB)

軟解析的時候給LCO加NULL型library cache lock和共享的library cache pin，給子LCO加共享型library cache lock和共享的library cache pin
硬解析的時候給LCO加NULL型library cache lock和排它的library cache pin，給子LCO加共享型library cache lock和共享的library cache pin

為什麼LCO在SQL parse之後會有null模式的library cache lock?這是因為一旦物件的定義改變，那麼ORACLE要喚醒NULL模式鎖的持有者通知它。

硬解析和DDL都可能導致library cache pin等待，比如在系統繁忙時候flush shared pool.

如何找到library cache pin鎖的持有者?

對OBJECT:

select session_id sid,lock_type type,lock_id1 object_name from dba_lock_internal where mode_requested<>'None' and mode_requested<>mode_held and session_id in (select sid from v$session_wait where wait_time=0 and event like 'library cache pin%');

對PIN HOLDER:

select sid holder,kglpnmod held,kglpnreq req,event,wait_time from v$kglpn,v$session where kglpnhdl in (select p1raw from v$session_wait where wait_time=0 and event like 'library cache pin%') and kglpnmod<>0 and v$session.saddr=x$kglpn.kglpnuse;