SGAに起因するパフォーマンス障害を発見する：Oracleパフォーマンス障害の克服（5）（3/3 ページ）
Oracleデータベースの運用管理者は、突発的に直面するパフォーマンス障害にどうやって対処したらよいか。本連載は、非常に複雑なOracleのアーキテクチャに頭を悩ます管理者に向け、短時間で問題を切り分け、対処法を見つけるノウハウを紹介する。対象とするバージョンはOracle8から9iまでを基本とし、10gの情報は随時加えていく。（編集局）
共有プールは全体と使用目的を意識して設定する
共有プールはライブラリ・キャッシュとディクショナリ・キャッシュとして使われる領域で、初期化パラメータファイルのSHARED_POOL_SIZEパラメータで設定されます。2つの用途の合算でメモリが消費されるため（比率は稼働中のインスタンスによって変動します）、それぞれがどのように使用されているか判断して、共有プールのサイズの適合性を確認します。まずは共有プール全体のサイズを確認し、全体として適正かどうか判断します。その後、使用目的（ライブラリ・キャッシュ、ディクショナリ・キャッシュ）ごとに、データベース・バッファ・キャッシュと同様にヒット率を確認し、最適な共有プールサイズを探っていきましょう。
共有プール全体のサイズを確認
V$DB_OBJECT_CACHEは、ライブラリ・キャッシュ内にキャッシュされるデータベース・オブジェクトを示します。オブジェクトには、表および索引、クラスタ、シノニム定義、PL/SQLプロシージャ、パッケージ、トリガーなどがあります。この動的パフォーマンスビューのSHARABLE_MEMカラムにて取得されるサイズの合計が各オブジェクトのメモリ上に保持されているサイズです。
V$SQLAREAは、共有SQL領域の統計情報を示します。SQL文字列ごとに1行ずつ表示されます。このビューで提供される統計情報は、メモリ内および解析済み、実行可能状態のSQL文の情報です。この動的パフォーマンスビューのSHARABLE_MEMカラムで取得されるサイズの合計がメモリ上に保持されているSQL文のサイズ、USERS_OPENINGカラムで取得されるサイズの合計の250倍がユーザーによるカーソル使用サイズとなります。
SQL> SET SERVEROUTPUT ON;
DECLARE
sum_db_object_cache NUMBER;
sum_s_sqlarea NUMBER;
sum_u_sqlarea NUMBER;
now_parameter NUMBER;
result NUMBER;
BEGIN
SELECT SUM(SHARABLE_MEM) INTO sum_db_object_cache
FROM V$DB_OBJECT_CACHE;
SELECT SUM(SHARABLE_MEM) INTO sum_s_sqlarea
FROM V$SQLAREA;
SELECT SUM(250 * USERS_OPENING) INTO sum_u_sqlarea
FROM V$SQLAREA;
result :=
sum_db_object_cache + sum_s_sqlarea + sum_u_sqlarea;
SELECT value INTO now_parameter
FROM V$PARAMETER WHERE NAME = 'shared_pool_size';
DBMS_OUTPUT.PUT_LINE(
'パラメータSHARED_POOL_SIZE->' || now_parameter);
DBMS_OUTPUT.PUT_LINE(
'現在最低限必要なSHARED_POOL_SIZE->' || result);
DBMS_OUTPUT.PUT_LINE(
'適正なSHARED_POOL_SIZE(×1.3)->' || result * 1.3);
END;
/
リスト4のSQLで、現在設定されている共有プールサイズ、最低限必要な共有プールサイズ、適正な共有プールのメモリサイズが分かりました。上記のSQLでは最適な共有プールを現在最低限必要な共有プールサイズの1.3倍としましたが、これは筆者の経験によるもので、状況によって各自で見定める必要があります。
ライブラリ・キャッシュ・ヒット率の算出
ライブラリ・キャッシュとはユーザーから実行されたSQLやPL/SQLプロシージャ、パッケージの実行計画や解析結果をメモリに格納し、同様なSQLに対する処理負担軽減を行っています。SQLが実行されると、すでに解析済みデータがキャッシュにあるか確認し、未解析の場合には解析結果をキャッシュに格納します。
実行されるたびに解析が行われているSQLは、実行された回数と解析された回数が同じと考えられます。これはV$SQLAREAから確認可能です。また、ライブラリ・キャッシュのヒット率を計算することで、SQLやパッケージが再利用されているかを確認できます。ヒット率は99％以上が望ましいとされています。
ライブラリ・キャッシュ・ヒット率＝
1 -（キャッシュ・ミス（RELOADS）合計 /
キャッシュ・ヒット（PINS）合計）
V$LIBRARYCACHEは、ライブラリ・キャッシュのパフォーマンスおよびアクティビティについての統計情報を示します。PINS列はキャッシュ・ヒットした（メモリから読み込まれた）件数を示し、RELOADS列はキャッシュ・ミスした（ディスクから読み出した）件数を示します。
SQL> SELECT SQL_TEXT AS SQL文,
PARSE_CALLS AS 解析回数,
EXECUTIONS AS 実行回数
FROM V$SQLAREA
WHERE PARSING_USER_ID != 0
AND PARSE_CALLS = EXECUTIONS
ORDER BY SQL_TEXT;
SQL> SELECT SUM(PINS) AS キャッシュヒット合計,
SUM(RELOADS) AS キャッシュミス合計,
ROUND((1 - SUM(RELOADS) / SUM(PINS)) * 100, 2)
|| '%' AS ライブラリキャッシュヒット率
FROM V$LIBRARYCACHE;
ライブラリ・キャッシュ内の統計データを使用可能かどうかは、ユーザーから要求されるSQLで判断されます。つまり、SQLができるだけ再利用可能な形で要求されていれば、キャッシュされたデータを再利用でき、サーバの負荷を軽減できます。Oracleが判断する基準は「SQL文が過去に要求されたものと同一か」なので、下記のような項目に考慮したSQLプログラミングをお勧めします。
- バインド変数を使用する
- PL/SQLストアド・プロシージャやストアド・ファンクションはできるだけパッケージ化する
ディクショナリ・キャッシュ・ヒット率の算出
ディクショナリ・キャッシュとはデータベース内のオブジェクトに関する変更情報や属性、構成情報を保持しています。SQLの解析時には、テーブル属性などのオブジェクト構成情報が参照されます。
ディクショナリ・キャッシュのヒット率を計算することで、データベース・オブジェクト情報をメモリから取得できているか確認できます。ヒット率は95％以上が望ましいとされています。
ディクショナリ・キャッシュ・ヒット率＝
1 -（キャッシュ・ミス（GETMISSES）合計 /
キャッシュ・ヒット（GETS）合計）
V$ROWCACHEは、データ・ディクショナリ・アクティビティについての統計情報を示します。GETS列はデータ・オブジェクトに関する情報要求の合計件数、GETMISSES列は結果的にキャッシュ・ミスになったデータ要求の回数を示します。
SQL> SELECT SUM(GETS) AS キャッシュヒット合計,
SUM(GETMISSES) AS キャッシュミス合計,
ROUND((1 - SUM(GETMISSES) / SUM(GETS)) * 100, 2)
|| '%' AS ディクショナリヒット率
FROM V$ROWCACHE;
初期化パラメータの変更
SGAのコンポーネントであるデータベース・バッファ・キャッシュと共有プールについて現在の状況が把握できたら、実際に初期化パラメータファイルのパラメータを変更するか、「ALTERSYSTEM …」で各パラメータを変更します。なお、Oracle9i以降のバージョンでは、データベース・バッファ・キャッシュと共有プールは動的に変更可能です。
SQL> ALTER SYSTEM SET パラメータ名 = 値;
当然のことですが、今回取り上げたメモリ設定は、SGA_MAX_SIZEを超えるサイズは指定できませんので、V$SGA_DYNAMIC_FREE_MEMORYから全体でSGA内のメモリをどの程度使用できるか確認し、その兼ね合いも考慮に入れます。また、Oracle10gでは自己管理機能拡張（自動共有（SGA）メモリ管理）により、管理の方法が大幅に容易になりました。
次回は引き続きSGAのコンポーネントから、REDOログバッファについて解説します。（次回に続く）
Copyright © ITmedia, Inc. All Rights Reserved.