月別アーカイブ: 2015年1月

「オプティマイザ統計の保留」の検証(序章②)

自動化の落とし穴

グローバル社会は24時間眠らない。

「9時から5時まで働いて、土日は完全休日。」正月からスーパーが開いている現代ではそんな古き良き時代の働き方は皆無と言えるかもしれません。

私の場合、以前某官公庁で大型汎用計算機の運用に携わっていた頃は、朝7時に当番が早めに出勤してコンピュータを立ち上げ、夕方18時くらいには電源を落として帰るような運用をしていました。(当然土日は休みです。)

しかし、DBAとして本格的に働き始めた2000年ごろから、24時間止められない運用とその運用を支える仕組みを直に担当するようになりました。

24×365(24時間365日)運用というものは携わった者でなければ理解できないシビアな世界です。ちょうどこの頃はインターネットをビジネスで使うということが当たり前のようになっていった時期であり、世界規模(グローバル)にビジネスを展開することが多くの企業で求められ始めた頃でもありました。

私はスペシャリストとしてオンコールで対応する立場でしたが、運用エンジニアは3交代シフトで24時間365日運用を行うような体制でした。

この会社は主に日本国内の企業を対象にしていましたが、それでも24時間システムを維持管理する必要があります。

特にグローバルに事業を展開している企業はより高いレベルでサービスを提供するため、例えばOracle社のサポートは当時日本、米国、英国に拠点を持ち、サポート案件を拠点間でハンドリングすることで、シームレスで高度なサポートを提供しているというようなことを聞きました。

その他にも外国為替市場のように、業界全体がグローバルに広がっている場合の運用もシビアです。

あるFX会社におけるパフォーマンス問題

Oracle10gは2004年にR1がリリースされましたが、以下は比較的早い時期にOracle9iから10gR1にアップグレードした、とあるFX(外国為替証拠金取引)会社で実際に起きた事例を若干の脚色を加えて再現したものです。

FXにおける繁忙時間帯は夜の21時です。なぜなら世界の主要取引市場である東京、ロンドン、ニューヨーク市場はそれぞれ朝7時に開かれます。ニューヨーク市場は元々の規模が大きことや、東京・ロンドン市場の取引傾向の影響を受けるために、日本国内のFX個人投資家にとっても14時間の時差があるニューヨーク市場の動向は非常に気になるのです。仕事から帰ってきたサラリーマンが自宅のパソコンで取引を始める時間帯ということもあり、21時からの数時間は日本のFX業者にとって最もクリティカルな時間帯なのです。

問題は21時にニューヨーク市場が開いて、トランザクション数が次第に伸びてきた22時過ぎに起きました。

さっきまで何の問題もなかったOLTPの取引処理が全く無応答になってしまったのです。

秒単位以下で変動する為替レートに対しリアルタイムに処理が完了しなければならないのに、この状況は致命的でした。

注文や約定と呼ばれる処理が遅れてしまっているため、焦った個人投資家がさらに取引処理を行おうとしたのかもしれません。滞留したトランザクションが膨大になり、状況はさらに悪化していきました。

このようなタイミングでアプリケーションの変更を行うはずがありません。性能劣化の原因が不明のまま時間だけが過ぎていきます。

ついに運用責任者がデータベースの強制再起動を決断しました。

未処理のトランザクションはすべてロールバックされ、せっかくの注文処理が無効になってしまいました。
そればかりか、レートが大きく変動したことによる損失を少しでも小さくするための決済処理が無効になってしまったため、投資家によっては大きな損失が発生してしまったかもしれません。

ほとんどの投資家はネット上で取引を行っているため、深刻なシステム障害はネットの掲示板等で瞬時に広まります。業者にとって投資家に対する補償等だけでなく、悪い評判が拡散してしまうということも大変にダメージが大きなことなのです。

障害の真相

後日、障害の原因を特定することができたのですが、それは唖然とするものでした。

まず初めに障害の背景となった事情について触れておきます。

Oracle10gからルール・ベース・オプティマイザ(RBO)がサポートされなくなりました。RBOで開発していた頃は、FROM句の後に記述するテーブルの順序を入れ替えただけで実行計画がガラッと変わり、パフォーマンスが大きく異なることがよくありました。

具体的には、レコード件数の多い順に記述するような原則で

FROM TBL-A(件数大),TBL-B(件数中),TBL-C(件数小)

のような感じで記述します。(この最適な順番を見つけるのが開発者の腕の見せどころでもありました。)
従って、テーブル件数が大きく変動するような場合は、なかなか開発者の意図した結果とならず苦労したという経験は、ベテランのOracle開発者であれば誰でも1つや2つ持っているのではないでしょうか?

ところが、Oracle10g 以降でコスト・ベース・オプティマイザ(CBO)のみになってから、世界は大きく変わりました。データ件数が変動しても統計情報を正しく取得していれば、最適な実行計画を必要に応じて算出してくれる、という理想のオプティマイザがCBOなのです。

前回のルート検索の例で言うと、渋滞情報をリアルタイムに収集して場合に応じた経路を示してくれるという機能がCBOに求められているのです。
つまり、地図上の最短距離だけでルートを示してくれるのがRBOで、渋滞情報を加味したルートを示すのがCBOなのです。

また、カーナビの渋滞情報が変わることに対するルート計算は比較的柔軟に再実行できるような気がしますが、実行計画算出(解析)は負荷の高い処理故、Oracleでは解析済みSQL文を共有することでその負荷がなるべく小さくなるような仕組みになっています。
つまり、渋滞がされた後でもずっと迂回路を案内してしまうような難しさをCBOは併せ持っているのです。

しかも、「正しく統計情報が取得されている」状態でなければ最適な実行計画は算出されないのです。Oracle社はこれに対してテーブル・インデックスの統計情報をデフォルトで自動的に収集する機能を実装しました。

収集の対象となるテーブル・インデックスを特定する条件はありますが、自動統計情報収集処理は10gの場合毎日22時に起動されています。

ほとんどの企業にとって夜の22時はオンライン業務が終了し、バッチ処理も終わっている頃、というのが自動統計情報収集の仕組みを作った開発者の頭の中にあったかどうかはわかりませんが、デフォルト設定がそのようになっているというのは事実です。

日本のFX業者にとってこの仕様が最悪の結果をもたらす可能性があるということは残念ながらあまり知られていませんでした。

詳細は関知していないのですが、このパフォーマンス問題が発生した経緯や背景事情を以下の通り想像を交えてまとめてみます。

  1. 繁忙時間帯が夜21時過ぎから日付が変わるあたりまでということは前述の通りですが、ニューヨーク市場がクローズされてから東京市場がオープンされる翌朝7時までのごく短い間にバッチ処理は終了するようになっています。
  2. 通常の問題のない日においては22時に自動統計情報収集処理が起動されていましたが、実行計画に影響を与えるようなデータの変動がバッチ処理で行われなかったため、統計情報が大きく変更され実行計画が突然入れ替わってしまうようなことはありませんでした。
  3. 問題の起きた日は、朝のバッチ処理でデータの大きな変動がありました。
  4. 21時の時点では前日の統計情報により良好な実行計画でOLTPが動いていました。
  5. 22時に通常と同じように自動統計情報収集処理が起動され、結果として良好な実行計画が変更されてしまいました。
  6. 実行計画算出処理(SQL文解析処理)は元々CPUリソースを消費するものでもあるので、それがOLTPに対して少なからず影響を与えていたというのも事実です。

再発防止策は?

障害の大きな原因として考えられるのは以下の2つです。

  • 22時という極めてクリティカルな時間帯であるにも関わらず、自動統計情報収集処理がデフォルトのまま起動されてしまった。
  • 実行計画に影響を与えるようなデータの変更があり、実行計画が変更されてしまった。

このケースでは「実行計画が意図せず変更されてしまった。」という問題に対する対策としては、

  1. 自動統計情報収集処理を業務影響の少ない時間帯に変更する。
  2. 統計情報収集処理を必要最小限の実行にとどめ、毎日は実施しない。
  3. 実行計画に影響を与えることが予めわかっているテーブルは統計情報が変更されないようにロックする。

のような項目を実施したと聞いています。

変動するデータに応じて最適な実行計画を立案する仕組みが、Oracle10g以降の「自動化」機能で実現されたわけですが、自動化というのは決して万能ではありません。

上の対策はある意味せっかくの自動化を意図的に殺すことで、(最適ではないかもしれないけれど)安定した運用を実現するものです。

私は「自動化の落とし穴」というテーマで、今までこの事例をいろいろな機会において紹介してきたのですが、反面自動化を選択するか否かの二者択一という考え方で本当に良いのかということをずっと考えてきました。

つまり、自動化と非自動化の中間に「半自動化」という概念があっても良いのではないかというのがその結論であり、次回紹介する「オプティマイザ統計の保留」というあまり注目されていない機能の特徴でもあります。

次回へ続く

「オプティマイザ統計の保留」の検証(序章①)

オプティマイザの正体

「HOW型」「WHAT型」コンピュータとは?

コンピュータを大きく分類すると「HOW型」と「WHAT型」という2つのタイプに分けることができるという説があります。

これは私が勝手に言っていることではなく1980年代にTRONを提唱した東大・坂村健教授の言葉です。(新版 TRONで変わるコンピュータ P.44〜、TRONプロジェクト Wikipedia)

「HOW型」というのは、現在使われているコンピュータつまりノイマン型コンピュータと呼ばれるコンピュータのことで、コンピュータがどのように振る舞うかを人間がプログラムという形で指示するものです。コンピュータが行う複雑な処理の一つ一つを厳密に定義しなければならないためプログラムを作るのは大変ですが、いったんプログラムができてしまえばコンピュータはそれを忠実に実行するだけというものです。

コンピュータ制御された車を考えると、「次の角を左に曲がれ。」とか「国道1号線を東京方面に進め。」等の指示を次々に与えながら目的地に誘導していくのが「HOW(どうやってやる)型」です。

一方「WHAT型」というのは1980年代当時研究されていた第五世代コンピュータとか、人工知能専用コンピュータに該当するもので、コンピュータ・カーの例で言うと「道路の左側を車線に沿って走れ。」とか「赤信号では止まること。」のように基本的なルール(専門的には知識ベース)だけを先に教えておいて、「何処何処へ行け!」という指示だけで目的地に向かわせるのが「WHAT(何をする)型」です。

第五世代コンピュータは当時最高の頭脳を結集させたプロジェクトだったようですが、結局は成果を出すことができずに終了してしまいました。まだコンピュータのパワーが非力だったということが最大の原因だったと思いますが、とりあえずムチャクチャに動いて(プログラムなしでコンピュータを動かすのは大変な事)最終的に結果を出せば良いというアプローチにやはり無理があったようです。

オプティマイザは「HOW型」でもあり「WHAT型」でもある

オプティマイザはリレーショナル・データベースの中で最も重要な機能と言っても過言ではありません。正しい結果をより早く返すためのアプローチを最適化する(optimize)機能・プログラムがオプティマイザ(optimizer)です。

それは当然「HOW型」コンピュータの上で動くプログラムですが、「WHAT型」しての性格も色濃く持っています。それはSQL(Structured Query Language)がまさに「(求める)結果=WHAT」の構造を記述するものだからです。

これはカーナビで経路検索をすることに似ています。例えば「日本橋から横浜ランドマークタワー」まで車で行きたい場合、Google Mapで検索すると

  1. 首都高速1号羽田線 と 首都高速神奈川1号横羽線 経由:35.1km、46分(31分)
  2. 首都高速3号渋谷線 と 第三京浜道路 経由:41.8km、49分(41分)
  3. 第二京浜/国道1号線 経由:35.2km、1時間8分(50分)

という結果が返ってきました。

GoogleMapの例(イメージと記事の内容は異なります。)

この2点間を結ぶ道は無数にあります(遠回りして新宿駅経由のルートでも目的地には着くことができます)が

  1. どんなにお金がかかっても最短時間で着くことができるルート
  2. 有料道路でも若干安いルート
  3. 有料道路を使わないルート

というような基準でそれぞれのルートを算出し(文字通り計算で求め)たのが上の結果です。ちなみにこれらは渋滞情報も加味されていて(カッコ)内は渋滞なしの場合の所要時間です。(これは実に興味深いことなので次回で取り上げます。)

カーナビで検索されたルートに該当するのが「アクセスパス」です。オプティマイザが最終的に1つに決定したアクセスパスに従って、実際のデータにアクセスされ、加工され、結果が返されます。

言い換えると、オプティマイザの役割は最適なアクセスパスを算出するところまでで、実際の物理的なI/Oやメモリ間操作などはオプティマイザの関知するところではありません。運転前にルートを検索することと実際にそのルートに従って車を運転することが違うことに相当します。

オプティマイザの計算量は膨大

オプティマイザは前述のように、決められたアルゴリズムに従って最適解を得ると言った面では「HOW型」と言えますが、ユーザの求める結果を実現するための「アクセスパス」を最終的に1つに決定するということでは「WHAT型」です。

2点間を結ぶルート検索であれば選択すべき経路はそれほど多くないのですが、複数のテーブルから求める結果を得るということは想像以上に大変なことです。

A、Bという2つのテーブルを結合して結果を得る場合、最初にAテーブルにアクセスしてその結果を基にBテーブルにアクセスすることを「A→B」と表現すると、A→BとB→Aという2通りのアクセスパスが存在します。

さらにA、B、Cの3つのテーブルでは、A→B、A→C、B→A、B→C、C→A、C→Bの6通りになります。(結合は原則的に2つのテーブルあるいは結果セット同士になります。)

テーブル数が増えるごとにアクセスパスは多くなり、簡単に説明すると(テーブル数)!:テーブル数の階乗となります。つまり10個のテーブルを結合するSQL文の場合は実に 3,628,800通りとなってしまいます。この中から最適なものを1つだけ選択しなければならないのでオプティマイザの計算量は膨大なものとなってしまいます。

実は、Oracleのオプティマイザは300万通り以上の組み合わせを律儀に評価するようなことはしません。「OPTIMIZER_MAX_PERMUTATIONS」というOracle8から導入された初期化パラメータによって評価する組み合わせの上限値が決められています。(これは解析時間を短縮するための苦肉の策と思われます。)

Oracle8と8iではこの値は「80,000」でしたが、9i以降では「2,000」となり、さらに10g以降では隠しパラメータ「_optimizer_max_permutations」となったため基本的に変更しないパラメータとなってしまいました。

SQL> select
  2   a.ksppinm  "Parameter"
  3  ,b.ksppstvl "Value"
  4  from
  5   x$ksppi  a
  6  ,x$ksppcv b
  7  where a.indx    = b.indx
  8  and   a.ksppinm like '%optimizer_max_permutations%';

Parameter                      Value
------------------------------ ----------
_optimizer_max_permutations    2000

7個以上のテーブルを結合するとアクセスパス算出が不十分になる?

「OPTIMIZER_MAX_PERMUTATIONS(または _OPTIMIZER_MAX_PERMUTATIONS)」パラメータが2,000であることの意味を考えてみましょう。

前述のとおり複数テーブルを結合する組み合わせの数は「(テーブル数)!」となります。テーブル数6の場合6!=720、7の場合7!=5,040であるので、7つ以上のテーブルを結合する場合、すべての組み合わせを評価して真に最適なアクセスパスを算出する前に、オプティマイザが評価を諦めてしまう可能性があります。

以前、ある企業のコンサルティングを行った際「テーブルの結合は5つまでとする。」というルールを定めているのを目にしたことがあります。
これは恐らく本パラメータを意識したルールでなかなか興味深い考え方だなと記憶しているのですが、原則的には間違った発想だと思います。

一般的に5つのテーブルを結合するような複雑なクエリーを書くことは珍しいかもしれませんが

.....
from
 emp  e1
,emp  e2
,emp  e3
,dept d
.....

のように、FROM句の後に同じテーブルを複数記述するようなことは簡単にできてしまうので、5つという制限は意味のない足かせになるかもしれませんし、そもそも結合を減らすためにせっかく正規化したテーブルを非正規化するようなことは本末転倒です。

それではもし、正規化された7つ以上のテーブルをどうしても結合しなければならない場合はどうしたらよいでしょうか?

  • LEADINGヒントやORDEREDヒントにより、FROM句の後に記述された順にテーブルが結合されるようオプティマイザに情報を与える。
    • デメリット:テーブル順を間違えると悪い結果をもたらす。統計情報が変動した場合どうする?
  • PL/SQLでカーソルを定義し(例えば4テーブルのSELECT文)、カーソル・ループの中で残りのテーブルを参照する。
    • デメリット:想像のとおりプログラムが複雑になり、手間の割には成果が少ないかもしれない。
  • 一時的に_OPTIMIZER_MAX_PERMUTATIONSパラメータの値を変更する。
    • デメリット:解析済みSQLがキャッシュアウトされる度にパラメータが変更できるか?実行計画を固定化する高度なスキルが必要。

いろいろ考えられるのですが、一長一短ありでなかなか単純ではありません。(あえて言えば3番目が一番スマートでしょう。)

次回へ

「オプティマイザ統計の保留」というあまり注目されていない機能を取り上げ(検証し)ようとしているのですが、オプティマイザについて語ると脱線してしまってなかなかたどり着けません。

次回「序章②」として、オプティマイザを理解する上で前提として押さえておきたいことを説明し、次々回で検証に入りたいと思います。