サーバ構成とアプリの仕様から問題発生まで
まずはサーバ構成とアプリの仕様から問題発生までを下の図で示す。
原因調査
まずは原因調査のため発行されたSQLの実行計画について11gと12cでそれぞれ取得した
- 11gの実行計画
テーブルBで論理削除コード='1'を絞り、テーブルAにテーブルBの絞り込んだ結果でアクセスしている - 12cの実行計画
テーブルAからキーAを全件取得し、取得した分、テーブルBにアクセスしている
次にテーブルA、Bの情報を収集し、以下の点が判明した
- テーブルA,Bともにデータ件数は500万件
- テーブルBで論理削除レコード='1'は2000件程度
原因の特定
SQLの構造と実行計画から、11gから12cに変更したことでDBLink越しの通信が増えたからではないかと推測した。
つまり、11gの時は「テーブルBで論理削除レコード='1'」の件数分、すなわち2000回リモートアクセスが発生していたが、12cに変更した後はテーブルAの件数分、すなわち500万回リモートアクセスが発生していると
試しに、カーソルの宣言で記載しているSQLをテーブルBの取得を先に評価するように直してみた
Selet * from テーブルB main where Exists(
select 1 from テーブルA@DB_A sub where main.キーA = sub.キーB
) where main.論理削除コード ='1'
修正後のSQLをOracle12cの環境で実行してみたところ、実行計画も実行時間も11gと同様のものになった
終わりに
DBLINK越しのSQLを作るときは、当たり前だがリモートアクセスをなるべく発生させないようクエリを組もう。
Oracleのオプティマイザに頼るとひょんなことから非効率なアクセスパスになってしまうことがあるのだから。