小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。


Oracleで大量データの更新後はオプティマイザ統計収集をしたほうが良い。


photo credit: bitzcelt via photopin cc

はじめに

Oracle DBにPL/SQLで数百万オーダのレコードをinsertして性能試験を行ったところ、極端な性能劣化が発生しました。単純なselectクエリでさえ数十秒待たされてしまう状況でした。色々と試してみましたが、オプティマイザ統計収集を手動で実施したら性能劣化が解消しました。

新・門外不出のOracle現場ワザ エキスパートが明かす運用・管理の極意 (DB Selection)
小田 圭二 大塚 信男 五十嵐 建平 谷 敦雄 宮崎 博之 神田 達成 村方 仁
翔泳社
売り上げランキング: 139,394

オプティマイザ統計とは

表や索引、また使用している領域、データの種類、データの分布などのデータ特性を表す情報です。
統計情報は Oracle Database がSQLの実行計画を生成する際に利用されています。
Oracleの統計情報にまつわる頻出FAQ〜概要、確認、収集・取得 (オラクルエンジニア通信 - 技術資料、マニュアル、セミナー) はてなブックマーク - Oracleの統計情報にまつわる頻出FAQ〜概要、確認、収集・取得 (オラクルエンジニア通信 - 技術資料、マニュアル、セミナー)

統計情報と実データの齟齬

短時間に大量のデータを更新すると、Oracleが内部的に保持している統計情報と実際に格納されているデータが食い違うというか、統計情報と実データに齟齬が発生します。
set autotraceコマンドを使い、クエリの実行計画を確認したところ、単純な主キーによる検索も、テーブルフルスキャンが実施されていました。統計情報から導き出された実行計画が実データの状態に合っていなかったのだと思います。

日中の大幅なデータの更新に対応するために、手動統計収集が有効とOracleのページに記載されています。

統計情報には、以下のような収集方法があります。

自動統計収集:Oracle Database が自動で定期的に統計情報を取得するため、取り忘れがない。更新が行われた表を特定し、その表の統計情報のみ再取得する
手動統計収集:日中の大幅なデータの更新に対応するため際に有効。実行計画への影響を把握できる
動的サンプリング:SQLをハードパースした際、統計情報が存在しない場合に統計情報の取得する。ハードパース時の負荷や統計情報の質を考慮して使用する必要がある
Oracleの統計情報にまつわる頻出FAQ〜概要、確認、収集・取得 (オラクルエンジニア通信 - 技術資料、マニュアル、セミナー) はてなブックマーク - Oracleの統計情報にまつわる頻出FAQ〜概要、確認、収集・取得 (オラクルエンジニア通信 - 技術資料、マニュアル、セミナー)

まとめ

PL/SQLなどで短い期間にテーブル内の大量のデータを更新する場合は、更新後に以下のコマンドでオプティマイザ統計情報の手動収集を実施したほうが良いです。

exec DBMS_STATS.GATHER_SCHEMA_STATS ('ユーザ名', estimate_percent=>dbms_stats.auto_sample_size);