【Oracle】SGA・PGAメモリ管理完全ガイド｜AMM・ASMM・メモリ設定・V$SGASTAT・V$PGASTAT でのチューニングまで解説

-- 主要メモリパラメータを確認
SELECT name, value, description
FROM V$PARAMETER
WHERE name IN (
    'memory_target',
    'memory_max_target',
    'sga_target',
    'sga_max_size',
    'pga_aggregate_target',
    'pga_aggregate_limit',
    'db_cache_size',
    'shared_pool_size'
)
ORDER BY name;
-- memory_target > 0 → AMM モード
-- sga_target > 0、memory_target = 0 → ASMM モード
-- 両方 0 → 手動管理モード

-- SGA 合計を 4GB、PGA を 2GB に設定（ASMM モード）
ALTER SYSTEM SET sga_target = 4G SCOPE=BOTH;
ALTER SYSTEM SET sga_max_size = 4G SCOPE=SPFILE;   -- sga_max_size は再起動が必要
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=BOTH;

-- AMM を無効化する（AMM から ASMM に切り替える場合）
ALTER SYSTEM SET memory_target = 0 SCOPE=BOTH;

-- 変更を確認
SHOW PARAMETER sga_target;
SHOW PARAMETER pga_aggregate_target;

-- SGA の主要コンポーネントの使用量を確認
SELECT
    pool,           -- shared pool / large pool / java pool など
    name,           -- コンポーネント名
    ROUND(bytes / 1024 / 1024, 2) AS mb
FROM V$SGASTAT
WHERE pool IS NOT NULL   -- NULL は SGA 固定部分
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- 共有プール（Shared Pool）の空き領域を確認
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')
ORDER BY bytes DESC;

-- 動的に管理される SGA コンポーネントの現在サイズと上限・下限を確認
SELECT
    component,
    ROUND(current_size / 1024 / 1024, 0)  AS current_mb,
    ROUND(min_size / 1024 / 1024, 0)      AS min_mb,
    ROUND(max_size / 1024 / 1024, 0)      AS max_mb,
    user_specified_size / 1024 / 1024     AS user_spec_mb,
    last_oper_type,   -- GROW / SHRINK / STATIC
    last_oper_time
FROM V$SGA_DYNAMIC_COMPONENTS
ORDER BY current_size DESC;

-- PGA 全体の使用状況を確認
SELECT
    name,
    ROUND(value / 1024 / 1024, 2) AS mb
FROM V$PGASTAT
WHERE name IN (
    'aggregate PGA target parameter',  -- PGA_AGGREGATE_TARGET の設定値
    'aggregate PGA auto target',       -- 自動管理対象の PGA 量
    'total PGA inuse',                 -- 現在使用中の PGA 合計
    'total PGA allocated',             -- 割り当て済みの PGA 合計
    'maximum PGA allocated'            -- ピーク時の PGA 使用量
)
ORDER BY mb DESC;

-- PGA 使用量が多いプロセス Top 10
SELECT
    p.pid,
    p.spid,
    s.username,
    s.program,
    ROUND(p.pga_used_mem / 1024 / 1024, 2)   AS pga_used_mb,
    ROUND(p.pga_alloc_mem / 1024 / 1024, 2)  AS pga_alloc_mb,
    ROUND(p.pga_max_mem / 1024 / 1024, 2)    AS pga_max_mb
FROM V$PROCESS p
LEFT JOIN V$SESSION s ON p.addr = s.paddr
WHERE p.pga_used_mem > 0
ORDER BY p.pga_used_mem DESC
FETCH FIRST 10 ROWS ONLY;

-- バッファキャッシュヒット率（物理読み込みの発生率）
SELECT
    ROUND(1 - (phyrds.value / (dbget.value + cget.value)), 4) * 100
        AS buffer_cache_hit_pct
FROM
    (SELECT value FROM V$SYSSTAT WHERE name = 'physical reads') phyrds,
    (SELECT value FROM V$SYSSTAT WHERE name = 'db block gets') dbget,
    (SELECT value FROM V$SYSSTAT WHERE name = 'consistent gets') cget;
-- 目標: 95% 以上（低い場合は DB_CACHE_SIZE を増やす）

-- ライブラリキャッシュヒット率（ハードパース発生率）
SELECT
    ROUND(SUM(pinhits) / NULLIF(SUM(pins), 0) * 100, 2) AS library_cache_hit_pct
FROM V$LIBRARYCACHE;
-- 目標: 95% 以上（低い場合はバインド変数使用が重要）