4.5 パフォーマンスアップ

4.5 パフォーマンスアップ

SQLが実行される際に、性能をあげるためには以下の点を理解している必要があります。

  • SQLはOracleサーバ内でどのような手順を踏んで実行されるのか
  • 性能を発揮するためには何をすべきか

効率のよいデータベースを設計するためには、データベース管理者として、SQLが実行される仕組みを理解し、アプリケーションに応じた設定を行う必要があります。
ここでは、パフォーマンスに関係するSQLの処理について、以下の順に説明します。

  • SQL文実行の流れ
  • 実行計画作成のために必要な統計情報の取得方法
  • 実行計画の確認方法
  • 実行計画の読み方とSQLチューニングのための考慮点

4.5.1 SQL文実行の流れ

まず、SQL文が実行されるまでの処理の流れを確認します。
SQL文そのものはテキスト文ですから、SQL文を実行できる形の実行モジュールに変換する必要があります。
処理は大きく解析実行フェッチの3つのフェーズに分けられます。

解析フェーズ:解析処理では、SQL文の文法のチェックや、構文中に含まれるオブジェクトの存在や権限に関するチェックを行います。
また、表や索引の統計情報より、SQL文を実行するのに最適なアクセスバスや結合処理を選択し、実行計画を作成します。
作成された実行計画を元に実行モジュールが作成され、これらのオブジェクトがSGA上の共有プール内のライブラリキャッシュに格納されます。
格納されたオブジェクトは、まったく同じSQL文が次に実行される際に再利用されます。

実行フェーズ:解析フェーズで取得された実行モジュールに、必要に応じて変数を代入し、実行します。
実行フェーズで「物理読み込みブロック数が多い」「アクセスするブロック数が多い」「ソート処理でIOが頻発する」などの状況を読みとれる場合が多い傾向にあります。

フェッチフェーズ:配列をうまく使用していないと、1回のフェッチで1行しか取得しないなどの効率の悪い処理を行っていることがわかります。

処理手順を詳しく迫っていき、その中でチューニングのポイントを紹介します。

【STEP1】
まず、ライブラリキャッシュ上に同じSQL文(またはPL/SQLプロシージャ)がないかどうかを調べます。
一度解析されたSQL文(またはPL/SQLプロシージャ)は、他のプロセスと共有できるようにするため、共有プール上のライブラリキャッシュに格納されます。
共有プールが小さすぎると、せっかく解析された結果が上書きされてしまいます。
共有プールのサイズは、管理者がメモリパラメータSHARED_POOL_SIZEを指定することによって決められます。
共有プールのサイズが適切であるかどうかを調べるためにv$library_cache動的パフォーマンスビューを問い合わせます。
列pinsの値は総実行回数を、pin列の値は実行時に共有できるSQLがライブラリキャッシュ上に存在した回数を示します。
v$library_cacheの1-pin/pinsの値が1%を超えていたら、領域が不足しているため、解析済みのSQLが上書きされていることを示します。

【STEP2】
もし、ライブラリキャッシュに同一のSQL文がある場合、すでに存在する実行モジュールを実行するだけで、解析フェーズの処理を割愛することができます。
この解析処理をSOFT PARSEと呼びます。
同様のSQLを大量に処理するようなOLTP系の処理では、ここがひとつのチューニングポイントになります。
同一のSQL文かどうかの比較は、テキスト形式のSQLをASCIIのコードに変換し、そのコード値を使用して行います。
そこで、完全に同じSQL文である必要があり、スペースの数、大文字小文字もすべて一致している必要があります。
WHERE条件式も、値のレベルまで一致している必要があるため、条件式の値はパインド変数を使用することによって、同じ式であると思わせるようにします。
パインド変数の代入は、解析フェーズの後で行うため、代入前の式は同一のSQL文として実行されます。

パインド変数の使い方の例

● PL/SQLの場合:変数をそのまま使う
● Javaの場合:PreparedStatementを使用する。""で囲まれた部分に変数を代入する。
● Pro*シリーズの場合:変数を使用してEXEC SQLキーワードの後にSQL文を埋め込む例を記述。ソースで最初に宣言された変数は、SQL文中で「:変数名」という形で使用される。
【STEP3】
ライブラリキャッシュにSQL文が存在しなかった場合、図4-17の手順でSQL文を解析します。
この解析処理をHARD PARSEと呼びます。

このように、解析フェーズでコストをかけて作成された実行計画や実行モジュールは、比較元となるSQL文本体とともに共有プールのライブラリキャッシュに格納され、共有できるようになります。

< 前へ | 4.5 パフォーマンスアップ | 次へ >

解説トレーナー

Oracle / 上流工程 担当 中村 才千代

データベース設計、システム構築の上流~下流工程全般のインストラクターです。SE時代の経験を生かし「業務を知るエンジニアこそDB設計に関わるべき」「DB設計に携わるエンジニアは業務を知る人に知恵を貸してもらう」ことを伝えたいと思っています。

■認定・受賞

2000年 Oracle University「Best Instructor of the Year」受賞
2002年 Oracle University「Best Instructor of the Year」受賞

Page Top