1、Shared Pool的組成
** library cache：存儲共享的SQL和PL/SQL代碼（解析和編譯后的SQL和pl/sql blocks——procedures，Functions，packages，triggers和匿名的pl/sql塊）使用最近最少使用算法管理 （LRU），避免語句的重復解析。
** data dictionary cache：保存字典對象的信息
** user global area（UGA）：用于在沒有設置large pool時存放共享的server connection的信息。保存在Shared模式下session的信息。
當缺少data dictionary cache或library cache的代價比缺少database buffer cache的代價更高，所以對Shared pool的tuning有更高的優先權。在tuning Shared pool時主要關心library cache提高其hit ratio又更重要些。
當Shared pool過小，server為了管理有限的空間，需要消耗更多的cpu，從而引起爭用。
Shared pool從整體上可以通過參數shared_pool_size來調節。
2、library cache
在server查找是否有被緩沖的sql時，會先把statement轉換為ASCII文本，再進行hash函數的計算。
1）盡可能減少重復解析的次數：盡可能使code扁平化，使用bind variables；可以適當增大library cache的大小，從而減少由于cache不足造成的已經被解析的SQL被換出而引起重新解析的可能；如果在cache中保存的被解析的SQL相關聯的 schema object被修改，則該cache中的內容將不可用，所以要盡量避免這樣的事件發生。
2）避免library cache的碎片產生：為較大的內存需求保留一定的內存，主要通過shared_pool_reserved_size參數設置；將常用的較大的SQL和 PL/SQL對象pinning到內存中，避免其被換出；為Oracle Shared server設置large pool；盡量少使用較大的匿名blocks，用小的PL/SQL包的方法代替；在Oracle Shared Server中測量共享進程所用的session內存。
3）在v$librarycache中保存了每類數據保存在library cache中的統計信息。其中，三個字段較為重要：gets，顯示了相應item總的請求數量；pins，顯示了執行的次數；reloads顯示了被換出后重載的次數
4）調節library cache的診斷工具：
** v$sgastat
** v$librarycache
eg：sql> select namespace, gethitratio from v$librarycache; –獲得命中率，在OLTP中應該高于90%，如果沒有達到可以考慮以下方法：
a)提高應用程序代碼效率（通過綁定變量，避免SQL的硬解析）
b)增加共享池的大小（增加之前先通過v$sgastat查詢是free memory是否足夠大，有無增加共享池必要）
** v$sqltext：full SQL text
eg：sql> select sql_text, users_executing, executions, loads from v$sqlarea;
sql> select * from v$sqltext where sql_text like ’select %’;
** v$db_object_cache：緩沖的DB object，包括packages、tables或是SQL中參照的別名
** v$sqlarea：統計了所有的共享cursor和相應的sql的前1000個字符
** 如果重載的比率大于1%，需要考慮增大shared_pool_size。
sql> select sum(pins) “executions”, sum(reloads) “cache misses”, sum(reloads)/sum(pins) from v$librarycache;
在statspack report中，library cache activity (Instance state activity)的內容也顯示了此信息：parse count（total、hard、failure）
5）還需要明確的是多次的修改對象，也可能使library cache的內容被標記為invalid，從而造成reload-to-pins值增大。在v$librarycache中的invalidations 字段顯示了標記為invalid的次數。注意：analyze語句可用使標記為invalid。
sql>select namespace, pins, reloads, invalidations from v$librarycache;
可以查詢v$shared_pool_advice查看oracle建議使用共享內存，也可以通過oracle 的OEM查看共享內存圖。
6）與緩沖的執行計劃的相關的view：
** 動態性能視圖v$sql_plan可用于查看緩沖了的cursor的的執行計劃信息，它比plan_table表還多7個字段，兩者中的相同字段的值是一 致的。此外在v$sql視圖中添加了一個字段plan_hash_value，它為一個hash值，用于匹配執行計劃。與v$sqltext、 v$sql_plan、v$sqlarea中的hash_value字段對應。
7）測試applications：對應已經存在的application，先分配一個較大的shared_pool_size，開啟應用，計算當前使用的共享內存：
select sum(sharable_mem)
from v$db_object_cache
where type=’PACKAGE’ or type=’PACKAGE BODY’
type = ‘FUNCTION’ or type = ‘PROCEDURE’;
對于相關的SQL語句，需要通過v$SQLAREA查詢：
select sum(sharable_mem) from v$sqlarea where executions>5;
此外，也可以假設在每個user每打開一個cursor將增加250 bytes。這可以測試application在高峰期的shared pool的占用：
select sum(250* users_opening) from v$sqlarea;
在測試環境中，可以根據user的多少來估算打開的cursors數量。
select 250 * value bytest_per_user
from v$sesstat s, v$statname n
where s.statistic# = n.statistic# and n.name = ‘opened cursors current’ and s.sid=15;
這樣，在理想情況下，application中將占用的library cache大約是上述的總和加上少量的動態SQL。
8）large memory的存在必要性在于滿足較大的連續內存的需求，可以通過large_pool_size和 shared_pool_reservered_size來設置。一般會建議將其設置為shared_pool_size 的10%，如果設置為50%或以上，系統將報錯。
通過視圖v$shared_pool_reserved可以幫助tuning其大小。其中最好的目標是設置使request_misses（由于 large mem不足，通過LRU實現的flush）的長期統計結果接近0。還有一個procedure工具 dbms_shared_pool.aborted_request_threshold，用于限制shared pool在出現ORA-4031之前被flush
對于其調節：如果v$shared_pool_reserved中的request_misses值不為0并且不斷增加，需要考慮增加 shared_pool_reserved_size；如果request_misses為0，并且free_memory=>50%，考慮減少 shared_pool_reserved_size；v$shared_pool_reserved中的request_failures（顯示了沒有 memory用于滿足請求）>0并不斷增加，可以適當考慮減小shared_pool_reserved_size或增大 shared_pool_size。
9）將large object保存在內存中。
通過下面的語句可以查看在內存中已經cache了那些對象：
select * from v$db_object_cache
where sharable_mem > 10000 and (type=’PACKAGE’ or type = ‘PACKAGE BODY’ or
type = ‘FUNCTION’ or type = ‘PROCEDURE’) and kept = ‘NO’;
需要被pin入內存中的對象主要有：常用的較大的存儲對象，如standard、diutil包；編譯的常用的triggers；sequences。最 好在開機時就將其pin入內存中（我以為這里可以編寫適當的開機trigger）。這樣，既是使用命令alter system flush shared_pool時，也不會講這些object flush掉。
具體pin對象到內存的方法可以使用DBMS_SHARED_POOL.keep存儲過程。可以用unkeep方法解除其pin狀態。
10）對于匿名pl/sql塊：可以先在視圖v$sqlarea中找到相應的sql_text，并將其適當的改為packaged Functions來執行。
select sql_text from v$sqlarea where command_type = 47 and length(sql_text) > 500;
此外，可以適當的將這些匿名的PL/SQL blocks pin入內存，如下：
declare /* KEEP_ME */ x number;
begin x := 5;
end;
select address, hash_value from v$sqlarea where command_type = 47 and sql_text like ‘%KEEP_ME%’;
execute dbms_shared_pool.keep(‘address, hash_value’);
11)其他影響library pool的參數
** open_cursors：此參數定義了user進程可分配到的私有SQL area中可以引用的cursor的數量。這些私有SQL area將一直存在，直到cursor被關閉。所以應用中應該及時關閉不用的cursor。
** cursor_space_for_time：其為Boolean值，默認為false，如果設置為true，則共享 SQL areas中將不會把其標識為過期，直到其被關閉。不要將其設置為true，除非v$librarycache中的reload始終保持0時再考慮。當應 用中使用了form或大量的動態SQL，應設置為false。
** session_cached_cursors：此參數用于同一個user會經常解析同一個sql的情況。這會經常出現在form的應用中。當設置了該值，會將關閉的游標的解析仍然cache在內存中，用于后面的軟解析。
為了驗證設置是否恰當，可以查證v$sesstat中的”session cursor cache hits”和”parse count”的值，如果parses 結果的hits比較小，則考慮增加該值，但是，其增加的開支來自于memory。
3、data dictionary cache及其術語、tuning：
1）了解一些術語：
gets：對某object請求的總次數；
getmisses：顯示了data請求造成cache misses的次數
當instance被剛剛startup時，dictionary cache中是空的，所以任何sql都會引起getmisses的增加，但是隨著大量data被讀入cache，getmisses也會減少。最終，將達到一個穩定的平衡狀態。
對data dictionary的調節只能通過間接的調節shared_pool_size。
診斷data dictionary的工具有：
** 視圖v$rowcache：主要需要關注的字段是parameter、gets和getmisses
** 在statspack的report中，有相關的內容，其中每個數據字典項的misses的百分比大多數應該< 2%,整個Dictionary Cache應該< 15%。report中的cache usage是cache的實體被使用的次數。
sql>select 1-sum(getmisses)/sum(gets) "dta dic hitration" from v$rowcache;
pct SGA是用于data dictionary cache的SGA的比率。
查看getmisses比gets的總比率的方法：
select parameter, gets, getmisses from v$rowcache where parameter=’dc_objects’ or parameter=’dc_synonyms’;
應盡量將該比率降低到<15%的情況。否則，考慮增加shared_pool_size。
4、UGA和Oracle shared Server
在不同的Oracle Server模式下，UGA的位置也不同，具體如下圖：
如果使用的Oracle Shared Server模式，并且沒有設置large pool，則user的session data和cursor state將存儲在shared pool中，而非dedicated Server模式中的PGA中。sort area和private SQL area包含在session data中。在這種模式的Server中應增加shared_pool_size，PGA將變小。
相關的查詢：
select sum(value) || ‘ bytes’ “total session memory” from v$mystat, v$statname
where v$statname.name=’session uga memory’ and v$mystat.statistic#=v$statname.statistic#;
select sum(value) || ‘ bytes’ “total session memory” from v$sesstat, v$statname
where v$statname.name=’session uga memory’ and v$sesstat.statistic#=v$statname.statistic#;
select sum(value) || ‘ bytes’ “total session memory” from v$mystat, v$statname
where v$statname.name=’session uga memory max’ and v$mystat.statistic#=v$statname.statistic#;
5、Large Pool
它是在shared_pool_size之外被分配的，設置它的主要好處在于：
1.它可以用于給I/O服務器進程(dbwr_io_salves,操作系統不支持異步IO，用它來模擬異步)
2.backup、resort進程提供所需的較大的內存，
3.共享服務器的會話內存
4.并行查詢消息處理
從而降低shared pool產生碎片的可能及其帶來的開支。
它的大小由參數large_pool_size決定。
sql>show parameter large_pool_size
它的使用情況：
sql>select * from v$sgastat where pool='large pool'