Oracle10g 動的ＳＧＡおさらいの巻

＜Oracle10g 動的ＳＧＡおさらいの巻＞
ペンネーム：アイスケーキ

やっとＧＷが終わりました。いや、あっという間に終わりになりました。
気合を入れて頑張りたいところですが、いきなりスピードを上げて無理をする
のは事故の元です。
時間に追われず余裕をもっていきましょう。

今週は「動的ＳＧＡ」についておさらいをしていきます。
（以前、検証した内容の要点をまとめています）

★☆★

Oracle9iからインスタンスを停止することなく動的に共有プールを変更できる
ようになりましたが、ALTERコマンドで指定しなければ変更できないレベルで
した。
Oracle10gからは共有プール等の拡張必要性を判断して（ライブラリキャッシ
ュヒット率低下等で推測しているものと思われます）デフォルト・バッファキ
ャッシュを縮小して共有プール、ラージプールおよびプロセス・プライベート
・メモリのサイズが拡張されます。

○SGA_TARGETパラメータが追加され今迄のSGA構成メモリパラメータを指定
する必要がなくなりました。
SHARED_POOL_SIZE、LARGE_POOL_SIZE、JAVA_POOL_SIZE、DB_CACHE_SIZE等
はもう指定しなくてもSGAを構成することができます。
もちろん個別に定義することも可能です。その場合は自動メモリ管理は行
われません。

○領域が足りないと判断して自動的に領域拡張が発生するのは共有プール、
Javaプール、ラージプール、Streamsプールです。
デフォルト・バッファキャッシュは常にメモリ供給元になります。
且つ、共有プール、ラージプール、Javaプールの各コンポーネント間での
メモリ領域の移動はありません。

○デフォルト・バッファキャッシュが不足していても共有プールなど他のコ
ンポーネントを縮小してデフォルト・バッファキャッシュは自動で増加し
ません。理由は以下のためです。

⇒バッファキャッシュは領域が不足する場合は待機となりエラーは起きない。
⇒バッファキャッシュ待機よりも共有プール等領域不足の方が深刻なため、
共有プール等のサイズから割り当てると更に深刻な状況に陥るから。
⇒共有プール、ラージプール、Javaプール領域が足りない場合は
ORA-4031(共有メモリーのstringバイトを割当てできません)、
ORA-29554(メモリー不足状態でJavaが未処理です)でユーザ処理が失敗し
てしまうから。

○どうしてもデフォルト・バッファキャッシュを増やしたい場合
sga_max_size>=sga_targetであれば手動でsga_targetを増やし共有プール、
Javaプール、ラージプール領域を手動調整する事で実現する事は可能です。

○V$SGAINFO動的ビューが追加され、SGA構成メモリパラメータ値をまとめて
参照することができるようになりました。
グラニュルサイズ等がみれます。

○動的ビューについてもフラグ値が増えました。

例）V$SGA_RESIZE_OPSに追加、削除された値
OPER_TYPE:操作タイプ ⇒STATIC（未変更）
⇒INITIALIZING （初期化中）
⇒DISABLED （遅延）
⇒SHRINK_CANCEL (縮小中止）

OPER_MODE:操作モード ⇒DISABLED（遅延）
⇒IMMEDIATE（即時）
⇒AUTOは無くなりました

STATUS:操作の完了状態 ⇒INACTIVE（停止中）
⇒PENDING （保留）
⇒COMPLETE （完了）
⇒CANCELLED（中止）
⇒NORMAL,CANCELは無くなりました

☆★☆★☆★
動的にメモリを拡張、縮小してみましょう。
今回は共有プールを拡張しますが、合わせてデフォルト・バッファプールが
縮小、拡張します。

◆環境
Microsoft Windows XP Pro
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options

SGAは128MBで定義されています。

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
sga_max_size                         big integer 128M
sga_target                           big integer 128M

新しく追加された動的ビューを参照すれば更にSGAを構成しているサイズが分
かります。この環境では１グラニュルが４MBであることが分かります。

SQL>select * from V$SGAINFO;
NAME                             BYTES      RES
-------------------------------- ---------- ---
Granule Size                        4194304 No  ★１グラニュル

◆共有プールの拡張

共有プールが不足したとシステムに認識させるため大量のSQL文を発行してラ
イブラリキャッシュヒット率を落としてみます。
（任意なテーブルを作成した後、PL/SQLでバインド変数を使わずに大量のSQL
を発行する。今回はinsert文を３千行）

BEGIN
    INSERT INTO TBL1(t1,t2,t3,t4) VALUES (i,(i*100),(i*100),(i*100));
    INSERT INTO TBL1(t1,t2,t3,t4) VALUES (i,(i*100),(i*100),(i*101));
    INSERT INTO TBL1(t1,t2,t3,t4) VALUES (i,(i*100),(i*100),(i*102));
　　（中略。。。）
END;
/

コンポーネントの拡縮を確認します。

SQL>
select to_char(START_TIME,'YYYY/MM/DD HH24:MI:SS') as "処理時間",
COMPONENT as "コンポーネント",
OPER_TYPE as "操作",
(FINAL_SIZE-INITIAL_SIZE)/1024/1024 as "差分(MB)",
FINAL_SIZE/1024/1024 as "size(MB)"
from V$SGA_RESIZE_OPS;

処理時間            コンポーネント       操作         差分(MB)  size(MB)
------------------- -------------------- ------------ --------- ----------
2005/05/06 13:00:04 DEFAULT buffer cache SHRINK              -4         76
2005/05/06 13:00:04 shared pool          GROW                 4         40
2005/05/06 13:23:03 DEFAULT buffer cache SHRINK              -4         72
2005/05/06 13:23:03 shared pool          GROW                 4         44

上記は共有プールへの領域割当状況が出力されていますが常にバッファキャッ
シュから割り当てられていく状況が見えます。
単位は４ＫＢ（１グラニュル）の倍数で増減しています。

■おさらい

10gでは自動拡張によりSGAコンポーネントのメモリサイズ調整が行われます。
（もちろん9i同様、alter コマンドでの変更も可能です）

以下のメモリ拡縮が発生します。
デフォルト・バッファキャッシュ縮小 ⇒ 共有プール拡張
デフォルト・バッファキャッシュ縮小 ⇒ Javaプール拡張
デフォルト・バッファキャッシュ縮小 ⇒ ラージプール拡張
デフォルト・バッファキャッシュ縮小 ⇒ Streamsプール拡張

又、３つのコンポーネント間での領域の貸し借りもありません。
（共有プールラージプール Javaプール共有プール）

自動調整されるコンポーネントに余裕が出た場合は以下のようにデフォルト・
バッファキャッシュへ領域が戻されることはありません。

共有プール縮小 ⇒ デフォルト・バッファキャッシュ拡張
Javaプール縮小 ⇒ デフォルト・バッファキャッシュ拡張
ラージプール縮小 ⇒ デフォルト・バッファキャッシュ拡張
Streamsプール縮小 ⇒ デフォルト・バッファキャッシュ拡張

以上のことから10gでは最適なSGA領域が確保されるように考えられています。
（チューニングや監視ポイントが増えたとも言えますが。。。）

今週はここまで。

企業の社会的な責任とは。。。考えさせられる茅ヶ崎より

Oracle10g 動的ＳＧＡおさらい の巻