Shared pool 共有プール

共有プール

---

共有プール（shared pool）はヒープ（heap memory）構造を取ります。
共有プールはその領域を複数のチャンクに分けて管理・使用します。

共有プールを使用するプロセス(SQLの実行やバックグラウンドプロセス)はメモリの空き（フリーチャンク）を探し（フリーリストのサーチ）
必要な領域の確保および、必要に応じてフリーチャンクの分割を行うため、共有プールラッチ（latch: shared pool ）を獲得してから実施します。

 

 

共有プールラッチ（latch: shared pool ）
共有メモリを使うために領域の空き確認＆確保の際に他の人（プロセス）が状態を変えないようにロックすること

共有プールラッチ（latch: shared pool ）を獲得する過程で競合が発生した場合共有プールラッチ（latch: shared pool ）の待機が発生します。
 

共有プールラッチ（latch: shared pool ）が起きる原因

１．ハード・パース過多による共有プールラッチの取り合い
　ハード・パースにより共有プールの使用が増え、共有プールの取得（チャンク）が発生
　→チャンクが増える→共有プールの断片化
　　→フリーリストのサーチが長くなる
　　→ラッチ獲得待ちが増える
　　→latch: shared poolの待機が発生する。

対策１．共有プールをサブブールに分ける。

　ラッチは共有プールに一つだけなので、その一つのラッチを取り合うことでlatch: shared poolの待機が発生する。
　そのため、対策の一つとしてサブプールに分けることによりラッチの取り合い（待機）が減る。
　
　サブプールはそれ自体が独立した共有プール（shared pool）に管理され、
　独自のフリーリスト（Freelist）、LRUリスト、共有プールラッチ（latch: shared pool ）を有します。

　サブプールの条件

　CPUの数が4以上あり、共有プールが250MB以上の場合

　サブブールの作成方法
　_KGHDSIDX_COUNT隠しパラメータを使用
　_KGHDSIDX_COUNTの値だけサブプールを作成して共有プールを管理します。

対策2．ハード・パースを減らす。
　
　チャンクの取得を減らすことにより、ラッチの取り合い（待機）を減らす。
　

 

---

共有プールのコンポーネントのサイズ（状態）を監視する。

一日毎とかv$sgastatからコンポーネントのサイズを記録して、増え具合を見る。

コンポーネントは、公開されていない情報もあるが、わかるものは区別しておいた。

 

格納用のテーブル

create table wk_t_vsgastat
(
    get_date      date
   ,POOL          VARCHAR2(12)
   ,Compornent    VARCHAR2(24)
   ,NAME          VARCHAR2(26)
   ,BYTES         NUMBER
   ,CON_ID        NUMBER
)
;

格納用テーブルにデータを入れるSQL

insert
    into sysdba.wk_t_vsgastat 
    select
        TO_CHAR(SYSDATE,'YYYY.MM.DD HH24:MI:SS') as NOW_TIME
       ,pool
       ,case name WHEN 'SQLA'                  THEN 'library cache' --SQL実行する際に利用する領域
                  WHEN 'kglsim object batch'   THEN 'library cache' --共有プールアドバイザに関連した領域
                  WHEN 'kglsim heap'           THEN 'library cache' --共有プールアドバイザに関連した領域
                  WHEN 'KGLH0'                 THEN 'library cache' --ライブラリキャッシュオブジェクト
                  WHEN 'KGLHD'                 THEN 'library cache' --ライブラリキャッシュ・ハンドルに関する情報を格納
                  WHEN 'KGLDA'                 THEN 'library cache' --ライブラリキャッシュデータブロック
                  WHEN 'KGLS'                  THEN 'library cache' --※公開可能な情報なし
                  WHEN 'KGLNA'                 THEN 'library cache' --ライブラリキャッシュ・ハンドルの名前
                  WHEN 'KGLSG'                 THEN 'library cache' --※公開可能な情報なし
                  WHEN'kglsim hash table bkts' THEN 'library cache' --※公開可能な情報なし
                  ELSE ''
        END as Compornent
       ,name
       ,bytes
       ,CON_ID
       from v$sgastat
    where
        pool = 'shared pool'
    order by bytes desc 
;

---

共有カーソル

 

共有カーソル統計情報

合計解析 ：V$SQL の「PARSE_CALLS」の合計値
ハード解析 ：V$SQL の「LOADS」の合計値
子カーソル ：V$SQL のレコード件数
ロード済計画 ：V$SQL の「LOADED_VERSIONS」の合計値
無効化 ：V$SQL の「INVALIDATIONS」の合計値
最大カーソル・サイズ(KB) ：V$SQL の「SHARABLE_MEM」の最大値
すべてのカーソル・サイズ(KB) ：V$SQL の「SHARABLE_MEM」の合計値
初回ロード時間 ：V$SQL の「first_load_time」の最小値
最新ロード時間 ：V$SQL の「last_load_time」の最大値
 

SELECT MAX(s.sharable_mem) "最大カーソル・サイズ(KB)",
SUM(s.sharable_mem) "すべてのカーソル・サイズ(KB)",
SUM(s.loaded_versions) "ロード済計画",
SUM(s.loads) "ハード解析",
MIN(to_date(s.first_load_time, 'YYYY-MM-DD/HH24:MI:SS')) "初回ロード時間",
SUM(s.invalidations) "無効化",
SUM(s.parse_calls) "合計解析",
MAX(to_date(s.last_load_time, 'YYYY-MM-DD/HH24:MI:SS')) "最新ロード時間",
count(1) "子カーソル",
FROM v$sql s,
sys."_BASE_USER" u,
sys."_BASE_USER" u1
WHERE s.sql_id = '<対象のSQL_ID>'
AND s.parsing_schema_id = u.user#(+)
AND s.parsing_user_id = u1.user#(+)
GROUP BY s.sql_id, s.plan_hash_value;