Tips13. OracleのSQLがある日突然遅くなる
SQLがある日突然遅くなる
Oracleを扱っていれば誰もが通る道です。
何故そのようなことが起こってしまうのか。
理由は一つ、「実行計画が最適なものではなくなってしまうから」です。
しかし、その対処は決して簡単ではありません。
「Oracleはこれだから困る！」と悪態付く人もいますが、これはOracleのせいではなく、RDBの宿命です。
データベースの運用を行っていくと（開発中であっても）、当然ながらデータは増加していきます。
データ量がある境界を越えると、以前の実行計画では最適なパフォーマンスを発揮することができなくなります。
もう少し細かい話をしますと。
実行計画はCBO(コスト・ベース・オプティマイザ)が、日々の統計情報（データベース中のテーブルのデータ量やデータの分布等）に基づいて決めますので、データの増加による実行計画は、統計情報が正しければ自ずと最適化されます。
しかし、統計情報の取得には時間やサーバリソースを消費しますし、常に100％最新の状態を維持することは現実的ではありません。
そうすると、統計情報に基づいた実行計画と、現実のデータに差異が発生し、最適な実行計画にならないケースが発生します。
そうなってしまった場合に、パフォーマンス劣化が発生する可能性が高くなります。
実行計画を比較する
可能であれば、遅くなる前後の実行計画を比較するのが確実です。
その場合、statspack か AWR(Automatic Workload Repository) が導入済みで、定期的にスナップショットが取得されていることが前提となります。
※AWRの使用には、Oracle Enterprise Edition + Oracle Diagnostics Pack のライセンスが必要です
statspackの使用方法については、下記を参考にしてください。
https://oracle.tf17.net/plsql/?page_id=790
https://docs.oracle.com/cd/F25597_01/document/products/wli/docs102/dbtuning/statsApdx.html
取得した実行計画を比較し、その内容が異なっていれば、CBOにより自動的に実行計画が変わってしまったものと思われます。
この時点で、問題となったSQLや実行計画を確認し、INDEXの過不足など原因が推定できるのでしたら、そちらの対処を行いましょう。
すぐに原因が判明しない場合は、次のように、統計情報の取得状況を確認しましょう。
統計情報が最新かどうか確認
まず統計情報が最新かどうかを確認しましょう。
SQL文中に登場するテーブルに対して、user_tables / all_tables データディクショナリ表の last_analyzed 列を確認します。
（パーティションの場合は、all_tab_partitions 、インデックスの場合は all_indexes です）
SELECT table_name, last_analyzed FROM user_tables WHERE table_name = 'テーブル名';
で確認できます（データディクショナリの参照権限が必要なことに注意してください）。
last_analyzed の日時以降に大幅なデータの変更があるようでしたら、統計情報が最新になっていない可能性があります。
統計情報を最新化する
統計情報の取得は、通常通りにデータベースをインストールしたままであれば平日22時に取得されます。
それ以降にデータ分布の変更があった場合は、統計情報が最新になっていない可能性があります。
手動で統計情報を取得するには、下記のコマンドを発行します。
dbms_stats.gather_table_stats(
ownname => 'スキーマ名',
tabname => 'テーブル名',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
degree => 1,
cascade => TRUE
);
再度、last_analyzed列を確認して、統計情報が最新化されていることを確認しましょう。
それでも改善しない場合は、いよいよ本格的に実行計画を精査する必要があります。
