門外不出のOracle現場ワザ

第3章　データベース管理 転ばぬ先の杖～設計編

日本オラクル株式会社 コンサルティング統括本部テクノロジーコンサルティング本部 小田 圭二(おだ けいじ)

目次

• Part1　 業務アプリケーション開発チーム編
  • トラブルは業務要件のヒアリング時から始まる
  • ER設計におけるトラブル回避のポイント
  • CRUD図でデータのライフサイクルを確認
  • アプリケーション側で実装すべきこと
  • 事前に調べておくべきロック関連の問題
  • デッドロックを防止する実装
  • そのほかの検討事項
• Part2　 インフラ設計チーム編

Part1　業務アプリケーション開発チーム編

コンサルティングをしていると、業務アプリケーションの設計時点でDBのことをきちんと考えているプロジェクトは少ないと感じます。特に業務アプリケーションにかかわるチームの方々は、RDBMSの内部構造や特性を意識せず、性能面での問題を引き起こすような設計をすることがあります。「業務アプリケーションの設計時点ではDBの内部について意識しない設計をすべき」という方針も正しいのですが、あなたがもし、そうしたチームに属しているのであれば、ぜひ本パートの内容を実務に生かしてみてください。トラブルは後の工程で解決するのが難しいので、物理設計や運用設計を担当するインフラ設計チームの方も、設計時にこの内容を基にして業務チームとディスカッションしてみることをお勧めします。

トラブルは業務要件のヒアリング時から始まる

一流のDBコンサルタントであっても、要件を変更してもらわない限りDBトラブルを解決できないことがあります。その意味では、DBトラブルはユーザーへの要件ヒアリングから始まると言えるでしょう。具体的なイメージを持ってもらうために、筆者がほぼ毎プロジェクトで遭遇する、業務要件に由来する典型的なトラブルを紹介します。

トラブルの例（自由検索）

それは「ある画面で自由検索をし、その結果を何らかの順序でソートして上位数十件を表示したい」という要件です。この要件を満たすSQL文は図1のようになります。

図1 上位数十件を表示するSQL文の例

このSQL文は、一見するとROWNUM（一定件数以上のデータを切り捨てる際に使用する条件句）で数十件しか処理されないように記述されているので、それほど重くなさそうです。Googleなどの検索エンジンがメジャーになり、エンドユーザーがこのような要件を希望することも増えてきました。しかし、皆さんにはこの要件を聞いたタイミングで次のようなことに気が付いていただきたいと思います。 まず、この要件を実現した場合に実行される処理を追いながら、その危険性を説明します。 はじめに、図2のように入力された条件に基づいて表からデータをフィルタリングし、その結果をソートします。

図2 実際の内部処理

ソート後のデータに対して上位数十件を表示するわけです。たいてい、入力する条件はどんなものか、何件ヒットするか事前には分かりません。何十万、何百万件もヒットしていた場合、インデックスのある／なしに関係なく、数秒では画面表示までたどりつけません。

これを見過ごしてしまうと、システムテストやパフォーマンステストでデータ量が本番と同じくらいになったときに、「耐えられない遅さだ」とクレームを受けることになります。筆者はどれくらい重いのかを判断するために、次のような質問を用意してチェックしています。

• 表の件数はどれくらいか？

  図2の(1)が数百件までであれば、どんな検索条件でもたいていは大丈夫です。

• like検索はないか？

  like検索では、実際に入力される条件によっては絞り込みがほとんどできません。名前検索で「さ」や「た」といった一文字が入力された場合、「佐藤」さんや「田中」さんが全員ヒットしてしまうため、図1の(2)のデータ量が多くなってしまう恐れがあります。
• like検索は前方一致（先頭一致）のみか？中間一致や後方一致もないか？

  前方一致検索であればインデックスが有効に使われ、高速な検索となりますが、中間一致や後方一致では一般に高速な検索ができません。インデックスはデータ全体を順番に並べているため、文字列の中間や後方といった部分に関しては、効率良く検索できるようになっていないためです。これは図1の(1)のピックアップに時間がかかることを意味します。

• データに偏りはないか？

  例えば「東京」というデータが表の中の半分を占めているというような偏りがある場合、「東京」を指定した検索には時間がかかってしまいます。図1の(2)のデータ量が多くなってしまう恐れがあります。

• 検索条件は複数項目（例えば、氏名、住所、電話番号など）から選べるような要件ではないか？その項目の数はどれくらいか？

  検索を高速にするためにはインデックスを用いますが、表のカラム（列）が20あるとして、そのうち検索条件に18項目（カラム）が使えるとすると、図2の表Aには、インデックスが18個必要になります。このため、更新時の性能が悪くなることが予想できます。 先ほどの要件は、このような危険性を含むことが分かりました。この場合、本当に自由検索が必要なのでしょうか。見もしない、何十万件もヒットするような甘い検索条件を許容する必要があるのでしょうか。

上記のような質問をしながら、できるだけ早いタイミングで代替案をエンドユーザーに提示しましょう。代替案として、ヒットする件数が少なくなるような入力条件を必須とするよう、アプリケーション側で入力チェックをしてもらうといった方法が考えられます。上記の4の例で言えば「東京」だけではなく、市町村まで入力必須にするといった対策があるでしょう。

トラブル回避のポイント

DB側で可能な対策としては、検索処理に必要な項目をすべてインデックスに含めておくという方法があります。表に対して多数のアクセスが発生する通常の設計と比べ、検索条件とソートのキーと表示対象をインデックスに含めた設計の場合、Oracleはインデックスのみを使用して検索処理を高速に行ないます（ただし、検索条件をインデックスの先頭にする必要があります）。なお、このテクニックの多用は厳禁です。後述しますが、インデックスが多くなり過ぎるのは望ましい状態ではないからです。

この自由検索の例のほかにも、オンラインバッチ 注1など、トラブルを防ぐために気にすべき要件はいろいろとあります。このようなトラブルを防ぐポイントは、 Oracleの動作をアルゴリズムの観点から把握すること、もしくは実績のある方法を採用することです。

業務チームは、「RDBMSにSQL文を渡せば、自動的に最適な方法で処理をしてくれるだろう」と考えてしまいがちです。しかし、自分がRDBMSになった気持ちでSQL文を処理することをイメージし、RDBMSの内部ではどれくらいの処理が発生しそうかということまで考えてみてください。

注1：オンラインからキックされる（もしくは実行される）バッチ処理。「随時バッチ」と呼ばれるものもあります。これらはオンラインに影響を与えたり、タイミングがコントロールできないためDBサーバーのリソースを枯渇させることがあります。

ER設計におけるトラブル回避のポイント

正規化と非正規化

ノウハウ不足や経験不足によるトラブルが起こりやすいのが、ER設計とその後の各種設計です。よく言われる正規化／非正規化はもちろん、それ以外にも注意しなければならない点があります。

まずは正規化に関する注意点として、「オンラインのレスポンス時間は5秒」と決められている要件を例に、正規化で気をつけるべきことを考えてみましょう。結果を返すために複数の表から多量のデータを集計しなければならないDB設計では、カットオーバー直前になって性能で苦労することは確実です。そのため、オンラインの性能が重要なものに関しては、非正規化を考えなければいけません。

性能が出るかどうかは、アクセスパスを確認することにより比較的早期に判断できます（アクセスパスについては後述します）。対処法はさまざまで、「非正規化をする」「集計済みの表を作る」「マテリアライズドビュー」（ユーザーからはビューに見えるが、実体を持っているビュー。詳しくは後述）を作るといった方法があります。設計段階であれば、まず非正規化と集計済みの表を作ることを検討してください。

マテリアライズドビューの使用を検討する

非正規化の詳しい解説は割愛しますが、マテリアライズドビューの使用はトラブルが起きた後の対策としても有効なため、紹介しておきましょう。

マテリアライズドビューは、先ほども触れましたが「実体を持った（materialized）ビュー」という意味です。例えば、表Aと表Bを結合したマテリアライズドビューCを定義すると、マテリアライズドビューCには結合済みのデータが入ります（図3）。

図3 マテリアライズドビューとは

データの同期はOracleが行ないます。さらにOracleの「クエリーリライト（書き換え）」という機能を使うと、表Aと表Bを検索するSQL文で、自動的にマテリアライズドビューCを検索してくれます。また、このような表の結合だけではなく、データ集計にも使えます。

良いことづくめのようですが、実体があるのでディスク上の容量もとりますし、制限もあるので注意してください。なお、詳細はマニュアルを参照してください。

非正規化で考慮すべきこと

次に、非正規化について考えてみましょう。まず気をつけていただきたいのは、「非正規化＝正規化をしない」というのは間違いということです。具体的には、業務画面のイメージからデータ項目を引っ張ってきてエンティティの候補にし、正規化を検討せずにそのままエンティティにしてしまうケースがあります（図4）。これは非正規化ではありません。

図4 画面＝エンティティ？

図4のエンティティには問題点が多数あります。正規化／非正規化に限っても、社員属性（表の列に相当）と部署エンティティは分けるべきですし、支給総額は明細（ディテール）から導き出せるため、まず明細を作ることを考えるべきです。「支給総額を作る」という非正規化はその後で、支給総額も「昨年度」「本年度」で属性（表の列に相当）を作らずに履歴とするべきです。残念ながら「データベースはファイルとほぼ同じ」という考え方で設計する方が多く、このような設計はいまだに見られます。

なお、非正規化をした場合（正規化すらしていない場合も含めて）、データ更新の際には複数の表を操作し、データの整合性を自分で確保しなければなりません。

参照整合性制約

参照整合性制約を使用しているプロジェクトも多いと思いますが、外部キーにインデックスを付けることを検討してください（図5）。

図5 親表へのdeleteにおいて子表への全件検索が実行される例

インデックスを付けていないと、親表と子表の結合で全体検索が発生したり、親表へのDELETE文やUPDATE文で、子表に対しての全件検索が発生する可能性があるためです。ただし、親表の行を削除することと、親表の主キーを更新することの是非も充分に検討してください 注2。

注2：主キーは更新されるような定義にしないのが設計のセオリーです。

インデックス

次は間違った説が広く信じられているインデックスについて考えてみます。世の中にはER設計が悪くても、インデックスさえ貼っておけばすべてのSQL処理が速くなると思っている人が多くいます。「パフォーマンスが出ないから、インデックスを貼る」を繰り返していると、1つの表が数十ものインデックスを持ってしまうことがあります。当然、データ更新時にそれらのインデックスも更新しなければなりません。

ここで、インデックスのパフォーマンスを見積もる例を考えてみましょう。例えば、1つの表にインデックスが50個あり、1つのインデックスで1ブロックを読み込み、1ブロックの読み込みは20msecとすると、「20msec×50個＝1秒」かかってしまいます（図6）。

図6 インデックスにより遅くなるケース

インデックスに関しては、よく精査した上で重要なもののみに貼りましょう。すべてをインデックスに頼らず、ER設計をきちんと行なってください。

トリガーの多用

データに変更が加わる際に自動的に動作するプログラムである「トリガー」についても、簡単に触れておきます。

トリガーは便利なものではありますが、使い方を間違えるとトラブルが発生します。トリガーを使用すると、トランザクション間でデッドロックが起きることもありますし、多用すると性能も劣化します。また、何かの障害の折にトリガーが無効になる可能性もあるため、そのまま運用するとデータの整合性がとれなくなる事態も考えられます。これらを考慮の上、トリガーを採用するかしないかを判断してください。

ビューを正しく利用する

便利さの裏に、落とし穴を持っているのがビューです。開発者にとってビューは便利なもので、複雑な表定義を隠蔽したり、よく使う結合済みのデータ（擬似的な表）を提供するなどしてくれるため、多用しがちになります。例えば、ビューを使ってさらにビューを作ったり（ビューのネスト）、数多くの項目を持つ汎用的なビューを作ってしまうこともあります。

しかし、「表定義の隠蔽」が裏目に出ることもあります。本来はもっと効率の良いSQL文が書けるのに、ビューによって無駄なものまで隠されてしまうことがあるのです。例えば、ビューの中でソートをする場合、そのビューを使うSQL文は無条件にソートされてしまうといった問題が起きてしまいます。

このような特性や隠蔽のマイナス面も理解した上で、きちんと開発者向けのガイドライン（後述）を作り、正しくビューを利用するようにしてください。もちろん、ビューを使って情報を隠蔽することは、セキュリティ強化の面でメリットがあるので推奨できます。

CRUD図でデータのライフサイクルを確認

そのほかに、設計段階で行なっておくと良いのは、データのライフサイクルを確認することです。その際に用いるのがCRUD図（図7）で、これにより「生成→更新→削除」というデータのライフサイクルを確認できます。監査ログのようなものは仕方ありませんが、「なんとなく」とか「あると何かのときに便利そうだから」といった理由により、参照されないデータを入れているケースがよく見られます。このようなときにCRUD図を用いれば、不要な表を減らしたり、領域を肥大化させないようにできます。

図7 CRUD図の例

なお、CRUDとは、Create（生成）、Read（参照）、Update（更新）、Delete（削除）の頭文字を取ったもので、SQL文のINSERT（データロード含む）、SELECT、UPDATE、DELETE（TRUNCATE含む）に相当します。例えば、「INSERT処理があってデータのDELETEがない表は、運用開始後に徐々にデータサイズが大きくなり、領域トラブルやパフォーマンストラブルの原因となる恐れがある」などということを知るために利用します。そのほか、データ容量やアクセス頻度、バッチ処理などの時間帯を記入することにより、サイズの見積もりや処理の効率、業務同士がロック競合しそうかどうかといったことを判断するためにも使用できます。 さらに、INSERT処理やUPDATE処理がない場合には「誰がデータをメンテナンスするのだろう。SQL*Plusを使って手作業で行なうのだろうか？」と疑うこともできます。

アプリケーション側で実装すべきこと

ここまではDB側で気をつけるべきポイントについて説明しましたが、アプリケーション側でもDB関連のトラブルを防ぐためにやるべきことがあります。DBエンジニアであったとしても、以降の内容はぜひ押さえておいてください。

アプリケーションの設計／開発では、まず動くことが優先されるので、トラブル時の対策が軽視されてしまうプロジェクトが多いのが実態です。しかし、アプリケーション側でエラーハンドリングが適切になされ、各種ログの出力機能が備わっていれば、トラブル発生時の大きな助けとなるのは確かです。

レスポンスタイムの計測機能

まず、レスポンスタイムの計測機能をアプリケーション側で持っておくべきです。計測機能はオーバーヘッドともなるため、設定のONとOFF、そしてレベル（詳細さ）を指定できるようにしておきましょう（Tipsも参照）。アプリ側で持つレスポンスタイムの計測機能は、システム全体のパフォーマンス概要の把握に大いに役立ちます（図8）。

図8 システムのパフォーマンスをアプリケーションで測る

実装には工数がかかりますが、システムの要件に合わせて実装しておいてください。筆者の経験でも、レスポンスタイムを測り始めてから大きな遅延が起きていることに気づいたプロジェクトや、測っておらず大トラブルに至っているシステムが多くあったからです。

エラーのログ出力機能

エラーのログ出力機能も軽視されがちですが、実装すべきものの1つです。RDBMSがエラーを出力してもアプリケーション側で受け取っていなかったり、どこにもログを出力していないといったこともよく見られます。

しかし、一部のエラーコードはOracleのalertファイルには記録されないため、障害分析のためにもアプリケーション側のエラーログ出力機能は必要です。ただし、エラーメッセージにオブジェクト名が記述されているケースもあるため（図9）、エラーコードだけでなく時刻とエラーメッセージも出力してください。

図9 エラー出力の例

また、Javaの場合はprintStackTrace（）もコールしてください。これにより、どこでエラーが発生しているのかなどの情報が手に入るため、調査に役立ちます。

タイムアウト機能

次に、保険と言うべきタイムアウトの実装です。タイムアウトは、システム内のどこかで処理時間がかかりすぎた場合には処理を中断し、アプリケーションに制御を戻して、ユーザーに処理が失敗したことを通知する機能です。これにより、クライアントがDB処理を無限に待つ事態を防げます。

例えばJavaであれば、SetQueryTimeout（）が使用できます 注3。ただし、タイムアウトに伴うリトライには気をつけてください。タイムアウト後は自動的にリトライしてしまうコーディングにすると、処理に時間がかかるケースで過大な負荷をDBに与えることになります（図10）。

図10 リトライによりDBが過負荷になる図

注3：SetQueryTimeout（）の詳細はマニュアルを参照してください。また、サポート契約を結んでいる場合は、マニュアルだけではなくOracleサポートのKROWN（FAQ）の情報も積極的に利用してください。

コネクションプーリング機能

最近では、自前のコネクションプーリング機能を持つアプリケーションサーバーが増えてきました。これは喜ばしいことで、使えるのであれば積極的に活用して性能を引き出したいものです。

コネクションプーリングはアプリケーションから見たDBサーバーの性能（特にコネクト処理）を大きく向上させるもので、DBサーバーのリソース使用量も少なくて済みます。アプリケーションサーバーを使用していないシステムや、コネクションプールを持たないアプリケーションサーバーを使用しているシステムでも、JDBCが持つコネクションプーリング機能など使えるものがありますので検討してみてください。

アプリケーションのログ出力機能もトラブルになりやすいポイントです。アプリケーションのログは「サーバーごとに1つのファイル」といった具合に、全トランザクションが1つのファイルに書き込むのが普通だと思います。しかし、1つのファイルに書かせる仕組みの場合、高トランザクションシステムでは排他制御によりボトルネックになりやすいのです。

例えば、ファイルシステム上のファイルには、Reader/Writerロックと呼ばれるロックがあるため、複数プロセスからの書き込みは同時には行なえません（特殊な設定をOSに行なった場合は除く）。何度も書き込まなければならない場合も、回数×I/Oの時間が余分にかかることになり、業務処理をその分だけ遅くしてしまいます。

事前に調べておくべきロック関連の問題

アプリケーション側で設計時に解決すべきもう1つの大きな問題は、ロック関連の問題です。「DBのロックでトラブル発生」と言われると、DB自体が悪いようにも聞こえますが、実際にはアプリケーションがロックをかけています。当然、設計時にトラブルの種が蒔かれていることが多いので、それらを1つずつ順番に見ていきましょう。

順番に採番するためだけの表

「順番に採番するためだけの表」とは、主キーの値として使うために、1つずつ値を大きくしながらその値を保持する表です。複数のトランザクションが同じ番号を受け取らないよう、表にはロックをかけます。このため、同一表に対するデータ挿入トランザクションが同時に動けなくなります。もちろん、代替案としてはシーケンス（順序）の使用が挙げられます。

現場では業務チームが「番号が飛んでしまうこともあるから、シーケンスは使用できない」と言うことがあります。しかし、例外があるとはいえ、実際の多くのケースではシーケンスを採用しても多少不便になるだけで済みます。

ロックがある場所に集中してしまう設計

これは上記の「順番に採番するためだけの表」を含んでいます。例としては、どこかに必ずロックをかけてから何らかの処理をするシステムや、それほど行数が多くない、ある1つのテーブルに更新や読み込みの負荷が集中するようなシステムが挙げられます。この設計は、処理のメインパス（主な処理のフロー）とアクセスするテーブルを見るだけで分かります。ただし、次に説明するデッドロック防止のための実装（ロックテーブル）は例外です。

デッドロックを防止する実装

デッドロックとは、トランザクションのお見合い状態、言い換えるとお互いに相手のロックが解放されない限り処理が進まない状態であることは皆さんご存知でしょう。次に、この悪名高いデッドロックを防止する方法について説明します。

ここでは、トランザクション同士がデッドロックを起こさないように設計する方法を2つ紹介します。なお、システムによってはこれらの実装が困難な場合もあるので、その際は設計時点でデッドロックが起きないことを確認しておきましょう。

表の更新順序を決めておく

1つめは、表を更新する順序を決めておく方法です。例えば、表A、表B、表Cという3つの表がある場合、どのトランザクションも必ず表A、表B、表Cの順に処理をすると決めておけばデッドロックは起こりません。図11の例では、在庫表に対する処理をしてから入荷希望表を処理するというように更新順序を決めた場合は、デッドロックが発生していません。

図11 デッドロックが起きる、起きないの図

ロックテーブルを作成する

2つめは、ロックテーブルを作る方法です。ロックテーブルとは、ロックを管理するためのユーザー自作のテーブルで、この表の行をロックしない限り業務処理ができないように実装します。例えばレンタルショップのシステムにおいて、オンラインでは必ず会員表の該当するユーザーの行をSELECT FOR UPDATEでロックしてから処理するように実装すれば、その会員に紐付くデータはほかのオンライントランザクションが触れなくなります。このとき作成するロックテーブルはDB上でも構いませんが、できればアプリケーション上に実装してください。

Webシステムでの実装方法

Webシステムでは、

• DBのトランザクションは画面をまたがないこと
• 画面制御系のデータはアプリケーションサーバー側で持っておくようにすること

の2点がトラブル防止のポイントです。

まず1.ですが、DBのトランザクションが画面をまたがるように設計してしまうと、ブラウザの「閉じる」ボタン（×ボタン）を押された場合に、DBに対して「トランザクションをロールバックしてください」と通知することができません。そのため、DB上にロックが延々と残ってしまいます。

また、2.の“画面制御系のデータ”

とは、実際に画面を制御するデータや、アプリケーションのトランザクションが画面をまたがる場合のトランザクションデータを指します。これらのトランザクションデータは一時的なデータなので、APサーバー上で持っておいて、トランザクションとして終了できる状態になってからDBに書いたほうが良いでしょう。これらには方式設計フェーズ 注4で扱うべきものも含まれますので、方式設計の際にも気をつけてください。この辺は参考になる文献も増えてきたので、必要であればそちらをご覧ください 注5。

注4：方式設計とは、システム全体のアーキテクチャ設計にあたるものです。要件を満たすコンポーネントとコンポーネント間の関係／動作を記述します。設計の最初の段階でもあります。

注5：トランザクションについては『EJBアンチパターン』（日経BP社）が、J2EEを使ったWebシステム全般については『J2EEパフォーマンスチューニング徹底解説』（技術評論社）が筆者のお薦め書籍です。

そのほかの検討事項

アクセスパスを確認する

まだ行なっているプロジェクトは多くありませんが、筆者はSQL文のアクセスパス（データを得るまでのアクセス経路）を設計時、もしくは単体テスト時に確認しておくことをお勧めします（表）。アクセスパスを確認することで、どのようにデータにアクセスすれば、求める結果を得られるのかが分かります。

表 アクセスパス分析の例

例えば、「この業務のこのSQL文は、この表のこのインデックスを見て、その後にこのキーでこの表を見れば結果が得られるから、性能は大丈夫そうだ」とか、「この表には検索に使えそうなインデックスがないから、フルスキャンしてしまう。データ件数が莫大だから性能は悪そうだ」など、大体の目安になります。判断に迷うものも出てきますが、要確認ポイントとして、テスト時に重点的に確認しておいたほうが良いでしょう。 このアクセスパスを確認するのは設計の最後のほうになりますが、システムテスト、もしくはパフォーマンステストで性能が出ないことに気がつくというトラブルをできるだけ回避するためにもお勧めです。

実行計画の変動と固定を考慮する

次に、SQL文の実行計画（ここでは上記アクセスパスとほぼ同じ意味）の固定について、設計時に考えておくことを提案します。実行計画の固定には、業務チームとインフラ設計チームの協力が必要です。Oracleは実行計画を基にデータにアクセスし、処理を行ないますが、この実行計画を作るのがオプティマイザです。

オプティマイザには、大きく分けて「コストベース」と「ルールベース」という2つのモードがあります。まずルールベースですが、Oracle 10gからサポートされなくなったため使用すべきではありません。次にコストベースですが、これには実行計画が変動して性能が劣化してしまうというリスクがあります。筆者も、多くのシステムが実行計画の変動で困っているのを見てきました。

しかし、設計時からであれば、次のような方針で実行計画を固定できます。

• すべてOracle任せ（あえて固定しない）：一番コストはかからないが性能劣化の恐れあり
• 大事なSQLのみ何らかの方法（ヒント文、アウトラインなど）で固定：性能劣化してもシステム全体には大きな影響が出ないようにする
• アウトライン機能などによりすべてのSQL文をほぼ固定：手間がかかっても性能劣化を極力避けたいプロジェクト向き

Tipsで説明していますが、自動で生成されるRDBMSの実行計画のすべてが最適なわけではありません。そのため、常に最適を求めるのであれば、何らかの方法で固定することを考えるべきです。

答えは、一言で言えば「予想だから」です。Oracleは多様なレベルの情報を収集して予想をします。しかし、表の行数、表のサイズ、値の範囲、行の長さ、データの分布（ヒストグラム）、インデックスと表のデータの相関（クラスタリングファクタ）まで調べても、必ず最適な実行計画が選ばれるとは限らないのです。例えば、図12のようなケースがあり得ます。

図12 どうしても最適な実行計画を立てられない例

この図では、まず製品名から製品IDが分かります。その後、その製品IDを使って発注表にアクセスします。製品名が「AAA」の場合は発注表を全件検索したほうが高速ですが、「BBB」の場合はインデックスアクセスのほうが高速と言えます。しかし、Oracleにとっては、実際に表を検索してみない限り知るよしもありません（SQL文からは判断できません）。つまり、最適な実行計画を調べるためにはすべてのあり得る実行計画を毎回実行してみない限り分からないのです。

カットオーバー前に統計情報の取得をする

次に、統計情報の取得（DBMS_STATSもしくはANALYZE）について見ていきましょう。今まで統計情報の取得は、定期的に行なうべき、もしくはデータ量の変動が起きた後に行なうべきという方針が推奨されていました。しかし最近では、カットオーバー前に一度統計情報の取得を行ない、それ以降は行なわないという方針も推奨されるようになってきています。この方法であれば、実行計画の変動により、速くなったり遅くなったりするのを少なくできます。

ユーザーの体感はともかくとして、統計情報の取得によりほとんどのSQL文の処理は速くなるため、カットオーバー前に一度統計情報の取得をするという方針が絶対に良いとは言えませんが、「遅くしたくない」ことを優先したいのであれば採用する価値はあります。まとめると、システムによって程度の違いはあれ、実行計画の固定は設計時から考えておくこと、統計情報の取得に関してもリスクをとって積極的に行なう方法と、リスクをとらない方法があること、また、設計の段階でこれらの選択をすべきであることを理解していただければと思います。

SQLコーディングガイドを作成する

そもそも、ただ書くだけならともかく、性能まで意識したSQLコーディングは難しいと言えます。一部のプロジェクトではSQL文を書くエンジニアをエキスパートだけに限定することもあるほどです。そのような恵まれたプロジェクトではなくても、一定の品質を保ちたい場合には、SQLコーディングガイドを作ってください。SQLコーディングガイドとは、性能を出すためのSQL文の書き方のルールや、生産性や可読性を上げるためのルールなど、SQL文を書くにあたって守るべき事項をまとめたガイドラインです。コーディングガイドの詳細はほかの書籍に譲ることにします。

しかし、せっかくガイドを作ったとしても、それを守らなければ意味がありません。「スケジュールが厳しくなってくると守っていられない」という気持ちは理解できますが、後から大きな影響が出てしまうため、必ず遵守してください。

例として「バインド変数を使用すべき」というガイドを挙げ、ガイドがなぜ必要なのかを説明しましょう。まず、質問です。インデックスを使用して1行をSELECTするSQL文をバインド変数を使って書いた場合と、使わずに書いた場合にはどれくらいリソース使用量が違うのでしょうか？答えは、CPU時間にして数十パーセントから数倍（カーソルキャッシュを使えば数十倍）といったレベルで違いが生まれます。単体テスト時には分かりませんが、この数十パーセントから数十倍という違いがパフォーマンステストでは大きな違いとして表われてきます。

Part2　インフラ設計チーム編 >>

"門外不出のOracle現場ワザ" インデックスに戻る

Copyright © 2009, Oracle Corporation Japan. All rights reserved.

無断転載を禁ず

この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。

Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle

Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。

小田 圭二(おだ けいじ)

1996年日本オラクル入社。人事教育本部にて、新卒や中途採用社員に対し、データベースやOS、ネットワークの講師を5年ほど経験した後、2000年にテクノロジーコンサルティング本部に異動。 テクノロジーのコンサルタントとして、主に大規模ミッションクリティカルシステムを担当。

ポリシーは、「OracleもOS上で動くアプリケーションにすぎない。だから、OS、ストレージ、ネットワークを学ぶべき」。 スキル面の興味は、アーキテクチャ、DBA、インフラ技術、教育、コンサル手法など。