異機種DB間のDBLINK性能測定 [OCI]
秋の訪れを感じる今日この頃である。7月から新しいプロジェクトに移り、仕事内容は維持運用のオンプレトラシューからクラウドを用いる設計に携わるようになり、今更ながらに本格的にクラウド(OCI)を使い始めている。使い始めるとクラウドは本当に便利で、ちょっとした検証をしたいと思ったら短期間にスクラッチから環境を用意できると実感した。今回はDBLINKについてOCIで簡単な性能検証をしたので、その内容について記載したいと思う。
複数のOracleを連携する方法としてすぐ思いつくのはDBLINKである。Oracle間のデータ連携なら、DBLINKを作成することで、リモート側のDBの表をアクセスしたり、DBLINK経由でローカルにMVIEWを作成し、レプリカを作ることが容易にできる。また、Oracleではなく異機種DB、例えばDB2やSQL Serverの場合でもDBLINKを構成することができる。ここでは、OracleとDB2、SQL Server間のDBLINKの実現方式とその制約、そして性能についてOCIで検証してみた結果を記す。
DBLINKは、別のOracleデータベースをOracleNet経由で接続しアクセスするためのスキーマオブジェクトである。tnsnames.oraに接続先Oracleの接続識別子があれば、その接続識別子を指定してDBLINKを作成し、表名@DBLINK名の形式でDBLINK先の表へアクセスすることができる。
この方法の利点は、クライアントから複数のDBへ接続することなく他のDBへアクセスできることである。リモートDB上の表との結合は、ローカルDB上の表の結合と同じようにSQLで記述できる。シノニムやビューを使えば、DBLINKの存在をSQLから隠蔽できるので、リモートDB上にあることすら意識することなくSQLを記述することも可能である。更新では2フェーズコミットを利用できる。DBLINKがなければ、クライアントAPはそれぞれのDBにアクセスし、それぞれから必要なデータを取得し、結合、ソート、集計し、更新の場合はコミットの制御までを行わなければいけないだろう。
一般的にDBLINKはOracleデータベース間の接続に利用するが、Oracle Gatewayを使えば異機種データベースに対して接続することも可能である。
Oracle GatewayはOracleデータベースと異機種DBとの間のゲートウェイであり、実体はOracleリスナーとそこから起動される異機種DB用のサーバプロセスである。Oracle Gatewayを構成すると、接続元のOracleは通常のDBLINKと全く同じように異機種へのDBLINKを構成できる。DBLINKの定義で指定する接続識別子は、Oracle Gatewayのリスナーに接続するように構成し、SIDによりDB2やSQL Serverといった接続先DBを識別し、それぞれのDBへアクセスするサーバプロセスを起動する（接続元のセッションが切れない限り、このサーバプロセスも維持される）。
逆に異機種DBからの接続は、そのDBの機能に依存する。例えばDB2であればFederation、SQL ServerであればLinked Serverを使うことでOracleデータベースへ接続することが可能である。いずれもDBサーバ上にOracleクライアント(Oracle Net)を構成し、Oracleデータベースに接続する設定を行うことで、DBLINKのように外部DBへのSQLを発行することができるようになる。
DBLINKが万能ではないことはよく知られている。DBLINK経由では基本的に結合はローカル側で実行するため、DBLINK先の表のすべての行をリモートから転送することになる。リモート表が大きいとこの転送量が多くなり、NWの転送時間がかかりクエリの性能遅延につながる。転送するデータ量を少なくするためには、リモートDB側でデータ量を絞ることが望ましい。しかし、ローカルの場合と比べ実行計画を制御する方法は限られている。DBLINK先のオブジェクトに対してパラレルクエリやパラレルDMLのような力業に頼る性能チューニングは使えない。バラバラになった複数のDB上で実行されるSQLをチューニングすることは、単一DBのSQLをチューニングすることよりはるかに難しいことは想像に難くない。
また、可用性の観点では、リモートDB側の計画停止や障害発生がローカルのSQL実行に影響することとなるため、連携するDBが多くなればなるほど、システム全体の可用性は低下するだろう。運用上、DBリリースやメンテナンス、パッチ適用に伴う停止調整に伴う影響確認などの運用が複雑となるだろう。
アプリケーションの開発生産性の観点では、DBLINKは魅力的であるのは理解できる。しかし、そのトレードオフとして上記のような制約は生じることは理解しておく必要がある。
実際にOracle、DB2、SQLServer間のDBLINKをテストする環境をOCI上で構築してみた。OracleはDBCS、DB2はOracle Linuxのコンピュート、SQL ServerはWindowsのコンピュートを作成し、その間を相互にDBLINKで接続できるようにした。Oracleデータベースは、DBCS上にPDBを2つ作成し、Oracle-Oracle間のDBLINKを測定できるようにした。
各サーバのスペック、OS、DBを以下に示す。
DBLINKの構成に利用した製品は以下の通りである。
今回、DB2とSQL Serverは構築から初めてだったので、参考[1][2]の書籍を活用した。今回の検証ではDBの構築だけでなく、試験データを準備したり、SQLの実行計画を取得したりと一通りの操作が必要だったので大変参考になった。
一方、Oracle GWは参考[3]のオンラインマニュアルを参考に構築した。OracleGWの構成方法には、DBサーバ上に構成する方法と、DBサーバと別に独立したGW専用サーバを立てる方法があるが、今回はDBCS上の$ORACLE_HOMEにインストールし、通常のgridのリスナーとは別にoracleのリスナーを別ポートで起動することとした。Oracle GWは$ORACLE_HOME配下にdg4db2やdg4msqlといったディレクトリを追加するので、DBCSの環境に影響はなくはないので、本当はディレクトリを分けた方が無難かのもしれない。
EMP表（100万件）、DEPT表（10万件）を結合し全件取得するSQLの処理時間を測定した。DEPT表はローカルの場合とリモートの場合で測定する（EMP表は常にローカル）。SQLの実行計画は①フルスキャン＋ハッシュ結合、②EMP表を駆動としたネスティッドループ（DEPT索引アクセス）の2パターンとする。具体的なSQLは下図を参照されたい。
なお、DB2とSQLの実行計画の書き方は自信がないので、実行計画を取得して確認した。若干不安な部分がない訳ではないが、少なくとも結合方法は所望の方法になっていることは確認した。
テスト結果は以下の通り。
OCI上でOracleとDB2、SQL Server間でDBLINKを構成し、リモートDBのデータが参照できることを確認した。また、SQLでリモートDB上の表と結合することが可能なことも確認できた。
性能については、ローカルに比べてHASH結合はリモートDBアクセスのオーバーヘッドは小さく、Nested Loopの方が遅延幅が大きいことが確認できた。今回の構成ではDBは全て同じOCIのサブネット内にあり、かつDEPT表は比較的小さかったので、全レコードを転送する時間はクエリの処理時間に比べると小さかったと考えられる。一方、DEPTの索引アクセスについてはリモートへの細かなアクセスが多発することにより、リモートDBアクセスのレイテンシの遅延が蓄積し、遅延幅が大きくなったと考えられる。
また、OracleやDB2の場合、Nested Loopでリモート表にアクセスする場合、リモートDB(Oracle)のSQLはプレディケート(where句)が付与され、主キー一本引きのSQLになっていた。つまり、リモートDB側ではDEPT表へのSQLはselect * from dept where deptno = XXが駆動表の件数と同じ100万回発行されていた。
一方、SQL ServerではリモートDB側（Oracle）にプレディケートが付与されておらず、また実行件数は1回のみであった。このことから、SQLServerでは、おそらくDEPT表の全件取得した結果セットに対してメモリ上でNested Loop結合を実行していたと思われる。これでは索引アクセスは使えないため、極端に性能が悪い結果になったと思われる。今回のようなケースで実行計画をNested Loopにすることはないとは思うが、DBによってこのあたりの挙動が異なることがわかった。
◇参考
[1]即戦力のDB2管理術
[2]SQL Server 2016の教科書 開発編
[3]Oracle Gateway Installation and Configuration Guide, Part V Installing and Configuring Oracle Database Gateway for SQL Server, Part VII Installing and Configuring Oracle Database Gateway for DRDA
https://docs.oracle.com/en/database/oracle/oracle-database/19/otgis/index.html
複数のOracleを連携する方法としてすぐ思いつくのはDBLINKである。Oracle間のデータ連携なら、DBLINKを作成することで、リモート側のDBの表をアクセスしたり、DBLINK経由でローカルにMVIEWを作成し、レプリカを作ることが容易にできる。また、Oracleではなく異機種DB、例えばDB2やSQL Serverの場合でもDBLINKを構成することができる。ここでは、OracleとDB2、SQL Server間のDBLINKの実現方式とその制約、そして性能についてOCIで検証してみた結果を記す。
１．前提知識
DBLINKは、別のOracleデータベースをOracleNet経由で接続しアクセスするためのスキーマオブジェクトである。tnsnames.oraに接続先Oracleの接続識別子があれば、その接続識別子を指定してDBLINKを作成し、表名@DBLINK名の形式でDBLINK先の表へアクセスすることができる。
この方法の利点は、クライアントから複数のDBへ接続することなく他のDBへアクセスできることである。リモートDB上の表との結合は、ローカルDB上の表の結合と同じようにSQLで記述できる。シノニムやビューを使えば、DBLINKの存在をSQLから隠蔽できるので、リモートDB上にあることすら意識することなくSQLを記述することも可能である。更新では2フェーズコミットを利用できる。DBLINKがなければ、クライアントAPはそれぞれのDBにアクセスし、それぞれから必要なデータを取得し、結合、ソート、集計し、更新の場合はコミットの制御までを行わなければいけないだろう。
一般的にDBLINKはOracleデータベース間の接続に利用するが、Oracle Gatewayを使えば異機種データベースに対して接続することも可能である。
Oracle GatewayはOracleデータベースと異機種DBとの間のゲートウェイであり、実体はOracleリスナーとそこから起動される異機種DB用のサーバプロセスである。Oracle Gatewayを構成すると、接続元のOracleは通常のDBLINKと全く同じように異機種へのDBLINKを構成できる。DBLINKの定義で指定する接続識別子は、Oracle Gatewayのリスナーに接続するように構成し、SIDによりDB2やSQL Serverといった接続先DBを識別し、それぞれのDBへアクセスするサーバプロセスを起動する（接続元のセッションが切れない限り、このサーバプロセスも維持される）。
逆に異機種DBからの接続は、そのDBの機能に依存する。例えばDB2であればFederation、SQL ServerであればLinked Serverを使うことでOracleデータベースへ接続することが可能である。いずれもDBサーバ上にOracleクライアント(Oracle Net)を構成し、Oracleデータベースに接続する設定を行うことで、DBLINKのように外部DBへのSQLを発行することができるようになる。
２．DBLINKは万能ではない
DBLINKが万能ではないことはよく知られている。DBLINK経由では基本的に結合はローカル側で実行するため、DBLINK先の表のすべての行をリモートから転送することになる。リモート表が大きいとこの転送量が多くなり、NWの転送時間がかかりクエリの性能遅延につながる。転送するデータ量を少なくするためには、リモートDB側でデータ量を絞ることが望ましい。しかし、ローカルの場合と比べ実行計画を制御する方法は限られている。DBLINK先のオブジェクトに対してパラレルクエリやパラレルDMLのような力業に頼る性能チューニングは使えない。バラバラになった複数のDB上で実行されるSQLをチューニングすることは、単一DBのSQLをチューニングすることよりはるかに難しいことは想像に難くない。
また、可用性の観点では、リモートDB側の計画停止や障害発生がローカルのSQL実行に影響することとなるため、連携するDBが多くなればなるほど、システム全体の可用性は低下するだろう。運用上、DBリリースやメンテナンス、パッチ適用に伴う停止調整に伴う影響確認などの運用が複雑となるだろう。
アプリケーションの開発生産性の観点では、DBLINKは魅力的であるのは理解できる。しかし、そのトレードオフとして上記のような制約は生じることは理解しておく必要がある。
３．OCIのテスト環境構築
実際にOracle、DB2、SQLServer間のDBLINKをテストする環境をOCI上で構築してみた。OracleはDBCS、DB2はOracle Linuxのコンピュート、SQL ServerはWindowsのコンピュートを作成し、その間を相互にDBLINKで接続できるようにした。Oracleデータベースは、DBCS上にPDBを2つ作成し、Oracle-Oracle間のDBLINKを測定できるようにした。
各サーバのスペック、OS、DBを以下に示す。
DBLINKの構成に利用した製品は以下の通りである。
今回、DB2とSQL Serverは構築から初めてだったので、参考[1][2]の書籍を活用した。今回の検証ではDBの構築だけでなく、試験データを準備したり、SQLの実行計画を取得したりと一通りの操作が必要だったので大変参考になった。
一方、Oracle GWは参考[3]のオンラインマニュアルを参考に構築した。OracleGWの構成方法には、DBサーバ上に構成する方法と、DBサーバと別に独立したGW専用サーバを立てる方法があるが、今回はDBCS上の$ORACLE_HOMEにインストールし、通常のgridのリスナーとは別にoracleのリスナーを別ポートで起動することとした。Oracle GWは$ORACLE_HOME配下にdg4db2やdg4msqlといったディレクトリを追加するので、DBCSの環境に影響はなくはないので、本当はディレクトリを分けた方が無難かのもしれない。
４．テスト内容
EMP表（100万件）、DEPT表（10万件）を結合し全件取得するSQLの処理時間を測定した。DEPT表はローカルの場合とリモートの場合で測定する（EMP表は常にローカル）。SQLの実行計画は①フルスキャン＋ハッシュ結合、②EMP表を駆動としたネスティッドループ（DEPT索引アクセス）の2パターンとする。具体的なSQLは下図を参照されたい。
なお、DB2とSQLの実行計画の書き方は自信がないので、実行計画を取得して確認した。若干不安な部分がない訳ではないが、少なくとも結合方法は所望の方法になっていることは確認した。
５．テスト結果
テスト結果は以下の通り。
６．考察
OCI上でOracleとDB2、SQL Server間でDBLINKを構成し、リモートDBのデータが参照できることを確認した。また、SQLでリモートDB上の表と結合することが可能なことも確認できた。
性能については、ローカルに比べてHASH結合はリモートDBアクセスのオーバーヘッドは小さく、Nested Loopの方が遅延幅が大きいことが確認できた。今回の構成ではDBは全て同じOCIのサブネット内にあり、かつDEPT表は比較的小さかったので、全レコードを転送する時間はクエリの処理時間に比べると小さかったと考えられる。一方、DEPTの索引アクセスについてはリモートへの細かなアクセスが多発することにより、リモートDBアクセスのレイテンシの遅延が蓄積し、遅延幅が大きくなったと考えられる。
また、OracleやDB2の場合、Nested Loopでリモート表にアクセスする場合、リモートDB(Oracle)のSQLはプレディケート(where句)が付与され、主キー一本引きのSQLになっていた。つまり、リモートDB側ではDEPT表へのSQLはselect * from dept where deptno = XXが駆動表の件数と同じ100万回発行されていた。
一方、SQL ServerではリモートDB側（Oracle）にプレディケートが付与されておらず、また実行件数は1回のみであった。このことから、SQLServerでは、おそらくDEPT表の全件取得した結果セットに対してメモリ上でNested Loop結合を実行していたと思われる。これでは索引アクセスは使えないため、極端に性能が悪い結果になったと思われる。今回のようなケースで実行計画をNested Loopにすることはないとは思うが、DBによってこのあたりの挙動が異なることがわかった。
◇参考
[1]即戦力のDB2管理術
即戦力のDB2管理術 ～仕組みからわかる効率的管理のノウハウ
- 作者: 下佐粉 昭
- 出版社/メーカー: 技術評論社
- 発売日: 2011/04/08
- メディア: 単行本（ソフトカバー）
[2]SQL Server 2016の教科書 開発編
- 出版社/メーカー: ソシム
- 発売日: 2016/07/26
- メディア: 単行本
[3]Oracle Gateway Installation and Configuration Guide, Part V Installing and Configuring Oracle Database Gateway for SQL Server, Part VII Installing and Configuring Oracle Database Gateway for DRDA
https://docs.oracle.com/en/database/oracle/oracle-database/19/otgis/index.html
2021-09-27 19:10
