この記事の目的

パフォーマンス＆チューニング・シリーズ：クエリ処理と抽象プランからサンプルを借用し、実行計画の読み方を紐解いてみる。

目次

1.実行計画の表示
 2.実行計画の読み方
 3.参考

1. 実行計画の表示

SQLを実行して実行計画を確認する場合

//クエリプランの表示 
isql -Uxx -Pxx -Dxx -Sxx
set showplan on
go
sqlステート
//クエリプランの表示停止
set showplan off

SQLを実行しないで実行計画を確認する場合

//クエリプランの表示 
isql -Uxx -Pxx -Dxx -Sxx
set noexec on
go
set showplan on
go
sqlステート
//クエリプランの表示停止
set showplan off
go
set showplan off
go

物理・論理I/O 実行時間の確認

isql -Uxx -Pxx -Dxx -Sxx
//物理・論理I/O 実行時間の表示
set statistics io,time on
go
//物理・論理I/O 実行時間の表示停止
set statistics io,time off
go

2. 実行計画の読み方

それでは早速、サンプルを参考に実行計画を読んでみる。

use pubs2
go
set showplan on
go
select stores.stor_name, sales.ord_num
from stores, sales, salesdetail
where salesdetail.stor_id = sales.stor_id
and stores.stor_id = sales.stor_id
plan " ( m_join ( i_scan salesdetailind salesdetail)
( m_join ( i_scan salesind sales ) ( sort ( t_scan stores ) ) ) )"


文 1 (1 行目) のクエリ・プラン。
PLAN 句に Abstract Plan を使用して最適化しました。
STEP 1
クエリのタイプは SELECT です。
6 operator(s) under root
ROOT:EMIT Operator (VA = 6)
|MERGE JOIN Operator (Join Type: Inner Join) (VA = 5)
| ワーク・テーブル 3 を内部記憶に使用しています。
| Key Count: 1
| Key Ordering: ASC
|
| |SCAN Operator (VA = 0)
| | FROM TABLE
| | salesdetail
| | インデックス : salesdetailind
| | 前方スキャン
| | インデックスの最初に位置付けます。
| | インデックスには、必要なカラムがすべて含まれています。ベース・テーブルは読み込まれません。
| | インデックス・リーフ・ページに対して I/O サイズ 2 キロバイトを使用しています。
| | インデックス・リーフ・ページに対する LRU でのバッファ置換方式
|
| |MERGE JOIN Operator (Join Type: Inner Join) (VA = 4)
| | ワーク・テーブル 2 を内部記憶に使用しています。
| | Key Count: 1
| | Key Ordering: ASC
| |
| | |SCAN Operator (VA = 1)
| | | FROM TABLE
| | | sales
| | | テーブル・スキャンです。
| | | 前方スキャン
| | | テーブルの最初に位置付けます。
| | | データ・ページに対して I/O サイズ 2 キロバイトを使用しています。
| | | データ・ページに対する LRU でのバッファ置換方式
| |
| | |SORT Operator (VA = 3)
| | | ワーク・テーブル 1 を内部記憶に使用しています。
| | |
| | | |SCAN Operator (VA = 2)
| | | | FROM TABLE
| | | | stores
| | | | テーブル・スキャンです。
| | | | 前方スキャン
| | | | テーブルの最初に位置付けます。
| | | | データ・ページに対して I/O サイズ 2 キロバイトを使用しています。
| | | | データ・ページに対する LRU でのバッファ置換方式

実行計画を読む上で押さえておくべきポイントは以下の通り。

ツリー構造の見方

EMIT（実行結果をクライアントorローカル変数等に割り当てる演算子）を頂点にしたツリー構造になっていて、複数子リーフがある場合は一番左側のリーフから実行されていく。
サンプルを箇条書きでネストさせたイメージが以下。

    1. EMIT
       1. MERGE JOIN Operator(VA=5)
         1. SCAN Operator(VA=0)
         1. MERGEJOIN Operator(VA=4)
            1. SCAN Operator(VA=1)
            1. SORT Operator(VA=3)
               1. SCAN Operator(VA=2)

今回の例で言うと、VA=0のSCAN Operator FROM TABLE から実行される。

演算子の説明

パフォーマンス＆チューニング・シリーズ：クエリ処理と抽象プランから普段見かける演算子を抜粋。

SCAN

ローをクエリ・プランに読み込んで、クエリ・プラン内の別の演算子がさらに処理できるようにする。FROMメッセージが常に続き、実行中のSCANを明示する。

FROM TABLE

| |SCAN Operator (VA = 0)
| | FROM TABLE
| | salesdetail
| | インデックス : salesdetailind
| | 前方スキャン
| | インデックスの最初に位置付けます。
| | インデックスには、必要なカラムがすべて含まれています。ベース・テーブルは読み込まれません。
| | インデックス・リーフ・ページに対して I/O サイズ 2 キロバイトを使用しています。
| | インデックス・リーフ・ページに対する LRU でのバッファ置換方式

FROM TABLE
リソース元のテーブル名を表示。相関名があれば、次の行に相関名を出力する。
スキャン方法
1. Table Scan
2. Using Clustered Index
3. Index:indexname
  今回の例だと、salesdetailindがインデックスとして使われている。
スキャン方向
テーブルスキャンもしくは、インデックススキャンの方向を示す。スキャンの方向は、インデックス作成時に指定された順序、およびorder by句でカラムに対して指定された順序等により決まる。
1. 前方スキャン
2. 後方スキャン
テーブルもしくは、インデックスのリーフ・レベルへのアクセス実行
1. Positioning at start of table
  テーブルスキャンがテーブルの最初のローから始まって前に進む。
2. Positioning at end of table
  テーブルスキャンがテーブルの最後のローから始まって遡っていく。
3. Positioning by key
  最初に条件に合うローにスキャンを配置するために、インデックスが使用される。
4. Positioning at index start
5. Positioning at index end
補足
1. カバード・インデックス・スキャン
  クエリが必要とするカラムが全てインデックスに含まれる場合、オプティマイザは、インデックス・カラムに有益なキーがなくても、Table ScanではなくてもIndex Scanを選択する。
  この場合は「インデックスは必要なカラムをすべて含んでいます。ベース・テーブルは読み込まれません。」と出力される。

I/Oサイズのメッセージ

| | インデックス・リーフ・ページに対して I/O サイズ 2 キロバイトを使用しています。
| | インデックス・リーフ・ページに対する LRU でのバッファ置換方式

I/Oメッセージ
1. データ・ページに対して I/Oサイズ Nキロバイトを使用しています。
  テーブルスキャン、ベース・テーブル・アクセスの場合出力
2. インデックス・リーフ・ページに対して I/Oサイズ Nキロバイトを使用しています。
  カバード・インデックス・スキャン、ベース・テーブル・アクセスの場合出力
キャッシュ方式メッセージ
1. LRU置換方式
  最後にアクセスされたページができる限り長期化保持されるキャッシュに配置される。
2. MRU置換方式
  最後にアクセスされたページが、すでに置換されるキャッシュに配置される。

JOIN

文 1 (1 行目) のクエリ・プラン。
PLAN 句に Abstract Plan を使用して最適化しました。
STEP 1
クエリのタイプは SELECT です。
6 operator(s) under root
ROOT:EMIT Operator (VA = 6)
|MERGE JOIN Operator (Join Type: Inner Join) (VA = 5)
| ワーク・テーブル 3 を内部記憶に使用しています。
| Key Count: 1
| Key Ordering: ASC

オプティマイザによって様々なJOIN演算子が選択される。
公式ドキュメントの説明より個別に以下アルゴリズムを調べた方が遥かに分かりやすいので、ここでの説明は割愛。

NESTED LOOP JOIN
MERGE JOIN
HASH JOIN
NARY NESTED LOOP JOIN
SEMIJOIN

重複削除演算子

select distinct au_lname, au_fname
from authors
where au_lname = "Bloom"
文 1 (2 行目) のクエリ・プラン。
STEP 1
クエリのタイプは SELECT です。
2 operator(s) under root
ROOT:EMIT Operator (VA = 2)
|GROUP SORTED Operator (VA = 1)
|Distinct
|
| |SCAN Operator (VA = 0)
| | FROM TABLE
| | authors
| | インデックス : aunmind
| | 前方スキャン
| | キーによって位置付けます。
| | インデックスには、必要なカラムがすべて含まれています。ベース・テーブルは読み込まれません。
| | Keys are:
| | au_lname ASC
| | インデックス・リーフ・ページに対して I/O サイズ 2 キロバイトを使用しています。
| | With LRU Buffer Replacement Strategy for index leaf pages.インデックス・リーフ・
ページに対する LRU でのバッファ置換方式

SCANの段階でソートされている必要はない。
オプティマイザは入力がソートされていない場合、SORT DistinctかHASH Distinctのどちらかの演算子方式を選択できる。
オプティマイザの決定はコスト見積もりによって決まる。

(再掲)SORT演算子

SORT演算子はクエリ・プラン内に演算子が１つのみ存在する。
指定されたキーを使用して、入力ストリームから出力データ・ストリームを生成する。
SORT演算子は、ストリーミング・ソートを実行することがあるが、結果を一時的にワーク・テーブルに格納しなければならないことがあり、次のフォーマットで表示する。
- Using Worktable for internal storage.
- 内部記憶領域として Worktableを使用しています。

3. 参考

公式ドキュメント
バージョンは古いが、クエリプランの確認方法は以降も大きな変化はないはず。

SAPジャパンサイト
sp_xx コマンドの使用方法とか書いてある。
リファレンス的な感じ。

Qiita記事
本記事記載後にクラスタインデックスについて調べていたら見つけた記事。今度読んでみる。