OracleのSQLのチューニングに使うツール

Oracleでは性能に関連するデータが色々と取れるため、性能問題の解決がかなり容易になっています。OracleSQLのチューニングによく使うツールと使い方のポイントをまとめてみました。

性能状況の統計的分析 (Statspack)

問題箇所の全体に占める割合によって性能改善の効果が決まるため、測定されたデータから統計的に性能比率を分析し、チューニングすべき箇所を見つけるのは、SQLレベルでのチューニングではなくてもよくやることだと思います。このために使うのはStatspackです。

ある期間における統計をとれるので、全体の中から問題になるSQLを発見することができます。Level7でStatspackを取得すれば大体チューニングに必要な情報が取れます。Standard EditionでもEnterprise Editionでも利用できるため、よく使われているツールです。

では早速、Statspackをセクション別に簡単にポイントを説明していきます。

  • Load Profile
    • Hard Parseが多発してないか、Physical Readが多発してIOPS不足になってないか、生成されるREDOサイズなど基礎的な状況の確認などをします
  • Instance Efficiency
    • バッファキャッシュのヒット率が低くないか、Hard Parse回数が多くないかなど
    • Load ProfileやInstance Efficencyのセクションで問題になるケースは、殆どのケースでDBA不在といっても過言ではないです。
    • 大概がサーバーパラメータファイルの設定で解決できるので、このレベルでは少し知っている人がみれば解決できることが殆どで、通常はあまり問題にはなりません
  • Top 5 Timed Events
    • どの待機イベントがTopか
    • CPU Timeが一番上に来ているか(CPUを使えているか)、上位の待機イベントが何か(何で待ってしまっているか?)などを見ます
    • CPU Timeが長過ぎたり、待機イベントが長過ぎたりするという場合は問題になります。待機イベントの合計時間である程度の問題の推測ができます。CPU Timeについては、SQL orderedのセクションをみないと詳細はわからないので、CPU Timeが長過ぎる場合にはSQL orderedを見て問題を見つけていきます
  • SQL ordered(SQL ordered by CPU Time, Elapsed Time, Executionsなど)
    • CPU時間がかかりすぎているもの, Elapsed Timeが長すぎるもの(待機イベントが殆ど)、Executionsが多すぎるもの(実行回数が多いもの)はここを見ればわかります。全体の中でどれだけのCPU Timeを占めているかなどの割合がわかるので、どこを改善すればどの程度性能向上の余地があるのかがわかるという点で、何をチューニングする必要があるのかがわかるので、とても貴重です。
    • CPU TimeやElapsed Timeが長い場合
      • CPU実行時間やElapsed Timeが長いSQLについては、Old Hash Valueの値を元にStatspack SQLレポートでSQLの実行計画を確認します。実行計画に問題がある場合には、SQLだけでなく統計情報の確認もします。
    • Executionsで実行回数が多い場合
      • 1回あたりのCPU Timeは短いけれど、実行回数が多く性能が劣化しているケースはよくあります。良くあるのはN+1問題などで、無駄なSQLの発行回数が増えてしまっていることはあり、そのような問題を見つけやすいとはいえます。
  • Segments
    • どの表、どの索引の負荷が問題になっているかがわかります。

このように、Statspackによる統計データは、問題の全体像、問題箇所、問題の割合を明確にできるという点でとても貴重なレポートです。しかし、Statspackで問題の大体の推測は出来ても、ある一時点での問題や複数の待機イベントの時間が突出して問題が混在している場合などには、Statspackでは完全に問題の原因を突き止めるのは難しいケースがあります。

ある時点での状況分析 (EM or ASH Viewer)

Statspackは統計データによる分析ですが、問題は統計だけではわからないというのはよくあります。今その瞬間でどのSQLがどの程度CPU Timeを使っていて、どの程度の待機イベントが発生しているのかを見なければ問題を特定できないことがあります。例えば、Statspackで待機イベントの待ち時間が長いものが複数存在している場合に、どのSQLがいつどの程度の割合で待機イベントで待っているかを判断しようとしてもわかりません。全体の中での統計とある一時点の状況は異なるからです。

このようなときに使うのが Enterprise Manager(or ASH Viewer) です。

Oracleがとても素晴らしいのは、どのSQLの実行時にどの待機イベントでどれだけ待っていたかというのを、Enterprise Managerを使うと時系列で見ることができます。Statspackのような期間での統計情報ではなく、まさにその遅延している状況で、特定のSQLについて待機イベントを把握することができるというのがポイントです。

発生する待機イベントの問題をどう解決するかはOracleの内部のアーキテクチャに密接に関連するため、チューニング方法は待機イベントによって異なります。しかし、Oracleアーキテクチャをよく理解していれば、何が問題で待機イベントが発生しているかは想像がつきやすいものでもあります。

OSの性能データの分析(iostat, vmstat, mpstat, sar)

Statspackである期間の統計をとっても、ASHでみても分からないのは、OSの性能データです。

Oracleのようなミドルではなく、Hardwareのレベルでボトルネックになってしまっている場合には、ミドルレベルでいくら改善しようとしてもHardewareの性能限界からそれ以上の性能を出すことはできません。
OracleというMiddlewareの問題なのか、Hardeareの問題なのかの切り分けは、このレイヤで判断する必要があります。まずMiddleの性能を使い切れるようなHardwareを選定できているかを検証することは重要です。
Hardewareの基礎性能を測るツールについては先日のエントリに書いたので参考にしてみてください。

ディスクのIOPSレベルで問題が出ているために、待機イベントが発生しているのか、CPUリソースの不足で待機イベントが発生してしまっているのかなどは、OS側の基礎情報もつきあわせないと分からないことは、高負荷環境ではよくあるのではないかと思います。OSレベルで問題がない場合は、ASHを中心に特定のSQLでの待機イベントを中心にして、ボトルネックの推定をOracleアーキテクチャを考量した上でしていくことになります。

基本的に、OSの性能データは、iostat, vmstat, mpstat, sarで見ます。これを時系列で記録し、EMなどと突き合わせるとどのような状況で問題がおきているかはわかりやすくなります。

ストレージのI/O周りでよくあるのは、Random Read/Write周りが限界にくることです。これはディスクの仕組みからくる物で、Random WriteだとSSDにするか、BBWCを使うか、Random ReadだとSSDにするかなど、はたまたディスク本数や回転数を上げるといったことでIOPSをあげ、Hardwareレベルでの限界値をあげておくというのはよくやるのではないかと思います。このIOPSを解決すると、CPU周りで詰まることが多い訳ですが、ここは色んなケースで詰まるのでCase by Caseです。このような場合、SQL発行回数の削減や非正規化などは、比較的よく使われるテクニックではないかと思います。

まとめ

全体の統計データと時系列データの2点を組みあせて、全体の中からどこを解決しなければいけないかを見つけた上で、問題の全体像が見えたら、問題を深追いしてボトルネックの原因を見つけるという手順になります。

それを実現するためには、OracleではStatspack + ASH + OSの基礎情報(iostat, vmstat, mpsta)を使って性能分析をします。これらのツールで詳細に性能に関する詳細データが統計的にも時系列的にも取ることが可能なため、ブラックボックスであってもある程度内部の動作の推測はできるので、ボトルネックの問題分析が容易になっています。

# 最近はMySQLもPerformance Schemaという近い物がでてきたので、もう少し時間が立つとOracleと同じような形になっていくのではないかと思います。今後のOracleの動向に注目したいところです。