| Oracle® Database Oracleプリコンパイラのためのプログラマーズ・ガイド 11g リリース2(11.2) B61344-01 |
|
 前へ |
 次へ |
この付録の項目は次のとおりです。
この付録では、アプリケーションのパフォーマンスを改善するために簡単に適用できる方法をいくつか紹介します。これらの方法を使用すると、多くの場合、処理時間を25%以上短縮できます。
パフォーマンス低下の原因の1つは、Oracleの通信オーバーヘッドが多いことです。OracleではSQL文を一度に1つずつ処理します。つまり、1つの文の処理が終わるたびに、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文内のホスト変数をそれぞれのアドレスに関連付け、Oracleでそれらの値の読み書きができるようにする処理です。
多くのアプリケーションでは、カーソルの管理が十分ではありません。このため不要な解析やバインドが発生し、処理のオーバーヘッドが著しく増加します。
プリコンパイルしたプログラムのパフォーマンスに満足できない場合、オーバーヘッドを軽減できる方法がいくつかあります。
特にネットワーク環境では、次の方法でOracleの通信オーバーヘッドを大幅に減らせます。
ホスト変数の使用
埋込みPL/SQLの使用
処理のオーバーヘッドは、次の方法で大幅に減らせる場合があります。
SQL文の最適化
索引の使用
行レベル・ロックの使用
不要な解析の排除
ホスト配列を使用すると、データの集合全体を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配列の要素の値が格納されます。
詳細は、第9章「ホスト配列の使用方法」を参照してください。
図C-1のように、アプリケーションがデータベース集約型であれば、制御構造体を使用してPL/SQLブロック内でSQL文をグループ化し、ブロック全体をOracleに送信できます。これによりアプリケーションとOracle間の通信量は大幅に減少します。
また、PL/SQLサブプログラムを使用して、アプリケーションからOracleへのコールを減らすこともできます。たとえば、10個のSQL文を個々に実行するには、10回のコールが必要ですが、10個のSQL文を含む1つのサブプログラムを実行する場合、コールは1回で済みます。
無名ブロックとは異なり、PL/SQLサブプログラムは別々にコンパイルし、Oracleデータベースに格納できます。PL/SQLサブプログラムは、コールされるとただちにPL/SQLエンジンに渡されます。さらに、複数のユーザーが1つのサブプログラムを実行する場合でも、メモリーにロードするコピーは1つで済みます。
PL/SQLは、Oracleアプリケーション開発ツール(Oracle FormsやOracle Reportsなど)と一緒に使用できます。PL/SQLでこれらのツールに手続き型処理能力を追加することで、パフォーマンスが向上します。PL/SQLを使用すれば、ツールはOracleをコールせずに迅速かつ効率的に計算ができます。その結果、時間が節約され、ネットワーク通信量も減らせます。詳細は、第5章「埋込みPL/SQLの使用方法」および『Oracle Database PL/SQL言語リファレンス』を参照してください。
Oracleオプティマイザにより、すべてのSQL文について実行計画(その文を実行するためにOracleが行う一連の手順)が生成されます。この手順は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』に記載されているルールによって決まります。これらのルールに従うと、最適なSQL文を作成できます。
Oracleオプティマイザにより、すべてのSQL文について実行計画(その文を実行するためにOracleが行う一連の手順)が生成されます。場合によっては、OracleにSQL文を最適化する方法を提案できます。このような提案はヒントと呼ばれ、これによりオプティマイザによる決定に開発者が影響を与えることができます。
ヒントはディレクティブではありません。オプティマイザによるジョブの実行を助けるに過ぎません。ヒントの中には、SQL文の最適化に使用される情報の範囲を限定するものもあれば、全体的な方針を提案するものもあります。ヒントを使用して、次のものを指定できます。
SQL文のための最適化の方法
参照される表ごとのアクセス・パス
結合のための結合順序
表を結合するために使用する方法
ヒントをオプティマイザに与えるには、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つ以上のヒントが含まれていることを示します。同じコメントに注釈とヒントを含めることができます。
オプティマイザ・ヒントの詳細は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。
索引は、ROWIDを使用して、表の列の各値をその値が格納されている行に関連付けます。索引はCREATE INDEX文で作成します。
表の15%未満の行しか戻さない問合せでは、索引を使用すればパフォーマンスを向上させることができます。表の15%以上の行を戻す問合せは、全体スキャン(つまり、すべての行を順番に読み取る方法)の方が速く実行されます。WHERE句で索引付きの列を指定する問合せでは、その索引を使用できます。索引を付ける列を選択するためのガイドラインは、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。
デフォルトでは、Oracleは表レベルではなく行レベルでデータをロックします。行レベルでロックすると、複数のユーザーが同じ表内の異なる行に同時にアクセスできます。その結果、パフォーマンスは大幅に向上します。
表レベルでもロックを指定できますが、トランザクション処理オプションの効果が低下します。表ロックの詳細は、「LOCK TABLE文の使用方法」を参照してください。
オンライン・トランザクションの処理を実行するアプリケーションには、行レベル・ロックが最も有効です。アプリケーションで表レベル・ロックを指定している場合は、行レベル・ロックを利用できるように変更してください。通常、明示的な表レベル・ロックの使用は避けます。
不要な解析を排除するには、カーソルの正しい処理と、次に示すカーソル管理オプションを選択して使用することが必要です。
これらのオプションは、暗黙カーソルと明示カーソル、カーソル・キャッシュおよびプライベートSQL領域に影響を与えます。
カーソル・キャッシュの統計情報は、ORACAを使用して取得できます。「Oracle通信領域の使用方法」を参照してください。
カーソルには、暗黙カーソルと明示カーソルの2種類があることを思い出してください。Oracleでは、すべてのデータ定義文およびデータ操作文にカーソルを暗黙的に宣言します。ただし、複数行を戻す問合せでは、カーソルを明示的に宣言する(またはホスト配列を使用する)必要があります。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文が変わった場合には、SQL文を再PREPAREして、カーソルを再オープンします。そのSQL文が不要になった場合のみ、カーソルをCLOSEします。
OPEN文およびCLOSE文をループの中に指定するのは、できるかぎり避けてください。これは、SQL文の不要な解析の原因になります。次の例では、OPEN文とCLOSE文がどちらも外側のwhileループ内にあります。MODE=ANSIの場合、ANSIではカーソルを再オープンする前にクローズする必要があるため、CLOSE文は例に示す位置に指定します。
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文の解析は、その構成を変更しないかぎり1回のみで十分です。たとえば、選択リストまたはWHERE句に列を追加して、問合せの構成を変更します。HOLD_CURSOR、RELEASE_CURSORおよびMAXOPENCURSORSオプションにより、OracleでのSQL文の解析および再解析の管理方法を制御できます。明示カーソルを宣言すると、解析を最大限制御できます。
データ操作文を実行すると、その文に関連付けられたカーソルがカーソル・キャッシュ内のエントリにリンクされます。カーソル・キャッシュとは、カーソル管理のために使用され、継続的に更新されるメモリー領域です。そのカーソル・キャッシュのエントリは、次にプライベートSQL領域にリンクされます。
プライベートSQL領域は、実行時にOracleによって動的に作成される作業領域で、解析済のSQL文、ホスト変数のアドレス、その他文の処理に必要な情報が保存されます。明示カーソルを使用すると、SQL文に名前を付け、そのプライベートSQL領域に保存されている情報にアクセスし、この情報の処理をある程度制御できます。
図C-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領域のリンクを維持する必要があります。この情報へのアクセスを維持することで、この文を次に実行する際の速度が大幅に向上します。
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をオーバーライドすることに注意してください。
