本番リリースを間近に控えた環境で、統計情報取得処理の遅延が発生しているのでサポートして欲しいと連絡がありました。
旧環境は11g、新環境は19cなのですが、同じテーブル定義、同じデータであるにも関わらず、処理が遅くなったというものです。
環境は以下の通りです。
問題の処理はバッチの中で実行されているもので、データの洗い替え処理を行い、直後にテーブル単位で統計情報取得をしています。
ログを見てみます。
旧環境
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(
3 OWNNAME => 'OWN_NAME'
4 ,TABNAME => 'TAB_NAME'
5 ,CASCADE => TRUE
6 );
7 END;
8 /
PL/SQLプロシージャが正常に完了しました。
経過: 00:22:23.35
新環境
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(
3 OWNNAME => 'OWN_NAME'
4 ,TABNAME => 'TAB_NAME'
5 ,CASCADE => TRUE
6 );
7 END;
8 /
PL/SQLプロシージャが正常に完了しました。
経過: 01:18:26.29
旧環境で約22分だったのが新環境では約1時間20分なので、明らかに遅延しています。
テーブルとインデックスのDDLをもらって検証環境でも再現するかテストしてみました。
11g
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(
3 OWNNAME => 'OWN_NAME'
4 ,TABNAME => 'TAB_NAME'
5 ,CASCADE => TRUE
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:58.32
19c
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(
3 OWNNAME => 'OWN_NAME'
4 ,TABNAME => 'TAB_NAME'
5 ,CASCADE => TRUE
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:04:27.79
データ量が1GBに満たないと差が出なかったのですが、5GB程度に増やしたら明らかな差が見られました。
ちなみに、本番環境でのデータ量は250GB程度です。
早速MOS(MyOracleSupport)で調べてみます。
Bug 25404996 - gather_table_stats runs slow with subpartition granularity (ドキュメントID 25404996.8)
granularity=>'SUBPARTITION' 指定で統計情報を取得すると遅くなるという不具合で、granularityは指定していないので、該当しなさそうです。
Massive Performance Degradation On Statistics For Large Partitioned Tables After Upgrade To 19c (ドキュメントID 2611336.1)
こちらはビットマップ・インデックスの統計情報取得に関し、19cで導入された新機能のせいで遅延が発生するというものです。
ビットマップ・インデックスは使用していないので、こちらも該当しなさそうです。
AWRでSQLレポートを確認すると、11gに比べて19cはBuffer Getsが41倍、Disk Readsが約24倍になっていました。
通常のチューニングですと、実行計画が変わったことで性能劣化が発生したのではないかと疑うところですが、統計情報取得処理はOracle側で用意しているプロシージャですからね…
こちらで悩んでいても仕方ないので、DDLと再現手順、AWRレポート、10053、10046 トレースも付けてサポートに問い合わせを上げました。
ちなみに、10053は Cost Based Optimizer(CBO) がどのような流れでその実行計画を選択したかトレースするもので、以下の様に設定します。
alter session set max_dump_file_size = UNLIMITED;
alter session set events '10053 trace name context forever, level 1';
<調査対象の SQL 文>
alter session set events '10053 trace name context off';
10046はいわゆるSQLトレースですが、レベルを指定する事で、sql_trace=trueを設定するよりも詳細な情報が取得できます。
設定方法は以下の通りです。
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';
<調査対象の SQL 文>
alter session set events '10046 trace name context off';
で、サポートからの回答を待つ事およそ1か月…
結論から言うと、開発部門で調査中の不具合に該当するようです。
原因はまだ調査中ですが、「DBMS_STATS.GATHER_TABLE_STATS の DEGREE を 32に指定することでの性能向上が確認されている」との事でした。
つまり並列度を32にしろとの事ですが、さすがにそこまで多重度を上げたら他の処理に悪影響が出そうです。
それで検証環境でDEGREE=>8で試したところ性能向上が見られたので、本番環境でもテストしてもらいました。
その結果、旧環境とほぼ同等のパフォーマンスが得られたので、バッチ内で実行している統計情報取得処理についてはDEGREE=>8を設定する事になりました。
統計情報取得処理はバッチ内で実行している以外にも、自動化メンテナンスジョブでも実行されています。
そちらの多重度はDB全体、スキーマ単位またはテーブル単位で変更可能で、スキーマ単位で変更するコマンドは以下の様になります。
SQL> begin
2 DBMS_STATS.SET_SCHEMA_PREFS (
3 OWNNAME => 'OWN_NAME',
4 pname => 'DEGREE',
5 pvalue => 8);
6 end;
7 /
PL/SQLプロシージャが正常に完了しました。
サポートに問い合わせを上げてから5か月が経ちましたが、この問題はいまだに調査中のままです。
回避策があるし、インスタンスがクラッシュするような重大な問題では無い(と、Oracleには思われている)のでこのままずっと調査中のままかも知れません…
