開発者：ODP.NET

ODP.NETのパフォーマンスの向上

Mark A. Williams著

接続プーリング、フェッチ・サイズの制御、ステートメント・キャッシュの活用

機能とパフォーマンスにおいて、Oracle Data Provider for .NET（ODP.NET）が.NETアプリケーションとOracle Databaseをつなぐ最適な選択肢となります。

アプリケーションによるデータベース・リソースの消費を減らすことができ、その結果、パフォーマンスとスケーラビリティが向上するパフォーマンス関連の機能がプロバイダに組み込まれています。 組み込まれている機能は、接続プーリング、フェッチ・サイズの制御、およびステートメント・キャッシュの3つです。

筆者がいくつかテストを実施し、アプリケーションのパフォーマンスを改善できる機能について確認しました。 この記事にあるすべてのテストでは、いずれもバージョン10.2.0.2.0のODP.NETとOracle Databaseを使用しました。 ステートメント・キャッシュ機能は、ODP.NETのバージョン10.1.0.3で追加された機能です。

接続プーリングの概要

データベースへの物理的な接続を作成するのは、時間とシステム・リソースの点でコストのかかる作業となります。 もっとも忙しい時間帯には、必要に応じて接続を作成するアプリケーションより、事前に接続を作成しておいて、必要なときに接続を割り当てることのできるアプリケーションの方がより適しています。 ODP.NETの接続プーリング機能を使用すると、アプリケーションは事前に作成された接続を用意するプールを作成および維持できます。

デフォルトでは、プロバイダの接続プーリングは有効になっています。 接続プーリングは、Pooling（ブール値の接続文字列属性）をTRUEまたはFALSEに設定することで、オンまたはオフにすることができます(接続プーリングのさまざまな側面を制御するほかの接続文字列パラメータについては、ODP.NETのドキュメントにすべて記載されています）。

アプリケーションが接続プーリングを使用する際、プロバイダは接続文字列の署名に基づき接続のプールを作成します。 この署名は接続文字列のパラメータの値に基づいています。 接続文字列パラメータのいずれかが変更された場合、次の接続が要求されたときに、ODP.NETがアプリケーションの新しい接続プールを作成します。

後続の接続要求は、新しい接続の作成プロセスを実行する代わりに、プールにある接続を使用できます。 アプリケーションが接続の使用を終了すると、その接続が物理的に閉じられたり、破棄されたりするのではなく、CloseメソッドまたはDisposeメソッドによってその接続がプールに戻されます。 プールされていない接続には、Disposeメソッドを呼び出すことが推奨されます。このメソッドは明示的にシステム・リソースを解放します。

動作中の接続プーリング

このコラムのサンプル・コードは、接続プーリングを使用した場合のパフォーマンス上のメリットを測定する1つの方法を明らかにするものです。 JulyAug2006.cs（ ダウンロード および リスト1 から入手可能）のコードが、2つの接続文字列を作成します。 1つは接続プーリングを使用し、もう1つは使用しません。 次に、コードは接続のOpenとCloseを繰り返します。 反復回数は、テスト・メソッドに渡されたパラメータによって制御されます。

接続プーリングを使用しない接続文字列（Pooling=FALSE）は、サンプルのHRユーザーとデフォルトのパスワードを使用します。

string no_pool =
  "User Id=hr;
   Password=hr;
   Data Source=oramag;
   Enlist=false;
   Pooling=false"

接続プーリングを使用する接続文字列（Pooling=TRUE）も、サンプルのHRユーザーとデフォルトのパスワードを使用します。

string with_pool =
  "User Id=hr;
   Password=hr;
   Data Source=oramag;
   Enlist=false;
   Pooling=true"

mainメソッドがそれぞれのmemberメソッドを呼び出して、テストを実行します。 1つのmemberメソッド（ConnectionPoolTest）は、現在時刻を取得し、接続テスト・ループを実行して、もう一度現在時刻を取得します。 次に、コードは2つの時刻の差を計算します。

筆者のテスト・システムでは、反復回数を100で実行した場合に次の結果が得られました。

Beginning Connection Pool Test with 100 iterations...
No Pooling: 7.8312608 total seconds.
With Pooling: 0.1301872 total seconds.

ご覧のとおり、接続プーリングがない場合、ループはほぼ8秒で実行されています。 接続プーリングがある場合、ループは約0.13秒で実行され、格段に速くなっています。

ワークロードは大幅に異なることがあるので、接続プーリングのパラメータの具体的な推奨値を設定するのは容易ではありません。 一般的には、最小のプール・サイズを十分大きな値に設定することが推奨されます。 筆者は、活動が一時停止している間に、接続プールが最小値にまで落ち込んでいる状態をたびたび確認しています。 活動が急速に再開すると、接続プールは、プール内の接続を使用する代わりに、迅速に接続を作成する必要があります。 ほとんどの場合、アプリケーション・サーバーは接続数を削減し、比較的アイドルの状態に留めます。 そのため、プール・サイズの最小値を低く設定する必要はありません。

フェッチ・サイズの制御

データベース層からデータを取得して、中間層（またはクライアント層）に渡す操作は、パフォーマンスの点でもっともコストがかかる操作の1つです。 エンドユーザーが大量のデータを使用する場合、アプリケーションによるデータ・フェッチのラウンドトリップ回数を最小限にしようとします。

デフォルトでは、ODP.NETは一度に64KBのデータを結果セットから読み取ります。 この値は、OracleDataReaderオブジェクトのFetchSize属性を設定することで変更できます。 ただし、フェッチ・サイズを任意に設定するのではなく、ODP.NETの機能を使用して、ラウンドトリップごとに結果セットからフェッチされる行数を制御します。 これには、RowSizeとFetchSizeの2つのプロパティを使用します。

RowSizeは、OracleCommandオブジェクトのプロパティです。このプロパティの値は、コマンド・オブジェクトが関連付けられているステートメントが実行されるときにODP.NETによって割り当てられます。 FetchSizeプロパティの値は明示的に設定します。 たとえば、ラウンドトリップごとに結果セットから一度に100行を取得する場合は、FetchSizeプロパティを次のように設定します。

dr.FetchSize = cmd.RowSize * 100;

ここで、drはOracleDataReaderを表し、cmdはOracleCommandオブジェクトを表します。

フェッチ・サイズの利点

フェッチ・サイズを制御する利点を示すために、SQL*Plusを使用して新しいシンプルな表（fetch_test）をHRサンプル・スキーマに作成します。

create table fetch_test
(
  id   number primary key,
  data varchar2(32)
);

そして、100,000行を移入します。

begin
  for i in 1..100000 loop
    insert into fetch_test
    values (i, to_char(i, '000000'));
  end loop;
end;
/

これは、Oracle Developer Tools for Visual Studio .NETなどのツール・スイートを使用しておこなうこともできます。 デフォルトでCOMMITを発行しないツールを使用する場合は、表を移入したあとでかならずCOMMITを発行してください。

表にデータを移入後、オプティマイザがデータへアクセスする適切な方法を決定できるように統計情報を収集します。 筆者はdbms_statsパッケージを使用しました。

begin
  dbms_stats.gather_table_stats(
    ownname =">" 'HR',
    tabname =">" 'FETCH_TEST',
    estimate_percent =">" null,
    method_opt =">" 'for all indexed columns size 1',
    cascade =">" TRUE);
end;
/

次に、接続プーリングに使用したものと同様の次の操作を含むテストを実行します。 現在時刻を取得する、フェッチ・サイズを適切な値に設定する、結果セットからデータを読み取る、現在時刻を再取得する、差異を計算する。 フェッチ・サイズを設定し、データを読み取るために、FetchTestというテスト・メソッドを作成します。このメソッドは、フェッチされる行数を制御する整数パラメータを受け入れます。 フェッチ・サイズを設定し、データを読み取るために、次のコードを使用します。

OracleCommand cmd = con.CreateCommand();

cmd.CommandText = "select * from fetch_test";

OracleDataReader dr = cmd.ExecuteReader();

dr.FetchSize = cmd.RowSize * numRows;

while (dr.Read())
{
rowsFetched++;
}

次に、5つの異なる行の値を使用して、テスト・メソッドを実行しました。 テスト結果のサマリーを次に示します。

ROWS      TIME
________  __________________________

1         5.4378192 seconds
10        1.1115984 seconds
100       0.2703888 seconds
1000      0.200288 seconds
10000     0.1902736 seconds

ご覧のように、"リソースを節約し、パフォーマンスを向上させる"（このアドバイスを耳にすることがよくあります）ために一度に1行だけをフェッチする場合、ランタイム・パフォーマンスは向上せず、大幅に低下することになります。 さらに、このようなデータ・セットの場合、一度に1,000行を超えるデータをフェッチする場合もパフォーマンスはあまり向上しません。 このことは2つの小さな列だけを含む、限られたデータ・セットにも当てはまります。 この方法を自分の使用環境を表す結果セットに適用して、フェッチ・サイズの適切な設定値を決定します。

フェッチ・サイズが恣意的に大きな値である場合、パフォーマンスが低下します。 大量のデータを格納して管理するには、より多くのクライアント側メモリとプロセッサ・サイクルが必要になります。 テストの目的は、ラウンドトリップ回数とトリップごとに取得するデータ量とのバランスにおいて、一番パフォーマンスのよい設定を見つけることにあります。

ステートメント・キャッシュ

アプリケーションでバインド変数を使用するのは効果的です。 バインド変数を使用することで、Oracle Databaseがセッション内でSQL文を再使用し、 ほかの セッションが同じ文を使用できるようになります（バインド変数の利点に関する詳細は、 Oracle Magazine （2005年9月/10月発行）の ODP.NET のコラムを参照してください）。 設計時にアプリケーションでどのステートメントが再使用されるかがわかっている場合、ODP.NETで公開されている機能のステートメント・キャッシュを使用すると、バインド変数によるパフォーマンス向上のメリットがさらに増すことになります。

ステートメント・キャッシュを使用する場合、 自分 のセッションでそのステートメントを再使用するために、サーバーのメモリにキャッシュされているステートメントのコピーを保持するように、Oracle Databaseに指示を出すことになります。 これにより、後続の実行時にOracle Databaseがより迅速にそのステートメントを使用できるようになります。 ステートメント・キャッシュでは、ステートメント自体のランタイム・パフォーマンスは向上しない点に注意してください。 ステートメント・キャッシュを使用すると、Oracle Databaseは、すべてのセッションではなく、ユーザーのセッションに関連付けられているメモリ領域でステートメントを検索でき、ステートメントが再解析されるのを回避できます。 データではなく、ステートメントがキャッシュされる点に注意してください。 キャッシュされたステートメントを実行すると、常に最新のデータベースの結果が戻されます。

ステートメント・キャッシュ機能は、Statement Cache Size接続文字列パラメータを設定して有効にします。 このパラメータのデフォルト値は0です。つまり、ステートメント・キャッシュはデフォルトでは無効になっています。 このパラメータで、アプリケーションのライフタイム中にキャッシュに保持するステートメントの数を設定します。 たとえば、16のステートメントをキャッシュする場合は、この値を16に設定します。すると、ODP.NETは最近使用された16のステートメントをキャッシュします。 使用順序が最新から17番目のステートメントは、16番目の 一意 なステートメントによって、キャッシュから削除されることになります。

ステートメント・キャッシュが有効になっている場合、ODP.NETは実行されるすべてのステートメントを自動的にキャッシュします。 ステートメント・キャッシュが有効で、あるステートメントをキャッシュに配置しない場合は、OracleCommandのAddToStatementCacheのプロパティをFALSEに設定します。

OracleCommand cmd = 
new OracleCommand();

cmd.Connection = con;

cmd.AddToStatementCache = false;

cmd.CommandText = "select data from fetch_test where id = :1";

ステートメント・キャッシュには、SQL文とPL/SQL文を格納できます。

ステートメント・キャッシュの結果

ステートメント・キャッシュのテストには、接続プーリングとフェッチ・サイズの制御に使用したものと同じパターンを使用しました。 まず、fetch_test表を使用して、データを繰り返し選択しました。この際、ステートメント・キャッシュは使用しません。次に、ステートメント・キャッシュを使用して同じテストを繰り返しました。

ステートメント・キャッシュがないテストの接続文字列を次に示します。

string constr =
  "User Id=hr;
   Password=hr;
   Data Source=oramag;
   Enlist=false;
   Pooling=true";

ステートメント・キャッシュがあるテストの接続文字列を次に示します。

string constr =
  "User Id=hr;
   Password=hr;
   Data Source=oramag;
   Enlist=false;
   Pooling=true;
   Statement Cache Size=1";

キャッシュ・サイズを1に設定している点に注意してください。これは、1つのステートメントだけをキャッシュすることが明白であるためです。 バインド変数は変わることがありますが、ステートメントはキャッシュされたままです。 バインド値の代わりにリテラル値を使用すると、Oracle Databaseはキャッシュを使用せずに、リテラル値が変わるたびにステートメントを再解析する必要があります。 また、新しいステートメントが現在キャッシュにあるステートメントと置き換わります。

ステートメント・キャッシュを使用する場合としない場合で、サンプル・ステートメントを各1,000回実行しました。これにより、筆者のシステムでは次の結果が得られました。

No Statement Caching: 
0.6409216 total seconds.
With Statement Caching: 
0.3905616 total seconds.

Mark A. Williams （ mawilliams@cheshamdbs.com ）氏は、Oracle ACEおよびOracle Certified Professional DBAであり、『  Pro .NET Oracle Programming 』（Apress、2004年）の著者でもあります。 また、Oracle on Windowsソリューションに取り組み、Oracle Technology NetworkのOracle Data Provider for .NETに関するフォーラムに貢献しています。