【Oracle】ORA-04031: 共有メモリを割り当てられません 完全ガイド｜共有プール断片化・V$SGASTAT調査・DBMS_SHARED_POOL.KEEP・予防策まで解説

ORA-04031: unable to allocate 4096 bytes of shared memory
("shared pool","SELECT emp_id, emp_name...","SQLA","sql area")

-- フィールドの構成:
-- ① 要求バイト数   : 4096 bytes
--    確保しようとした連続メモリサイズ。大きいほど深刻
-- ② プール名       : "shared pool"
--    shared pool / large pool / java pool / streams pool のどれかを確認
-- ③ ヒープ名       : "SELECT emp_id, emp_name..."
--    確保しようとした対象。SQLテキストが入ることが多い
-- ④ タイプ/サブタイプ: "SQLA" / "sql area"
--    sql area = SQL解析結果
--    PLSQL MCODE = コンパイル済みPL/SQLコード
--    KGLOB     = Library Cache Object（カーソルや定義オブジェクト）

-- 共有プール全体のサイズと空き容量を確認
SELECT
    name,
    ROUND(bytes / 1024 / 1024, 2) AS mb
FROM V$SGASTAT
WHERE pool = 'shared pool'
  AND name IN ('free memory', 'library cache', 'dictionary cache',
               'sql area', 'PLSQL DIANA', 'PLSQL MCODE')
ORDER BY mb DESC;

-- 空き容量が極端に少ない（全体の5%未満）場合はサイズ不足
-- 空きが十分あっても発生する場合は断片化が原因

-- SGA全体のコンポーネント別サイズ
SELECT
    name,
    ROUND(bytes / 1024 / 1024, 2) AS mb
FROM V$SGA
ORDER BY bytes DESC;

-- 現在の SHARED_POOL_SIZE パラメータ
SELECT NAME, VALUE, DESCRIPTION
FROM V$PARAMETER
WHERE NAME IN (
    'shared_pool_size',
    'sga_target',       -- AMM/ASMM 使用時のSGA合計
    'memory_target',    -- AMM 使用時のメモリ合計
    'cursor_sharing'
);

-- ハードパース（VERSION_COUNT が多い or PARSE_CALLS が多い）SQL を特定
-- VERSION_COUNT: 同じSQLの異なる実行計画（カーソル）の数が多い = 問題の兆候
SELECT
    sql_id,
    version_count,
    parse_calls,
    executions,
    ROUND(cpu_time / 1000000, 2) AS cpu_sec,
    ROUND(elapsed_time / 1000000, 2) AS elapsed_sec,
    SUBSTR(sql_text, 1, 80)    AS sql_preview
FROM V$SQL
WHERE version_count > 10   -- バージョン数が多いSQL
   OR (parse_calls > 100 AND executions < parse_calls * 0.5)  -- 実行より解析が多い
ORDER BY version_count DESC
FETCH FIRST 20 ROWS ONLY;

-- リテラル値の違いだけで大量の SQL が生成されていないか確認
-- sql_text の末尾の定数部分だけが違うSQLが多ければ、バインド変数未使用が原因
SELECT
    SUBSTR(sql_text, 1, 60) AS sql_prefix,
    COUNT(*) AS cursor_count
FROM V$SQL
GROUP BY SUBSTR(sql_text, 1, 60)
HAVING COUNT(*) > 50
ORDER BY cursor_count DESC;

-- X$KSMSP: 共有プールの最小割り当て単位（チャンク）の状態
-- DBA権限が必要
SELECT
    ksmchcls  AS status,    -- free=空き / recr=再利用可 / R-free=解放済み
    ksmchsiz  AS chunk_size,
    COUNT(*)  AS chunks,
    ROUND(SUM(ksmchsiz) / 1024 / 1024, 2) AS total_mb
FROM X$KSMSP
GROUP BY ksmchcls, ksmchsiz
HAVING ksmchcls = 'free'
ORDER BY ksmchsiz DESC
FETCH FIRST 20 ROWS ONLY;

-- 最大の空きチャンクサイズ（これが ORA-04031 の要求サイズより小さいと失敗）
SELECT MAX(ksmchsiz) AS max_free_chunk_bytes
FROM X$KSMSP
WHERE ksmchcls = 'free';

-- 動的パラメータのため、サービスを止めずに変更できる
ALTER SYSTEM SET SHARED_POOL_SIZE = 512M SCOPE = BOTH;
-- SCOPE = BOTH: 即時反映 + spfile に保存（推奨）

-- AMM (Automatic Memory Management) 使用中の場合
-- sga_target や memory_target を増やす
ALTER SYSTEM SET SGA_TARGET = 2G SCOPE = BOTH;

-- ASMM (Automatic Shared Memory Management) 使用中の場合:
-- sga_target > 0 かつ shared_pool_size に下限を設定する
ALTER SYSTEM SET SHARED_POOL_SIZE = 256M SCOPE = BOTH;
-- → Oracle が SGA_TARGET の範囲内で自動調整しつつ、最低 256MB は共有プールに確保する

-- 現在の実際の共有プールサイズ（V$SGA_DYNAMIC_COMPONENTS で確認）
SELECT component, ROUND(current_size / 1024 / 1024, 2) AS current_mb,
       ROUND(min_size / 1024 / 1024, 2) AS min_mb,
       ROUND(max_size / 1024 / 1024, 2) AS max_mb
FROM V$SGA_DYNAMIC_COMPONENTS
WHERE component LIKE '%shared pool%';

-- 大型パッケージを共有プールに固定する
-- 引数: (名前, フラグ)
-- フラグ 'P': パッケージ・パッケージ本体（デフォルト・最もよく使う）
-- フラグ 'C': SQL カーソル（'address,hash_value' 形式、V$SQL から取得）
BEGIN
    DBMS_SHARED_POOL.KEEP('DBMS_STANDARD', 'P');    -- 標準パッケージを固定
    DBMS_SHARED_POOL.KEEP('MY_BIG_PACKAGE', 'P');   -- 自作の大型パッケージ
END;
/

-- 固定されているオブジェクトを確認
SELECT name, type, kept
FROM V$DB_OBJECT_CACHE
WHERE kept = 'YES'
ORDER BY sharable_mem DESC;

-- KEEP の対象として優先度が高いオブジェクト（サイズの大きいもの）
SELECT name, type,
       ROUND(sharable_mem / 1024, 2) AS sharable_kb,
       kept
FROM V$DB_OBJECT_CACHE
WHERE type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
  AND kept = 'NO'
ORDER BY sharable_mem DESC
FETCH FIRST 20 ROWS ONLY;
-- → サイズが大きく頻繁に使われるオブジェクトを KEEP する候補として使う

-- 固定を解除する
BEGIN
    DBMS_SHARED_POOL.UNKEEP('MY_BIG_PACKAGE', 'P');
END;
/

-- 緊急時: 共有プールを全クリアして断片化を解消する
-- → 直後は全SQL再解析のためパフォーマンスが一時低下する
ALTER SYSTEM FLUSH SHARED_POOL;

-- 実行後は負荷が落ち着くまで V$SYSSTAT でハードパース数を監視する
SELECT name, value
FROM V$SYSSTAT
WHERE name IN ('hard parses', 'soft parses', 'parse count (total)');

-- NG: リテラルSQL（1万種類のIDに対して1万通りのSQLが共有プールに保存される）
SELECT * FROM employees WHERE employee_id = 1;
SELECT * FROM employees WHERE employee_id = 2;
SELECT * FROM employees WHERE employee_id = 3;
-- V$SQL に 3 行（= 3つの異なるSQL）が登録される

-- OK: バインド変数を使う（同じSQLが共有プールの1エントリを使いまわす）
SELECT * FROM employees WHERE employee_id = :emp_id;
-- :emp_id に 1, 2, 3 … を渡しても V$SQL の 1 エントリのみ使う

-- 現在リテラルSQLが何件あるか（同一パターンで何件の SQL が登録されているか）
SELECT
    SUBSTR(sql_text, 1, 60) AS sql_prefix,
    COUNT(*)                AS literal_count
FROM V$SQL
GROUP BY SUBSTR(sql_text, 1, 60)
HAVING COUNT(*) > 100    -- 100件以上の類似SQLがある = バインド変数未使用の疑い
ORDER BY literal_count DESC
FETCH FIRST 10 ROWS ONLY;

-- 現在の設定確認（デフォルト: EXACT）
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'cursor_sharing';

-- FORCE: Oracle がリテラルをバインド変数に自動置換してカーソルを共有する
ALTER SYSTEM SET CURSOR_SHARING = FORCE SCOPE = BOTH;

-- 注意事項:
-- ・ヒストグラムやデータ分布によっては最適でない実行計画が選ばれる場合がある
-- ・統計情報のバインド変数ピーキングの影響を受けやすくなる
-- ・根本解決にはならないため、アプリケーション改修後は EXACT に戻すことを推奨
ALTER SYSTEM SET CURSOR_SHARING = EXACT SCOPE = BOTH;   -- 正常な設定に戻す