※ 本記事は、Emiel Ramakers, Julien Silverstonによる”Tune fetch size to optimize large query performance in Oracle Database Service for Azure“を翻訳したものです。

2023年8月4日

Oracle Database Service for Azure (ODSA)を使用するスプリット・スタック・アーキテクチャを持つマルチクラウド・アプローチは、Azureのデータベース・クライアントからOracle Cloud Infrastructure (OCI)のデータベースに接続しています。すべてのコンポーネントがローカルで実行されている環境からスプリット・スタック・アーキテクチャに移行する場合、パフォーマンスを最適化するためにネットワーク・パラメータを調整する必要がある場合があります。最初のアクションの1つは、問合せのフェッチ・サイズを確認する必要があります。

背景

問合せから多数の行を受信するには、複数のネットワーク・ラウンド・トリップが必要です。ラウンド・トリップの数は、結果セット内の合計行数とフェッチ・サイズ(データベースへの各トリップで返される行数)の関数です。そのため、同じ問合せ結果に対して、フェッチ・サイズを調整してラウンド・トリップの数に影響を与えることができます。クライアントとデータベースが同じネットワーク上に共存する環境では、ネットワーク・ラウンド・トリップの数は通常、パフォーマンスの決定要素ではないため、フェッチ・サイズの影響を考慮する必要はほとんどありません。

ネットワーク・ラウンド・トリップは、レイテンシのある環境で検討することが重要です。たとえば、異なるクラウド間でスプリット・スタック・アーキテクチャを使用する場合などです。問合せで多くのネットワーク・ラウンド・トリップが必要な場合、各ラウンド・トリップで待機時間がわずかに増加しても、パフォーマンスに大きく影響する可能性があります。そのため、この状況では、ラウンド・トリップの数をできるだけ減らすことが重要です。

フェッチ・サイズのチューニング

結果セットに10,000行あり、フェッチ・サイズが10の場合は、データを取得するためのネットワーク・コールが1,000件あります。フェッチ・サイズを200に変更した場合、必要なネットワーク・コールは50のみです。これらの相違点は、待機時間が短い環境では無視できる可能性があります。ただし、ある程度の待機時間がある環境では、特に多くの行を返す問合せを処理する際に、フェッチ・サイズを正しくチューニングする必要があります。

フェッチ・サイズを大きくすると、クライアント・アプリケーションは、返されるすべての行を1つのフェッチに格納するために、より多くのメモリーを使用します。フェッチ・サイズは、使用するツールおよび接続に応じて、次の例のように様々な方法で影響を受けます。:

  • SQL*Plusを使用して、配列サイズまたはrowprefetchパラメータのいずれかでフェッチのサイズを制御できます。この例は、rowprefetchを使用する際の劇的な影響を示しています。

  • JDBCを使用して独自のアプリケーションに変更を加えるには、「フェッチ・サイズの設定」に記載されているsetFetchSizeメソッドを使用します。

  • Oracle Call Interface (他のOCI)を使用して独自のアプリケーションを変更するには、「プリフェッチ数の設定について」の説明に従って、OCI_ATTR_PREFETCH_ROWSおよびOCI_ATTR_PREFETCH_MEMORYパラメータを使用します。

  • 異なる環境を使用して独自のアプリケーションに変更を加えるには、サード・パーティのドライバでフェッチ・サイズを設定する方法があります。たとえば、Perlでは、MetaCPANのDBI用のDBD::Oracleドライバによって、行プリフェッチを調整する方法が提供されます。

  • これらのパラメータの調整に直接アクセスできないOracle Call Interfaceを使用して記述されたサード・パーティ・アプリケーションを使用している場合、oraaccess.xmlファイルのプリフェッチ・パラメータを使用して、アプリケーションの設定を上書きできます。この方法は、「oraaccess.xmlのグローバル・パラメータの指定について」に記載されています。

再現可能な例

次の簡単な例は、チューニング・フェッチ・サイズの大きな影響を示しています。データベース・ユーザーが最も多く作成し、そのユーザーとして、200万行の単純なテスト表を作成しました。:

CREATE TABLE TEST (COL_A VARCHAR2(10))
/
BEGIN
  FOR c IN 1..2000000 LOOP
    INSERT INTO TEST VALUES (’aaaaaaaaaa’);
  END LOOP;
END;
/
COMMIT;

クライアントで、test.sqlファイルを作成します:

set pagesize 0
set rowprefetch 1
connect perftest/[PASSWORD]@[connectstring]
select * from test;
exit

次に、このスクリプトの実行時間を計測し、データベースとネットワークにかかる時間のみを測定できるように、結果を画面に出力する時間を測定しません。:

time sqlplus /nolog @test.sql >/dev/null

次に、test.sqlファイルのrowprefetchを15、50、100、500および1,000に調整し、それらの各値に対して同じ操作を再度実行します。

テスト・ケース結果

ロンドン地域でODSAを使用してこのテスト・ケースを実行しました。Base DatabaseをVM.Standard2.2シェイプで使用し、Oracle Linux 8.5でAzure Standard DS1 v2仮想マシン(VM)から接続しました。これらのテストを3回実行し、その結果を平均化しました。リージョンやシェイプが異なると、結果がわずかに異なる場合があります。しかし、全体の平均は同じままです。

rowprefetch設定

Azureのクライアントからの平均問合せ時間(秒)

1 (デフォルト)

340

20

190

50

80

100

56

500

16

1,000

11

これらの結果は、フェッチ・サイズが小さいと、指数関数的に大きな影響を与える可能性があることを示しています。この例では、rowprefetch設定を最適化すると、問合せのレスポンス時間が340秒(6分未満)から11秒に短縮されることがわかります。

まとめ

マルチクラウド環境に移行し、大きな結果セットを返す問合せを使用すると、問合せのフェッチ・サイズをチューニングしてパフォーマンスを最適化できます。

Oracle Database Service for AzureおよびOracle Cloud Infrastructureの詳細は、次のリソースを参照してください。: