Oracle記憶體分配與使用小記（二）Shared Pool and Large Pool

配置和使用Shared Pool and Large Pool

[@more@]

包括PL/SQL blocks and SQL，dictionary cache data和其他。正確配置的好處在於：
１、 避免SQL重複parse，減少CPU資源使用
２、 減少latch資源爭用
３、 減少IO，因為dictionary elements避免了訪問磁碟

Shared Pool Concepts
包括library cache 和dictionary cache，根據需要自動增加或減少。
　　library cache包括parsed or compiled的PL/SQL blocks 和 SQL以及JAVA類。
　　dictionary cache 存放了來自 data dictionary的資料。比如usernames, segment information, profile data, tablespace information, and sequence numbers等，在解析和編譯SQL時引用．
　　Shared Pool 的cache miss開銷比buffer cache的大很多，所以要謹慎設定。
　　共享池記憶體分配以chunks（大塊）為單位，這樣允許大物件（5K）直接CACHE到記憶體中，而不需要申請一段連續的空間，這樣可以減少碎片帶來的空間浪費．
　　reserved pool：Shared Pool隔離出來的一小段，用來存放大於５ｋ的大物件．

Hard / soft parse:
　　Soft parse使用的資源包括CPU 和library cache latch gets
　　Hard parse是指要解析的SQL沒有在library cache中，或者執行的時候發現解析過的SQL已經aged out，就是離開了library cache，稱為Library cache misses．使用的資源包括額外的CPU, library cache latch gets, 以及shared pool latch gets.

Using the Shared Pool Effectively
　　Shared Cursors
　　SQL中儘量指定表的owner，而不使用public synonyms.，可以significantly reduces the number of entries in the dictionary cache．令一種方法是使用同樣的USERID訪問資料庫．儘量使用儲存過程．
　　避免在高峰時期執行DDL，因為這樣會使相關連的SQL失效，要重新編譯．
　　Cache Sequence Numbers可以減少dictionary cache locks，比如：ALTER SEQUENCE customers_seq CACHE 5; 事先cache 5個．2~28

Sizing the Shared Pool
不能太大，因為需要維護共享的結構，同時使得SQL的老化的代價更高，帶來大量的管理開銷，導致CPU的嚴重問題．在充分利用繫結變數的系統，通常100m(1G)~300m(8G), erp 可以達到500m．

Library Cache 的配置：
可以透過以下３點判斷：
1)V$LIBRARYCACHE：
RELOAD列反映了之前已經CACHE的物件，後來AGED OUT了，然後又被re-load (re-parsing)的SQL數量．因為其object handle已經建立，所以會有這個統計結果．期望值應該為０．
INVALIDATIONS 列反映了物件失效的次數，導致的原因比如DDL操作等．系統高峰時期的期望值應該為０．如果過大，應該減少高峰時期DDL操作，或者適當減小共享池配置．
PINS：The number of times a PIN was requested for objects of this namespace
PINHITS：The number of times all of the metadata pieces of the library object were found in memory
SELECT namespace, pins, pinhits, reloads, invalidations
  FROM V$LIBRARYCACHE
 ORDER BY namespace;

NAMESPACE             PINS    PINHITS    RELOADS INVALIDATIONS
--------------- ---------- ---------- ---------- -------------
SQL AREA          21536413   21520516      11204             2
．．．
SQL AREA部分，執行了21536413次，11204次導致library cache miss，需要reparse SQL或者reload定義；２次INVALIDATIONS，同樣導致library cache miss
Library Cache Hit Ratio = sum(pinhits) / sum(pins)
2)V$SGASTAT:
高峰時期檢查pool=’shared pool’ and name=’free memory’值，應該儘量低．
3)Library cache hit ratio：
考察hard parsing rate的大小，是否有shared pool latch 和 library cache latch的競爭．

Shared Pool Advisory Statistics
V$SHARED_POOL_ADVICE.ESTD_LC_TIME_SAVED:表示Parse time saved
V$LIBRARY_CACHE_MEMORY: library cache中各個名稱空間的具體記憶體

Dictionary Cache的配置：
通常shared pool滿足了library cache後，dictionary cache也會被滿足．
Instance剛啟動的時候，dictionary cache沒有任何的資料．任何的SQL都會導致cache miss.逐漸的，資料越多，miss越小．
V$ROWCACHE記錄了library cache中每種資料字典item型別的統計資訊：
Parameter：資料字典item型別
Gets: 對這種型別資料的請求次數．
Getsmiss:請求失敗，需要IO讀取磁碟
Modifications: 資料被修改(update, insert , delete)的次數

Interpreting Shared Pool Statistics
增大shared_pool_size的同時，考慮增加open_cursors引數（cursors permitted for a session）．
如果reloads接近0，並且有大量的free memory,可以適當減小shared_pool_size

使用Large Pool
Large pool沒有LRU鏈，oracle不會將物件aged out．Large pool用於以下情況：
Parallel query並行查詢，Recovery Manager，shared server

Tuning the Large Pool and Shared Pool for the Shared Server Architecture
    使用large pool to allocate the shared server-related User Global Area (UGA),配置LARGE_POOL_SIZE，最小300k，查詢v$sgastat
    Even though use of shared memory increases with shared servers, the total amount of memory use decreases. This is because there are fewer processes; therefore, Oracle uses less PGA memory with shared servers when compared to dedicated server environments.
For best performance with sorts using shared servers, set SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE to the same value. This keeps the sort result in the large pool instead of having it written to disk.
    V$SESSTAT:
    Session UGA memory: The value of this statistic is the amount of memory in bytes allocated to the session. (If the sessions are connected to dedicated server processes, then this memory is part of the memories of the user processes. If the sessions are connected to shared server processes, then this memory is part of the shared pool.可以根據這個值來配置large pool)
    Session UGA memory max: The value of this statistic is the maximum amount of memory in bytes ever allocated to the session.

PRIVATE_SGA：
來限制沒個session從SGA中得到多少記憶體，很少用到．

使用CURSOR_SPACE_FOR_TIME（……）
配置Reserved Pool（……）
Keeping Large Objects to Prevent Aging（……）
CURSOR_SHARING for Existing Applications（……）