OracleでSQLのParse回数を軽減するための方法

OracleではSQLの実行手順は、次の3フェーズから構成されます。

• Parse
• Execute
• Fetch

その中でParse処理は、次のような手順で行われます。

• セッションにキャッシュされたカーソルが存在しない場合
  • カーソルをオープン
    • 共有プール上に共有カーソルがある場合
      • soft parse
    • 共有プール上に共有カーソルが無い場合
      • Hard Parse
• セッションにキャッシュされたカーソルが存在する場合
  • そのカーソルを利用

特にCPU負荷の高いHard Parseの処理では

• SQLの文法的チェック
• SQLの意味的チェック
• 実行計画生成
• 解析済みSQLの共有プールへのキャッシュ

という処理をします。処理を見ればわかるように、この処理が毎回走ってしまってはとてもCPUコストの高い処理になってしまいます。

そのため、共有プールに既に解析済みSQL情報があれば、Hard ParseはパスしてSoft Parseをし、共有プールから解析済みSQL情報を取得します。セッションにキャッシュされたカーソルがあれば、その部分についてはSoft Parseもパスできます。

従って、Parse回数の削減のためには、共有プールサイズに解析結果を乗せてHard Parseが発生しないようにし、Soft Parseになるようにするようにすればいいわけです。それに加えて、Session Cursor CacheでSoft ParseもさせないSofter Soft Parseをするようにすることで共有プールのチェックもしないようにすれば、よりCPUには優しくなります。

Parseについては、OracleとAPの話と両方あるのでまとめると、SQLのパース処理は次のように削減することができます。

• Oracle
  • ハードパースの削減
    • 共有プールにキャッシュ
  • ソフトパースの削減
    • Session Cursor Cacheの利用
• AP
  • Prepared Statemenc CacheでRDBMS側のPrepare作業をさせないようにすることでParse回数を削減する

このような方法で、SQLのParse回数を削減することで、CPU負荷は確実に削減できます。ここでのパース回数の削減結果は、StatspackのInstance Efficiency Sectionで確認できます。I/O性能に余裕があり高負荷の場合には、CPU側がネックになるということもあり、少しでもOracleのCPU Timeを削減したいということがあります。このチューニングは容易に出来るものでもあるので、あらかじめやっておくのがいいでしょう。

ここからは余談ですが、この話を書いていた時に、DeNAのHandler Socketでは、このSQLのパース処理がコスト高ということで、パース処理をショートカットすることでQPSを高めるということをやっているという話を思い出しました。Prepated Statementなどでキャッシュできる場合を考慮すると、Handler Socketでパース処理コストを削減と書いているのは、正確には上記で書いたパース処理の範囲より広範囲なものをさしていると推測されますが、SQL関連処理をばっさり切ってしまっていることでCPU Timeの削減を図るというのは面白いなと思いました。KVS的に使う場合には、SQLをほぼ捨てることで、このように過激に性能改善をする方法もあるのは面白いと思ったのと、ここらあたりの仕組みを外からプラガブルに組み込めるmysqlは非常に素晴らしいなと思ったのでした。

# 追記: Handler Socketの存在意義について
なんで、そんな何回もHard Parseする必要があるのだろうと思ってたんですが、sh2さんのコメントをみてわかりました。「MySQLには共有プールなんてないしPrepared Statementをキャッシュしても実行計画は都度生成する、など考えるとHandlerSocketの存在意義がよく分かる」と。

Persistent Connection + Prepared Statementを使いにくいという事情があるのはConsumer系ではよくある話なので、それで難しい部分はあるのかなとは思ってました。ただ、共有プールもないとすると、そもそもMySQLでは毎回Hard Parseするしかないということなんだなあと。これはちょっといけてないですね。MySQLも、ここらはまだこれからって感じなのかな。