SQLチューニングの必須知識を総ざらい（前編）：Oracle SQLチューニング講座（2）（1/2 ページ）

本連載では、Oracleデータベースのパフォーマンス・チューニングの中から、特にSQLのチューニングに注目して、実践レベルの手法を解説する。読者はOracleデータベースのアーキテクチャを理解し、運用管理の実務経験を積んでいることが望ましい。対象とするバージョンは現状で広く使われているOracle9iの機能を基本とするが、Oracle 10gで有効な情報も随時紹介していく。（編集局）

[加藤猛，株式会社アゲハ]

連載目次

パフォーマンス・チューニング概要

　前回「パフォーマンス向上の最短コースを知る」で説明したように、SQLのチューニングはOracleインスタンスやデータベースの構成などにも密接にかかわっています。すでに基本的な知識をお持ちの方も大勢いらっしゃると思いますが、それらの知識を「パフォーマンス」と結び付けて考えることが重要なポイントです。

　SQLによって取得されるデータが、実際にはどのような形式でファイルの中に格納されているのか、あるいはそのデータにはどのようにアクセスされるのかを理解することにより、現状の把握や、改善ポイントの絞り込みを的確に行うことができます。

　そこで、今回と次回の2回にわたってOracleデータベースの基礎知識を紹介し、SQLチューニングを実施する際にはどのような点を考慮するべきかについて確認していきます。

OracleのI/O単位

　SQL文によって表からレコードを取得する場合、実際のI/Oはデータ・ブロックと呼ばれる単位（2Kbytes、4Kbytes、8Kbytes、16Kbytesなど）で行われ、読み込まれたデータ・ブロックはデータベース・バッファ・キャッシュと呼ばれるメモリ上にキャッシュされます（図1）。

図1　OracleのファイルI/O
SELECT文による問い合わせが発行されると、データファイルから取得されたデータ・ブロックはデータベース・バッファ・キャッシュに保持される。次回の問い合わせでは、まずデータベース・バッファ・キャッシュを先に探し、存在しない場合にデータファイルにアクセスする

　ちなみに、取得したいデータを含むブロックが、すでにデータベース・バッファ・キャッシュ上にあった場合をキャッシュ・ヒット、存在しなかった場合をキャッシュ・ミスと呼びます。

　SQLチューニングを行う場合、「処理時間」はもちろんとして、そのSQLが「アクセスする必要のあるデータ・ブロック数を減らす」ということも念頭に置いてください。そうすることによって、データベース・バッファ・キャッシュがより効率的に使われ、また、キャッシュ・ミスの場合でも必要なデータ・ファイルへのI/Oは小さくなるため、システム全体の負荷を低減することが可能です。

　例えば、レコードの挿入、削除、更新によって、データ・ブロック内がフラグメント（断片化）してしまった場合を考えてみます。当初はすべてのレコードを参照するのに5ブロックの読み込みだけでよかったものが、フラグメントの発生によって9ブロックの読み込みが必要となる場合もあります（図2）。

図2　フラグメントの発生とアクセス・ブロック数
Oracleのデータベースは「表領域」の内部に表や索引、パーティションなどの「データ・セグメント」を持つ構造である。各データ・セグメントは「エクステント」と呼ばれるサイズで拡張される。エクステント内のデータ・ブロックに「レコード」が格納されている（図の上側）。
レコードのフラグメントが発生すると、読み込むデータ・ブロック数が増加しパフォーマンスが低下する（図の下側）。

　これは、アクセスするデータ・ブロック数が増加するほんの一例にすぎませんが、格納されるデータの特性（1レコード・サイズが大きいなど）に応じた適切な記憶領域パラメータの指定（PCTFREE^注1）やマルチ・ブロック・サイズの使用によって、SQL文実行時にアクセスされるデータ・ブロック数を調整することが可能です。

注1：PCTFREE

データ・ブロックにレコードを書き込む際に、どの程度の空き領域を確保するか、パーセントで指定する。この値が小さいと、レコードのデータサイズが増えた場合に同一のブロック内に収まらなくなり、別ブロックへの行移行が生じパフォーマンスが劣化する。デフォルト値は10％。

オプティマイザ

　多くの場合、あるSQL文が実行された際、取得したいレコードへのアクセス方法は複数存在します。例えば、1つの表を検索する場合を考えてみても、「表全体を読み込むのか、索引（後述）を使用するのか、また、索引を使用する場合は、どの索引を使用するのか」など、複数の方法が考えられます。このように、表へどのような経路でアクセスし、どのような順番、方法で結合するかといった組み合わせを「実行計画」と呼び、複数ある実行計画の中から、最も効率的と思われる実行計画を決定するのがオプティマイザの役割です（図3）。

図3　オプティマイザの役割

　オプティマイザには、一定のルールにのっとって実行計画を決定するルールベース・アプローチと、表、索引の統計情報を基にコストを見積もり、その結果によって実行計画を決定するコストベース・アプローチがあります。初期化パラメータ「OPTIMIZER_MODE」がデフォルトの「CHOOSE」である場合、対象とする表、索引の統計情報が取得されていればコストベース・アプローチ、統計情報が取得されていない場合はルールベース・アプローチで最適化されます。これらの動作は、SQL文単位、セッション単位などで変更することも可能です。

　Oracle9iまでは、コストベース、ルールベースのどちらでも使用することができます。必ずしもコストベースのオプティマイザを使用する必要はありませんが、パフォーマンスを向上させるために追加された機能の多くは、コストベース・オプティマイザでのみ使用可能となる点に注意が必要です。なお、Oracle 10gからはコストベースのオプティマイザのみがサポート対象となっています。

　次ページではレコードへのアクセス方法として、「全表スキャン」「ROWIDスキャン」「索引スキャン」の違いについて解説します。（次ページに続く）