SQLの実行と排他制御からDBの内部動作を知る(後編) (1/4):EnterpriseZine(エンタープライズジン)
Shoeisha Technology Media

EnterpriseZine(エンタープライズジン)

EnterpriseZine(エンタープライズジン)

テーマ別に探す

SQLの実行と排他制御からDBの内部動作を知る(後編)

2007/07/30 10:40

本稿ではOracleデータベースを例に、SQL文が発行されてから結果が戻されるまでに行なわれる処理について分かりやすく説明します。データファイルからメモリ上にデータを読み出して検索する流れを解説しますが、これが分かるとOracleの内部動作がよく見えてきます。SQL文を効率良く実行する機能(オプティマイザ)や、複数のユーザーが1つのデータにアクセスした際の混乱を防ぐ仕組み(ロック/排他制御)などはDBAの必須知識として、しっかり理解しましょう。

 (DB Magazine 2007年5月号より転載)

実行計画とオプティマイザと統計情報

ルールベースとコストベース

 前編では、SQL文がクライアントから発行されて実行されるまでに行なわれる内部処理の、おおまかな流れをつかんでもらいました。ここからは、その過程で使用される技術や実行される処理の詳しい説明に入ります。まずは、実行計画がどのように作られ、また何をもって最適な実行計画とするのかについて説明します。

 先ほど簡単に述べましたが、実行計画とは、SQL文を実行するために順番に実行される一連の操作手順のことです。サーバープロセスは実行計画に従って、SQL文を実行していきます。実行計画の中では、例えば索引を使用するのか(もしくは使用しないのか)、2つの表を結合させて検索する場合にどちらの表を先に検索するのか、結合にはどの方法を使用するのか、といったことが示されています。

 この実行計画は「オプティマイザ」によって作られます。オプティマイザとはSQL文を解析してデータを最も効率的に検索できる方法を探し、処理の順序を決定するエンジンです。Oracleは、「ルールベース」と「コストベース」という2種類のオプティマイザを持っています。

ルールベースオプティマイザ

 ルールベースとは、あらかじめ決められたルールに基づいて最適化された実行計画を作成する方法です。SQL文の内容(書き方)によって、適用されるルールが変化します。そのため、実行効率の良いSQL文の書き方を考慮することが必要です。

 ルールベースのメリットは、例えば索引があったら索引を使うなど、動作が直感的で分かりやすい点です。一方で、データの状況によっては索引を使用しないほうが検索が速い場合にも、必ず使用されてしまうなどのデメリットがあります。そのため、使用は推奨されていません。

 Oracleのルールベースオプティマイザは、バージョン7.2で仕様が凍結されており、バージョン7.3以降のパーティション表や索引構成表など、いくつかの機能を使用できません。また、Oracle DB 10gではサポート外となっています。

コストベースオプティマイザ

 コストベースとは、統計情報(後述)を基に各アクセス方法のコスト(処理の負荷)を計算して、最もコストが低い実行計画を選び出す方法です。通常、データベースのデータは時間が経つにつれて増えていきますが、その値に偏りが生じていく場合があります。コストベースオプティマイザは、データの内容に応じて最適な実行計画をその都度検討してくれます。これにより、ルールベースで問題であった「索引を使うとかえって遅くなる」ケースでは、索引を使用しない実行計画を生成します。

 ただし、コストベースでは正確な統計情報がないと、最適な実行計画が生成されません。そのため、統計情報を定期的に更新する必要があります。

コストベースオプティマイザの動作

 コストベースオプティマイザがコストを見積もる際には、以下のような手順で進めます(図10)。

図10:コストベースオプティマイザの動作
図10:コストベースオプティマイザの動作
  1. 選択性の判断

     選択性とは、WHERE句などで条件を指定している場合、その条件を満たす行が全行のうちどの程度あるかを表わす割合(%)です。この判断のために、統計情報を用います。

  1. カーディナリティの判断

     カーディナリティとは、表の列内の固有値の種類数を表わします。種類数が多いと“カーディナリティが高い”、種類数が少ないと“カーディナリティが低い”と表現します。社員表を例に考えてみると、氏名は同姓同名者がいる可能性は低いのでカーディナリティが高く、性別は男性と女性の2つしかありませんからカーディナリティが低くくなります。

  1. コストの算出

     選択性やカーディナリティが判明したら、いくつかの実行計画“案”について、実行時のディスクI/Oの回数やCPUとメモリの使用量などを「コスト」として見積もります。その中で、コストの一番低い案が選択されます。

 先ほどから何度も登場している「統計情報」とは、蓄積された表の行数やブロック数、行の平均的な長さ、列の値の偏りなどの情報を指します。この情報はデータディクショナリに格納されています。統計情報は自動的には取得されないので、DBMS_STATSパッケージやEMを使用して取得します。

 なお、統計情報は実際に格納されているデータファイル上の表を全表検索(後述)して取得します。そのため、大変負荷が高い処理となります。大きな表などの場合は、全体でなく部分的にサンプリングして情報を取得することもできます。Oracle DB 10gの場合、DBCA(Database Configuration Assistant)機能を使用してデータベースを作成している場合には、1日1回必要に応じて自動的に統計情報を取得するGATHER_STATS_JOBジョブが提供されています。

 また、統計情報が著しく実際の状態と異なっている場合には、オプティマイザは最適な実行計画を選ぶことができません。大規模なデータの更新を行なった場合には、自動取得が有効であるとしても、それまでに誤った実行計画が選ばれないよう、ただちに統計情報を再取得することもあります。

※この続きは、会員の方のみお読みいただけます(登録無料)。


著者プロフィール

  • 市川 道雄(イチカワ ミチオ)

    株式会社日立システムアンドサービス、プロフェッショナルサービス部に勤務。データベース関連の教育を担当後、現在は並行してサポート/ソリューションサービス部門と連携した教育企画/実施を担当している。Oracle認定講師。データベーススペシャリスト、ORACLE MASTER Platinum Oracl...

バックナンバー

連載:DB Magazineスペシャル

もっと読む

All contents copyright © 2007-2017 Shoeisha Co., Ltd. All rights reserved. ver.1.5