oracleの統計情報メンテナンス

gamiyama3110 commented 4 years ago

全体は多いけど絞れば大した量にならないselectが10秒以上掛かっている。インデックスも最適ではないにしろ、それなりに適切（なんだそれ）に付いてる。インデックスにはカーディナリティが低い（ 1 ）カラムが含まれていて、よくあるdel_flgってやつ。運用されていないため、全て 0 になっている。これのパフォーマンス改善を実施。

sqlのボトルネックを探してみた。複数あるselectのうち、1つに10秒以上掛かる箇所があった。実行計画を確認しながら、結果が変わらないようにwhere句の条件を変えてみた結果、del_flgの条件を外すだけでパフォーマンスが改善した。しかし、sqlはAPやバッチスクリプト等に同様のロジックが配布されている状況なので、sqlの改修を優先させたくなかった。

アプローチ・インデックスからdel_flgを消す・論理削除できそうなレコードにdel_flgを立てる・sqlを改修

gamiyama3110 commented 4 years ago

テーブルをクローンし、del_flgを立てる準備を進めた。

ここでテーブルのクローンを用意して気が付いたこと。複製したテーブルにはインデックスも同等のもの設定し、ボトルネックとなっていたsqlを実行した結果、実行時間が短縮された。適用されているインデックスは同じものだったことから、インデックスになにか問題があるのかと調べた結果、インデックスを再構築する手順があったので実施。 https://www.shift-the-oracle.com/alter-index/alter-index-rebuild.html

結果、12秒掛かっていたものが半分程度に短縮されたが、del_flgを外した時のパフォーマンスにはならなかった。

メモ onlineオプションはエンタープライズ版でないと使えない。インデックス再構築中は表ロックが掛かり、追加・更新が行えなくなるがselectには影響なし。

gamiyama3110 commented 4 years ago

で、del_flgを立てる準備を進めている中、フラグ立てるくらいじゃ改善しない予感がしたので別アプローチをする。

複製したテーブルと既存テーブルの実行計画を眺めていたら、既存テーブルに info type = "dynamic_sampling" note="y" : 2 の結果が出力されているが、複製したテーブルへのselectでは出力されていなかった。

dynamic_sampling とは？動的統計 https://techlab.sixsquare.co.jp/archives/1011#i インデックスを選択するオプティマイザが参考とする情報に統計情報を利用するが、その統計情報が利用できない場合に参照する統計情報。複製直後のテーブルなら、動的統計が適用されそうだが、時間を置いてから複製テーブルにselectすると同じパラメータ結果が出力されるようになった。

統計情報を調べていると、何かしらのきっかけで統計情報が更新されず、動的統計が適用されるようだった。

SELECT /*+ dynamic_sampling(0) */ FROM

https://ameblo.jp/archive-redo-blog/entry-10045293985.html こんな形でコメントとしてsqlに含めるとそのselectで動的統計の機能をoffにすることができる。これでボトルネックのsqlを検証した結果、パフォーマンスが改善された。動的統計をOFFにすることが容易でないため、動的統計が適用される原因を探るため統計情報を確認する。

統計情報はインデックス単位、テーブル単位で持っている。

インデックス

SELECT TABLE_NAME NAME
     , LAST_ANALYZED
  FROM USER_TABLES
 WHERE TABLE_NAME = 'hoge_table'
 UNION ALL
SELECT INDEX_NAME
     , LAST_ANALYZED
  FROM USER_INDEXES
 WHERE INDEX_NAME in ( 'hoge_table_ind_001', 'hoge_table_ind_002' )

テーブル

SELECT
  TABLE_NAME NAME
  , LAST_ANALYZED
  , STATUS
FROM
  USER_TABLES
WHERE
  TABLE_NAME = 'hoge_table'; // 大文字小文字区別する

インデックスは再構築をしたので、最終更新日は新しい。しかし、テーブルは2年も前で止まっていた。

これにより、テーブルの統計情報の再構築に向けて調べる。

gamiyama3110 commented 4 years ago

統計情報は再構築することでパフォーマンスが改善することも、しないこともある模様。 https://www.projectgroup.info/tips/Oracle/Oracle_000001.html

この辺りの検証については統計情報が蓄積されていないと検証できないのでモドカシイ。 →クローンしたテーブルの統計情報は18年で止まってた。もうわけわからん

https://www.projectgroup.info/tips/Oracle/Oracle_000001.html

テーブルの全件から統計情報を収集します。
analyze table テーブル名 compute statistics;

10%のデータをサンプリングして統計情報を収集します。
analyze table テーブル名 estimate statistics sample 10 percent;

テーブルの統計情報を削除します。
analyze table テーブル名 delete statistics;

全件から更新 or 10%から更新 or 消す

再構築してみた。

81839538 カウント総数、これで30分くらい

https://www.oracle.com/technetwork/jp/ondemand/db-new/d-9-screen-1448391-ja.pdf

再構築中にクエリ、コマンド共に影響ないことを確認できたのは収穫。

gamiyama3110 commented 4 years ago

product環境で実施した。結果としては、好転した。

ボトルネックとなっていたsqlは1秒を切る様になったが、条件を変えアクセスすると稀に10秒弱になる時がある。今回、統計情報の更新により、動的統計の適用が避けられ、オプティマイザが最適なインデックスを取得するまでの時間が短縮されたように思う。ソフトパースが適用されればパフォーマンスは良く、ハードパース（条件を変えてみるとか）だと時間が掛かってしまうのではないかなと。

実施後、失敗だったなと思ったこと。ボトルネックのsqlはバインド変数を使っていたが、検証中は変数を定数に書き換えて検証していた。つまり、ソフトパースで適用されるものがハードパースで動く様になっていたはずで、検証としては正しいものではなかったのではないかなと。 sqldeveloperでもバインド変数を使ったクエリが投げられるのをすっかり忘れてた（テヘペロ）

一先ず、月末に該当sqlが呼び出される回数が増えるので、スクリプトの実行時間等を見ながら様子見をする。

gamiyama3110 / gamiyama.github.io

oracleの統計情報メンテナンス #6