oracle バッチ処理が遅い - おらくるのいる生活

11gから19cにアップグレードした環境で、バッチ処理が遅いのでチューニングして欲しいとの連絡がありました。

環境は以下の通りです。

Oracle Restart（SIHA） SE2 19.15.0.0
Microsoft Windows

アップグレードした環境なので、19cになった事で遅くなったのかと思いましたがそうでは無く、最初から遅かったようです。つまり、正常時と遅延時を比較して遅延原因を探る手法は取れません。

しかもSQLが遅いのか、Cで作成されているプログラムの作りの問題なのか分からないとの事。

更に言えばSEなので、EEで（オプションを購入していれば）使用できるチューニング関連の数々の機能がどれも使えません。

仕方ないので現行と新環境で問題のバッチを実行し、statspackを取得して貰いました。

が、新環境でTop 5 Timed Eventsにアイドル待機イベントが含まれるというバグを踏んでしまいました。

12.1ではバグがあるけど12.2で直った筈では…と思ったのですが、別の不具合でした。

12.2以降の不具合

12.2以降 STATSPACK: Top 5 Timed Eventsに'Data Guard: Timer'のようなアイドル待機イベントが含まれる (ドキュメントID 2453074.1)

12.1の不具合

新しいアイドル・イベントが誤って STATSPACK レポートに表示される (ドキュメントID 2106498.1)

12.2以降の不具合については、以下のSQLで欠落しているアイドル待機イベントをSTATS$IDLE_EVENT表に追加する事で回避できます。

connect perfstat/<PASSWORD>

insert into stats$idle_event
select name from v$event_name where wait_class='Idle'
minus
select event from stats$idle_event;

commit;

対処後にもう一度statspackレポートを取って貰ったので、どこに問題があるのか見て行きます。

まず Top 5 Timed Eventsを確認しましたが、殆どがCPU時間で、バグを踏んでいそうな怪しい待機も無ければ、長時間のIO待機もありません。

また、キャッシュ・ヒット率はほぼ100％でした。

次に「SQL ordered by Elapsed time」を参照して遅いSQLを確認したところ、特に遅いSQLは2つで、その2つが遅いとされている10のバッチの殆どに登場する事が判りました。

そこでチューニング対象をその2つのSQLに絞りましたが、ここではその内の一つについて取り上げます。

絞り込んだSQLについて、statspackのsqlレポートを取得して貰いましたが、statspackだけでは情報量が少ないので、sqlhc（SQLヘルスチェック）も取って貰いました。

取得方法は以下ドキュメントの通りです。

SQL チューニング・ヘルスチェック・スクリプト (SQLHC) (ドキュメントID 1542531.1)

SQLHCの良いところは、対象SQLで使われているテーブルと、そのテーブルに付与されている全てのインデックスの統計関連報が一覧できる事です。

勿論、statspackのsqlレポート同様に実行計画も確認できます。

更にはテーブルとインデックスのDDLも付いているので、検証環境でのテストにも役立ちます。

sqlレポートを確認して判明したのは

更にSQLHCを確認して判明したのは

ここまで判れば答えは出たようなものですが、順に説明して行きます。

まず「選択性」について。

セレクティビティ(選択率)とは、返される行数の割合を見積もったもので、イコール検索の場合は通常以下の式で見積もられます。

選択率 = 1/ Distinct Keys(列値の種類の数)

具体的に言うと、値が2種類くらいしかないカラムだけから構成されているインデックスなので、カーディナリティが高く（＝問い合わせで処理される行数が多い）、その為論理読み込みが多くなり、それが遅延の原因となった訳です。

以下に、実際のSQLを単純化した例を挙げます。

select count(*) from tab1 where a_kbn= :b0 and b_cd = :b1 and substr(c_no,1,6 = :b2);

ここで、a_kbnの値は1種類しかなく、b_cdの値は2種類のみ、c_noは18万種類くらいあり、テーブル件数は19万程度。

実際はここまで単純ではありませんが、概ね、こういう作りです。

で、使われたインデックスidx_1はa_kbn,b_cdのみが含まれ、使われなかったインデックスidx_2はc_noが含まれているものの、SQLでsubstr関数を使用しているので、選択性はidx_2の方がずっと低いのに使用されなかった訳です。

であれば、SQLで関数が使用されていてもインデックスが使われるように、関数インデックスを作ってあげれば良い訳です。

create index idx_f1 on tab1 (a_kbn,b_cd,substr(c_no,1,6));

結果として論理読み込みが激減、バッチ処理時間は3時間20分程度から、9分足らずまで改善しました。