Oracle SGAに関する調査

メモリ管理の方法を確認する

最近はほとんどSGAとPGAを完全自動的に管理する「自動メモリ管理」を採用されているのではと思いますが、当方の環境は以下の通り「自動共有メモリー管理」（SGAのサイズを決める）となっています。

初期化パラメータからメモリ管理を確認SQL

select NAME,VALUE,DISPLAY_VALUE from v$parameter2 where NAME in (
'sga_max_size'
,'statistics_level'
,'sga_target'
,'pga_aggregate_target'
);

確認例

NAME                     VALUE                DISPLAY_VALUE
------------------------ -------------------- --------------------
sga_max_size             1191182336           1136M
sga_target               1191182336           1136M   <-- 0以上かつsga_max_size以下
pga_aggregate_target     396361728            378M
statistics_level         TYPICAL              TYPICAL  <-- typicalもしくは

SQL>

SQL*Plus表示用

set lin 100
COLUMN NAME FORMAT A24
COLUMN VALUE FORMAT A20
COLUMN DISPLAY_VALUE FORMAT A20

V$SGAでSGAのサイズを調べる

sqlplus で接続した後、show sga とたたいて調べる方法がメジャーなのかもしれませんが、私はV$SGAから確認します。

SGA確認SQL

select name,value from V$SGA order by name
;

確認例

V$SGASTATから使用状況を確認

「自動メモリ管理」 でも 「自動共有メモリー管理」 であっても、Oracleがよしなに調整するこの　Variable Size　の内訳が知りたくなりますが、これはV$SGASTATで確認する事ができます。

V$SGASTAT確認SQL

select 
case
 when x.POOL = 'buffer_cache'   then  1
 when x.POOL = 'shared_io_pool' then  2
 when x.POOL = 'fixed_sga'      then  3
 when x.POOL = 'log_buffer'     then  4
 when x.POOL = 'large pool'     then  5
 when x.POOL = 'streams pool'   then  6
 when x.POOL = 'shared pool'    then  7
else 9
end lno
,x.POOL
,sum(x.USED_MEMORY) USED_MEMORY
,sum(x.FREE_MEMORY) FREE_MEMORY
,case
 when sum(x.FREE_MEMORY) = 0 then ' - '
 when sum(x.USED_MEMORY) = 0 then '  0.0%'
 when sum(x.FREE_MEMORY) > 0 then to_char(round(sum(x.USED_MEMORY) / ( sum(x.USED_MEMORY) +  sum(x.FREE_MEMORY) ),3) * 100 ,'B90.0')|| '%'
end "利用率"
 from
(
select nvl(t.POOL, t.NAME) as POOL
,decode(t.NAME, 'free memory',t.BYTES,0) free_memory
,decode(t.NAME, 'free memory',0,t.BYTES) used_memory
 from V$SGASTAT t
) x
group by x.POOL
order by lno
;

この通り内訳が確認できます。上記Variable Sizeの値は以下赤枠の合算値とほぼ同じになります。そして次に気になってくるのはshared pool(共有プール）ですが、これはV$BH動的パフォーマンスビューを参照する事で具体的にどんなテーブルや、インデックスがキャッシュオンしているか確認できます。

確認例