パフォーマンス・チューニング

C.1 パフォーマンス低下の原因

パフォーマンス低下の原因の1つは、Oracleの通信オーバーヘッドが多いことです。Oracleは一度にSQL文を1つずつ処理する必要があります。つまり、各SQL文がOracleをコールするので、オーバーヘッドが増加します。ネットワーク化された環境下では、ネットワークを介してSQL文を送信する必要があるため、ネットワークの通信量が増加することになります。ネットワークの通信量が多いと、アプリケーションの処理速度が著しく低下する可能性があります。

パフォーマンスを低下させるもう1つの原因は、非効率的なSQL文です。SQLは非常に柔軟性があるため、2つの異なる文で同じ結果を得ることができますが、一方の文の効率が悪い場合があります。たとえば、次の2つのSELECT文では、同じ行(最低1人の従業員がいる部門の名前と番号)を戻します。

EXEC SQL SELECT DNAME, DEPTNO
 FROM DEPT
 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP);
EXEC SQL SELECT DNAME, DEPTNO
 FROM DEPT
 WHERE EXISTS
 (SELECT DEPTNO FROM EMP WHERE DEPT.DEPTNO = EMP.DEPTNO);

ただし、最初の文では、DEPT表内のすべての部門番号を探して時間のかかるEMP表全体のスキャンを行うため、処理速度が低下します。EMP表内のDEPTNO列に索引を付けていても、この副問合せにはDEPTNOを指定するWHERE句がないので、索引は使用されません。

パフォーマンス低下の3つ目の原因は、不要な解析およびバインドです。SQL文を実行する前にOracleがこのSQL文を解析しバインドする必要があることに注意してください。解析とは、SQL文を調べて、これが構文規則に従って正しいデータベース・オブジェクトを参照していることを確認する作業です。バインドとは、SQL文内のホスト変数をOracleがその値に対して読取りまたは書込みができるようにそれぞれのアドレスに対応付ける作業です。

大部分のアプリケーションにおいて、カーソルの管理を十分に行っているとはいえません。このため不要な解析またはバインドが発生し、結果的に処理のオーバーヘッドが著しく増加します。

C.2 パフォーマンスの改善方法

プリコンパイルしたプログラムのパフォーマンスがよくない場合でも、オーバーヘッドを減少させる方法はあります。

特にネットワーク環境では、次の方法でOracleの通信オーバーヘッドを大幅に減らせます。

ホスト配列の使用
埋込みPL/SQLの使用

処理のオーバーヘッドは、次の方法で大幅に減らせる場合があります。

SQL文の最適化
索引の使用
行レベル・ロックの利用
不要な解析の排除

C.3 ホスト配列の使用方法

ホスト配列を使用すると、データの集合全体を1つのSQL文で操作できるため、パフォーマンスが向上します。たとえば、300人の従業員の給料をEMP表に挿入するとします。配列がないと、プログラムは300の個々の挿入(従業員ごとに1つ)を実行する必要があります。配列を使用すると、必要なINSERTは1回のみです。次の文を考えてみます。

EXEC SQL INSERT INTO EMP (SAL) VALUES (:salary);

salaryが単純なホスト変数の場合、OracleではINSERT文を1回実行し、EMP表に1行挿入します。その行のSAL列にsalaryの値が格納されます。この方法で300行を挿入するには、INSERT文を300回実行する必要があります。

しかし、salaryがサイズが300のホスト配列の場合は、300行すべてが一度にEMP表に挿入されます。各行のSAL列には、salary配列の要素の値が格納されます。

詳細は、ホスト配列の使用方法を参照してください。

C.4 埋込みPL/SQLの使用方法

図C-1に示すように、データベース処理が中心のアプリケーションの場合は、制御構造を使用して複数のSQL文を1つのPL/SQLブロックにまとめ、そのブロック全体をOracleに送信できます。これによりアプリケーションとOracle間の通信量は大幅に減少します。

また、PL/SQLサブプログラムを使用して、アプリケーションからOracleへのコールを減らすこともできます。たとえば、10個のSQL文を個々に実行するには、10回のコールが必要ですが、10個のSQL文を含む1つのサブプログラムを実行する場合、コールは1回で済みます。

無名ブロックとは異なり、PL/SQLサブプログラムは別々にコンパイルし、Oracleデータベースに格納できます。コールされたPL/SQLサブプログラムは、ただちにPL/SQLエンジンに渡されます。さらに、複数のユーザーが1つのサブプログラムを実行する場合でも、メモリーにロードするコピーは1つで済みます。

図C-1 PL/SQLによるパフォーマンスの向上

「図C-1 PL/SQLによるパフォーマンスの向上」の説明

PL/SQLは、Oracleアプリケーション開発ツール(Oracle FormsやOracle Reportsなど)と一緒に使用できます。PL/SQLでこれらのツールに手続き型処理能力を追加することで、パフォーマンスが向上します。PL/SQLを使用すれば、ツールはOracleをコールせずに迅速かつ効率的に計算ができます。その結果、時間が節約され、ネットワーク通信量も減らせます。詳細は、埋込みPL/SQLの使用方法およびOracle Database PL/SQL言語リファレンスを参照してください。

C.5 SQL文の最適化

Oracleオプティマイザにより、すべてのSQL文について実行計画(その文を実行するためにOracleが行う一連の手順)が生成されます。これらの手順は、Oracle Databaseアドバンスト・アプリケーション開発者ガイドに記載されているルールによって決まります。これらのルールに従うと、最適なSQL文を作成できます。

C.5.1 オプティマイザ・ヒント

Oracleオプティマイザにより、すべてのSQL文について実行計画(その文を実行するためにOracleが行う一連の手順)が生成されます。場合によっては、OracleにSQL文を最適化する方法を提案できます。このような提案はヒントと呼ばれ、これによりオプティマイザによる決定に開発者が影響を与えることができます。

ヒントはディレクティブではなく、オプティマイザによるジョブの実行を助けるだけです。一部のヒントはSQL文を最適化するために使用される情報の範囲を制限し、他のヒントは全体的な戦略を提案します。ヒントを使用して、次の事項を指定できます。

SQL文の最適化アプローチ
参照されているそれぞれの表へのアクセス・パス
結合のための結合順序
表を結合する方法

C.5.2 ヒントの与え方

ヒントをオプティマイザに与えるには、SELECT文、UPDATE文またはDELETE文の動詞の直後に、C言語形式のコメントとしてヒントを入れます。ルール重視の最適化またはコスト重視の最適化のどちらかを選択できます。コスト重視の最適化では、ヒントはスループットの最大化または応答時間に寄与します。次の例では、ALL_ROWSヒントにより、問合せスループットが最大になります。

EXEC SQL SELECT /*+ ALL_ROWS (cost-based) */ EMPNO, ENAME, SAL
 INTO :emp_number, :emp_name, :salary -- host arrays
 FROM EMP
 WHERE DEPTNO = :dept_number;

プラス記号(+)はコメント先頭の直後に置く必要があり、コメントが1つ以上のヒントを含むことを示します。コメントには注釈とヒントが含まれることがあることに注意してください。

オプティマイザ・ヒントの詳細は、「パフォーマンスとスケーラビリティ」を参照してください。

C.5.3 トレース機能

SQLトレース機能とEXPLAIN PLAN文を使用すると、アプリケーションの処理速度を低下させる恐れのあるSQL文を特定できます。トレース機能で、Oracleで実行するすべてのSQL文に対する統計表示を生成します。この統計表示で、最も処理時間のかかる文がどれか判断できます。その結果、これらの文のチューニングに専念できます。

EXPLAIN PLAN文は、アプリケーション内のSQL文ごとに実行計画を示します。実行計画を使用すると、非効率的なSQL文を特定できます。

C.6 索引の使用について

索引は、ROWIDを使用して、表の列の各値をその値が格納されている行に関連付けます。索引はCREATE INDEX文で作成します。

表の15%未満の行しか戻さない問合せでは、索引を使用することによりパフォーマンスが向上します。表の15%以上の行を戻す問合せは、全体スキャン(つまり、すべての行を順番に読み取る方法)の方が速く実行されます。WHERE句で索引付きの列を指定する問合せでは、その索引を使用できます。索引付けする列の選択に役立つガイドラインは、「パフォーマンスとスケーラビリティ」を参照してください。

C.7 行レベル・ロックの利用

デフォルトでは、Oracleは表レベルではなく行レベルでデータをロックします。行レベルでロックすると、複数のユーザーが同一の表の別の行に同時にアクセスできます。その結果、パフォーマンスが大幅に向上します。

表レベルでのロックも指定できますが、これはトランザクション処理オプションの効果を低下させます。表ロックの詳細は、LOCK TABLE文の使用についてを参照してください。

オンラインのトランザクション処理を実行するアプリケーションには、行レベル・ロックが最も有効です。アプリケーションで表レベル・ロックを指定している場合は、行レベル・ロックを利用できるように変更してください。通常、明示的な表レベル・ロックは使用しないようにします。

C.8 不要な解析の排除について

不要な解析を排除するには、カーソルを正しく操作すること、および次に示すカーソル管理オプションを選択して使用することが必要です。

MAXOPENCURSORS
HOLD_CURSOR
RELEASE_CURSOR

これらのオプションは、暗黙カーソルと明示カーソル、カーソル・キャッシュおよびプライベートSQL領域に影響を与えます。

カーソル・キャッシュの統計情報は、ORACAを使用して取得できます。Oracle通信領域の使用についてを参照してください。

C.8.1 明示カーソルの処理について

カーソルには、暗黙カーソルと明示カーソルの2種類があることを思い出してください。Oracleはデータ定義文およびDML文のすべてを暗黙的にカーソルを宣言します。ただし、複数行を戻す問合せでは、カーソルを明示的に宣言する(またはホスト配列を使用する)必要があります。DECLARE CURSOR文を使用すると、明示カーソルを宣言できます。明示カーソルのオープンおよびクローズの方法は、パフォーマンスに影響を与えます。

アクティブ・セットの再評価が必要なときは、そのカーソルを再度オープンするだけでかまいません。OPEN文では、任意の新しいホスト変数の値が使用されます。最初にカーソルをクローズせずにオープンのままにしておくと、処理時間を節約できます。

パフォーマンス・チューニングを容易にするために、プリコンパイラではすでにオープンされているカーソルを再オープンできます。ただし、これはANSI/ISOの埋込みSQL規格に対するOracle拡張機能です。したがって、MODE=ANSIの場合、カーソルは再オープンする前にクローズする必要があります。

カーソルのオープンによって取得したリソース(メモリーおよびロック)を解放する場合のみ、そのカーソルをCLOSEします。たとえば、プログラムでは終了前にすべてのカーソルをクローズする必要があります。

C.8.2 カーソルの制御

一般に、明示的に宣言されたカーソルの制御には、次の3つの方法があります。

DECLARE、OPENおよびCLOSE文の使用
PREPARE、DECLARE、OPENおよびCLOSE文の使用
MODE=ANSIの場合のCOMMITによるカーソルのクローズ

最初の方法を使用する場合は、不要な解析に注意する必要があります。OPEN文は、カーソルをクローズしたか、まだオープンしていないために、解析された文を使用できないときにかぎり解析を実行します。プログラムはカーソルをDECLAREし、ホスト変数の値が変わるたびにこれを再オープンし、このSQL文が必要なくなったときにのみこれをCLOSEする必要があります。

2番目の方法(動的SQLの方法3および4)を使用する場合は、PREPAREで解析が実行され、解析された文はCLOSEを実行するまで使用できます。プログラムはSQL文を用意してカーソルをDECLAREし、ホスト変数の値が変わるたびにこれを再オープンし、このSQL文が必要なくなったときにのみこれをCLOSEする必要があります。

可能な場合、OPEN文とCLOSE文を1つのループに配置しないようにします。このSQL文が不必要に再解析される原因になる可能性があります。次の例では、OPENとCLOSE文のどちらも、外側のwhileループ内にあります。MODE=ANSIの場合、示されているようにCLOSE文を配置する必要があります。ANSIでは、カーソルをクローズしてから再オープンする必要があるためです。

EXEC SQL DECLARE emp_cursor CURSOR FOR 
 SELECT ename, sal from emp where sal > :salary and 
 sal <= :salary + 1000; 
salary = 0; 
while (salary < 5000) 
{ 
 EXEC SQL OPEN emp_cursor; 
 while (SQLCODE==0) 
 { 
 EXEC SQL FETCH emp_cursor INTO .... 
 ... 
 } 
 salary += 1000; 
 EXEC SQL CLOSE emp_cursor; 
}

しかし、MODE=ORACLEでは、CLOSE文はオープンされているカーソルがなくても実行できます。CLOSE文を外側のwhileループの外に指定すると、OPEN文が繰り戻されるたびに再解析されるのを回避できます。

... 
while (salary < 5000) 
{ 
 EXEC SQL OPEN emp_cursor; 
 while (sqlca.sqlcode==0) 
 { 
 EXEC SQL FETCH emp_cursor INTO .... 
 ... 
 } 
 salary += 1000; 
} 
EXEC SQL CLOSE emp_cursor;

C.8.3 カーソル管理オプションの使用について

SQL文の解析は、構成を変更しないかぎり一度のみで十分です。たとえば、選択リストまたはWHERE句に列を追加して、問合せの構成を変更します。HOLD_CURSOR、RELEASE_CURSORおよびMAXOPENCURSORSオプションにより、OracleでのSQL文の解析および再解析の管理方法を制御できます。明示カーソルを宣言すると、解析を最大限に制御できます。

C.8.4 プライベートSQL領域およびカーソル・キャッシュ

データ操作文を実行すると、その文に関連付けられたカーソルがカーソル・キャッシュ内のエントリにリンクされます。カーソル・キャッシュとはカーソル管理のために使用されて連続的に更新されるメモリー領域です。そのカーソル・キャッシュのエントリは、次にプライベートSQL領域にリンクされます。

プライベートSQL領域は、実行時にOracleによって動的に作成される作業領域で、解析済のSQL文、ホスト変数のアドレス、その他文の処理に必要な情報が保存されます。明示カーソルを使用すると、SQL文に名前を付け、そのプライベートSQL領域に保存されている情報にアクセスし、この情報の処理をある程度制御できます。

図C-2は、プログラムでINSERTおよびDELETEが実行された後のカーソル・キャッシュを表しています。

図C-2 カーソル・キャッシュでリンクされたカーソル

「図C-2 2カーソル・キャッシュでリンクされたカーソル」の説明

C.8.5 リソースの使用

ユーザー・セッションごとのオープン・カーソルの最大数は、Oracleの初期化パラメータOPEN_CURSORSによって設定します。

MAXOPENCURSORSは、カーソル・キャッシュの初期サイズを指定します。新しいカーソルが必要で、空きのキャッシュ・エントリがない場合、Oracleはエントリを再利用しようとします。再利用できるかどうかは、HOLD_CURSORとRELEASE_CURSORの値によって決まり、明示カーソルの場合は、カーソル自体の状態によって決まります。

MAXOPENCURSORSの値が実際に必要なキャッシュ・エントリ数より少ない場合、Oracleでは再利用可能のマークが付いている最初のキャッシュ・エントリが使用されます。たとえば、INSERT文のキャッシュ・エントリE(1)に再利用可能のマークが付いていて、キャッシュ・エントリの数はすでにMAXOPENCURSORSに達しているとします。プログラムが新しい文を実行する場合、キャッシュ・エントリE(1)とそのプライベートSQL領域は新しい文に再度割り当てられることがあります。INSERT文を再実行するには、Oracleではその文を解析しなおし、別のカーソル・キャッシュ・エントリを再び割り当てる必要があります。

再利用できるキャッシュ・エントリが見つからない場合、Oracleは追加のキャッシュ・エントリを割り当てます。たとえば、MAXOPENCURSORS=8で、8エントリすべてがアクティブな場合、9番目のエントリが作成されます。空きメモリーがなくなるか、OPEN_CURSORSで設定した上限に達するまで、Oracleでは必要に応じて追加のキャッシュ・エントリの割当てを続けます。この動的割当ては、処理オーバーヘッドを増大させます。

したがって、MAXOPENCURSORSに小さい値を指定するとメモリーは節約できますが、新しいキャッシュ・エントリの動的割当ておよび解除によりリソースの消費が大きくなる可能性があります。MAXOPENCURSORSの値を大きく設定すると、実行速度は確実に速くなりますが、メモリーの使用量は大きくなります。

C.8.6 実行回数が少ない場合

実行回数の少ないSQL文とそのプライベートSQL領域間のリンクは、一時的なものにした方がよい場合もあります。

HOLD_CURSOR=NO(デフォルト)の場合、OracleでSQL文が実行され、カーソルがクローズされた後に、プリコンパイラではカーソルとカーソル・キャッシュ間のリンクに再利用可能のマークを付けます。このリンクは、それが示すカーソル・キャッシュ・エントリが別のSQL文に必要になると、すぐに再利用されます。これにより、プライベートSQL領域に割り当てられたメモリーが解放され、解析ロックが解除されます。ただし、準備されたカーソルはアクティブ状態のままにしておく必要があるため、HOLD_CURSOR=NOを指定した場合も、そのリンクは維持されます。

RELEASE_CURSOR=YESと指定した場合、OracleでSQL文が実行され、カーソルがクローズされると、プライベートSQL領域は自動的に解放され、解析済の文は失われます。たとえば、サイトでメモリーの節約のためにMAXOPENCURSORSを小さい値に設定しているような場合には、これが必要になります。

データ操作文がデータ定義文より前にあり、どちらも同じ表を参照する場合には、データ操作文にRELEASE_CURSOR=YESを指定してください。これにより、データ操作文で取得される解析ロックと、データ定義文で要求される排他ロックとの間の競合が回避できます。

RELEASE_CURSOR=YESを指定した場合、プライベートSQL領域とキャッシュ・エントリ間のリンクはただちに削除され、プライベートSQL領域は解放されます。HOLD_CURSOR=YESを指定しても、RELEASE_CURSOR=YESによりHOLD_CURSOR=YESがオーバーライドされるため、OracleではSQL文を実行する前に、プライベートSQL領域用のメモリーを再度割り当て、SQL文の再解析を続ける必要があります。

それにもかかわらず、RELEASE_CURSOR=YESの場合、OracleではSQL文とPL/SQLブロックの解析済の表現が共有SQLキャッシュに保存されるため、それ以上の再解析処理は不要になることがあります。カーソルをクローズしても、解析された表現はキャッシュの内容が書き換えられるまで効力を持ちます。

C.8.7 実行回数が多い場合

プライベートSQL領域にはSQL文の実行に必要なすべての情報が格納されるため、頻繁に実行されるSQL文では、そのプライベートSQL領域とのリンクを維持する必要があります。この情報へのアクセスを上手に管理すると、後続の文の実行速度をさらに向上させることができます。

HOLD_CURSOR=YESの場合、OracleでSQL文が実行された後もカーソルとカーソル・キャッシュ間のリンクが維持されます。したがって、解析された文および割り当てられたメモリーが、利用可能なまま維持されます。これは、不要な再解析を避けるためにアクティブの状態にしておくSQL文で役立ちます。

HOLD_CURSOR=YESおよびRELEASE_CURSOR=NO(デフォルト)の場合、キャッシュ・エントリとプライベートSQL領域間のリンクは、OracleでSQL文が実行された後も維持され、オープン・カーソル数がMAXOPENCURSORSの値を超えないかぎり、再利用されることはありません。これは、解析済の文と割り当てられたメモリーが使用可能な状態のままなので、頻繁に実行されるSQL文に役立ちます。

デフォルト値のHOLD_CURSOR=YESおよびRELEASE_CURSOR=NOを使用すると、Oracleの以前のバージョンでは、SQL文の実行後に、解析済の表現はそのまま使用可能でした。Oracleデータベース・バージョン7では、同様の状況で、解析済の表現を使用できるのは、共有SQLキャッシュで期限切れになって削除されるまでです。通常、これは問題にはなりませんが、そのSQL文が再解析される前に参照オブジェクトの定義が変わると、結果が予期せぬものになる場合があります。

C.8.8 パラメータの相互作用

表C-1は、HOLD_CURSORとRELEASE_CURSORの相互作用を示しています。HOLD_CURSOR=NOを指定すると、RELEASE_CURSOR=NOはオーバーライドされ、RELEASE_CURSOR=YESを指定すると、HOLD_CURSOR=YESがオーバーライドされることに注意してください。

表C-1 HOLD_CURSORとRELEASE_CURSORの相互作用

HOLD_CURSOR	RELEASE_CURSOR	リンク
NO	NO	再利用可能としてマーク
YES	NO	維持
NO	YES	ただちに削除
YES	YES	ただちに削除