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

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

MAXOPENCURSORS
HOLD_CURSOR
RELEASE_CURSOR

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

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

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

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

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

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

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

カーソルの制御

一般に、明示的に宣言されたカーソルの制御には、次の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;

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

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

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

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

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

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

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

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

リソースの使用

ユーザー・セッションごとのオープン・カーソルの最大数は、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の値を大きく設定すると、実行速度は確実に速くなりますが、メモリーの使用量は大きくなります。

実行回数が少ない場合

実行回数の少ない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キャッシュに保存されるため、それ以上の再解析処理は不要になることがあります。カーソルをクローズしても、解析された表現はキャッシュの内容が書き換えられるまで効力を持ちます。

実行回数が多い場合

プライベート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-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	ただちに削除されます。