もうそろそろOracle Databaseのパフォチューもしなくて良い世の中になって欲しいものですが、、、
よくある問題
実行計画が変わっているわけでもないのに性能劣化が発生するよくあるケースとして、Table Full Scanでのデータ取得がbuffer cache readからdirect path readへの変更があります。
オプティマイザとしてはデータが大きくなったっぽいから良かれと思ってbufferに載せない判断をしているんですが、これが発生するケースって、そもそもFull Scanであるまじきテーブルを、開発中はデータ少なくてそこそこ動くからってデータ肥大化を考えずに放置していたということがほとんどなんですよね。
なので、結構データが大きくなってから気づいて、かつ急激な性能劣化をもたらし、そこそこの障害になるという。
前段が長くなりましたが、このbuffer cache readからdirect path readに切り替わる閾値は、デフォルトではbuffer cache領域の2%となっています。
buffer cacheが1Gであれば20M、64Gあれば1.28Gという計算です。
要するに大きければ大きいほど発生時の被害が大きくなる可能性が高いということですね。
発生時の対処方法
この閾値は隠しパラメータで変更することができます。が、この対応はあまりオススメしません。
理由はデータベース全体の設定になってしまうので、閾値の判断が難しいから。
個別テーブルの対処であれば、Keep Bufferにでも避けてしまうのが無難でしょう。
ただ、最も本質的には、以下であるべきと考えます。
- Full ScanとすべきでないテーブルはIndexを適切に設けること。
- Full Scanを狙っているテーブルに対してはインメモリパラレルクエリとして動作させること。
インメモリパラレルクエリの場合は、buffer cacheの80%を越えなければ、direct path readとなりません。