SQL クエリ・プランの解釈

プランの表示

EXPLAIN ツールまたは [プラン表示] ツールを使用して、SELECT、DECLARE、UPDATE、DELETE、TRUNCATE TABLE の各操作と、一部の INSERT 操作の実行プランを表示できます。これらは、実行の一環として SELECT を使用するため、総称してクエリ操作と呼びます。InterSystems IRIS では、実行プランは、クエリ操作が準備されたときに生成されます。実行プランを生成するために、実際にクエリを実行する必要はありません。

これらのツールには既定で、InterSystems IRIS が最適であると見なしたクエリ・プランの内容が表示されます。ほとんどのクエリには、有効なクエリ・プランが複数存在します。InterSystems IRIS が最適であると見なしたクエリ・プランに加えて、代替クエリ実行プランを生成および表示することもできます。

InterSystems IRIS には、以下のクエリ・プラン・ツールが用意されています。

• SQL EXPLAIN コマンド。このコマンドを使用すると、XML 形式のクエリ・プランと、オプションで代替クエリ・プランおよび SQL 統計を生成・表示できます。生成されたすべてのクエリ・プランと統計は、Plan という名前の単一の結果セット・フィールドに含まれます。EXPLAIN コマンドは SELECT クエリでのみ使用できることに注意してください。

• 管理ポータル→[システムエクスプローラ]→SQL インタフェースの [プラン表示] ボタン。

• 管理ポータル→[システムエクスプローラ]→[ツール]→[SQL パフォーマンス・ツール]。

• $SYSTEM.SQL.Explain() メソッド。このメソッドを使用すると、XML 形式のクエリ・プランと、オプションで代替クエリ・プランを生成・表示できます。

• SQL シェルでシェル・コマンドの SHOW PLAN と SHOW PLANALT を使用して、最近実行したクエリの実行プランを表示できます。

生成された %PARALLEL クエリおよびシャード・クエリの場合、これらのツールでは、該当するクエリ・プランがすべて表示されます。

EXPLAIN の使用法

以下の例のように EXPLAIN コマンドを実行することでクエリ実行プランを生成できます。

EXPLAIN SELECT TOP 10 Name,DOB FROM Sample.Person

ALT キーワードを使用すると、EXPLAIN コマンドによって代替のクエリ・プランが生成され、返されるクエリ・プランに反映されます。

STAT キーワードを使用すると、EXPLAIN コマンドによってクエリのモジュールごとにパフォーマンス統計が生成されます。モジュールごとに以下の統計が返されます。

• <ModuleName> ： モジュール名。

• <TimeSpent> ： モジュールの総実行時間 (秒単位)。

• <GlobalRefs> ： グローバル参照の数。

• <LinesOfCode> ： コード実行行数。

• <DiskWait> ： ディスク待機時間 (秒単位)。

• <RowCount> ： 結果セット内の行数。

• <ModuleCount> ： このモジュールが実行された回数。

• <Counter> ： このプログラムが実行された回数。

クエリ・プランは XML 形式の文字列で返されます。その最上位のタグは <plans> です。ALT キーワードを指定しない場合、<plans> タグの下位には <plan> タグが 1 つだけ存在します。そのタグの下位には、クエリを指定した <sql> タグ、この特定のプランの相対コストを記述した <cost> タグ、および SQL オプティマイザでクエリがどのように処理されたかの説明があります。ALT キーワードを指定した場合は、<plans> タグの下位に複数の <plan> タグが作成されます。STAT キーワードを指定した場合は、<cost> タグの下位に複数の <stats> タグが存在します。クエリの処理に関連するモジュールごとに別々の <stats> タグが生成されます。

プランの構造

[プラン表示] の実行プランは、文テキストとクエリ・プランの 2 つのコンポーネントで構成されています。

[ステートメント・テキスト] には元のクエリが複製されますが、変更点として、管理ポータルの [SQL] インタフェースにある [プラン表示] ボタンを使用すると、コメントと改行が削除された SQL 文が表示されます。空白は標準化されます。[プラン表示] ボタンは、各リテラルを ? で置換するリテラル置換の実行も表示します。ただし、二重の括弧でリテラル値を囲むことで、リテラル置換を抑制している場合を除きます。Explain() メソッドを使用してプラン表示を表示したときや、[SQL 実行時統計情報] ツールまたは [別のプランを表示] ツールを使用して表示したときには、このような変更は行われません。

[クエリプラン] には、クエリの実行に使用されるプランが示されます。クエリ・プランの内容は以下のとおりです。

• [Frozen Plan凍結プラン] は、クエリ・プランが凍結されている場合に、[クエリプラン] の先頭行に表示されます。それ以外の場合、先頭行は空白になります。

• [相対コスト] は、同じクエリの異なる実行プランの効率を比較するための抽象的な数値として、多数の要因から計算される整数値です。この計算では、さまざまな要因の中でも特にクエリの複雑さ、インデックスの有無、およびテーブルのサイズが考慮されます。相対コストは、2 つの異なるクエリの比較には有用ではありません。WHERE 節を指定していない COUNT(*) または MAX(%ID) などの特定の集約クエリによって “Relative cost not available” が返されます。

• [クエリプラン] は、メイン・モジュールと 1 つ以上のサブコンポーネント (必要な場合) で構成されます。1 つ以上のモジュール・サブコンポーネントが表示される場合は、B から始まるアルファベット順の名前が付けられ (Module:B、Module:C など)、実行順にリストされます (アルファベット順とは限りません)。

  既定では、モジュールは処理を実行し、その結果を内部一時ファイル (内部一時テーブル) に入力します。"コメント・オプション" に記載されているように、/*#OPTIONS {"NoTempFile":1} */ を指定することにより、内部一時ファイルを生成しないクエリ・プランを作成するようクエリ・オプティマイザに強制することもできます。

  名前が付いたサブクエリ・モジュールは、クエリ内のサブクエリごとに表示されます。サブクエリ・モジュールには、アルファベット順に名前が付けられます。サブクエリの命名時には、1 つ以上の文字をスキップして各サブクエリに名前が付けられます。アルファベットの最後に達すると、追加のサブクエリには、Z=26 と解析し、同じスキップ・シーケンスを使用して番号が付けられます。例えば、Subquery:F から始まる 2 つおきのサブクエリ命名シーケンスは、F、I、L、O、R、U、X、27、30、33 となります。また、Subquery:G から始まる 1 つおきのサブクエリ命名シーケンスは、G、I、K、M、O、Q、S、U、W、Y、27、29 となります。サブクエリがモジュールを呼び出す場合、そのモジュールは、スキップなしでサブクエリの後にアルファベット順に配置されます。例えば、Subquery:H を指定すると Module:I が呼び出されます。

• メイン・モジュールの最初の箇条項目が "Read master map" である場合、非効率的なクエリ・プランであることを示しています。クエリ・プランは、Read master map...(使用可能なインデックスがない)、Read index map...(使用可能なインデックスを使用)、または Generate a stream of idkey values using the multi-index combination...(マルチ・インデックス、複数インデックスを使用) のいずれかのマップ・タイプの文で実行を開始します。マスタ・マップは、データのインデックスを読み取るのではなく、データ自体を読み取るため、ほとんどの場合、Read master map... は非効率的なクエリ・プランであることを示しています。テーブルが比較的小さなものでない限り、クエリ・プランを再生成したときに最初のマップに Read index map... と表示されるように、インデックスを定義する必要があります。クエリ・プランの解釈の詳細は、"SQL 実行プランの解釈" を参照してください。

一部の操作では、クエリ・プランが生成されない可能性のあることを示す [プラン表示] が作成されます。

• 非クエリの INSERT：INSERT... VALUES() コマンドはクエリを実行しません。そのため、クエリ・プランは生成されません。

• 常に FALSE のクエリ：InterSystems IRIS は、クエリ条件が常に false になるためにデータを返せないクエリを準備している状況を判断できます。この状況は、[プラン表示] の [クエリプラン] コンポーネントで通知されます。例えば、条件 WHERE %ID IS NULL または条件 WHERE Name %STARTSWITH('A') AND Name IS NULL を含むクエリはデータを返せないため、InterSystems IRIS は実行プランを生成しません。実行プランを生成する代わりに、[クエリプラン] は “Output no rows” というメッセージを示します。クエリに、これらの条件のいずれかを指定したサブクエリが含まれている場合、そのサブクエリ・モジュールについての [クエリプラン] には “Subquery result NULL, found no rows” というメッセージが表示されます。この条件チェックは、NULL が関与するいくつかの状況に制限されていて、自己矛盾するクエリ条件のすべてを補足することを意図していません。

• 不正なクエリ：[プラン表示] には、ほとんどの不正なクエリに対して SQLCODE エラー・メッセージを表示します。ただし、[プラン表示] は空として表示することもあります。例えば、WHERE Name = $$$$$ や WHERE Name %STARTSWITH('A") (単一引用符と二重引用符に注目してください) などが挙げられます。このような場合、[プラン表示] には [文テキスト] が表示されません。また、[クエリプラン] には [No plan created for this statement] というメッセージが表示されます。一般に、これはリテラルを区切っている引用符が釣り合っていない場合に発生します。また、ユーザ定義 ("外部") 関数に正しい構文を指定しないで、複数のドル記号を先頭に指定した場合にも発生します。

プランの読み取り

プラン表示の結果は、クエリで指定したデータにアクセスして提示するためにどのような処理が実行されるかを示す一連の文です。以下では、プラン表示の文の解釈方法について説明します。

別のプランを表示

管理ポータルまたは Explain() メソッドを使用して、クエリの代替実行プランを表示できます。

管理ポータルからクエリの代替実行プランを表示するには、以下のいずれかを使用します。

• [システムエクスプローラ]、[ツール]、[SQL パフォーマンス・ツール]、[代替表示プラン] の順に選択します。

• [システムエクスプローラ]、[SQL] の順に選択し、[ツール] ドロップダウン・メニューから [代替表示プラン] を選択します。

[代替表示プラン] ツールを使用して、以下の手順を実行します。

1. SQL クエリ・テキストを入力するか、[履歴を表示] ボタンを使用して SQL クエリ・テキストを取得します。右側にある [X] の円をクリックすると、クエリ・テキスト・フィールドをクリアできます。

2. [プラン表示のオプション] ボタンを押して、複数の代替表示プランを表示します。[... をバックグラウンドで実行] チェック・ボックスは、既定ではチェックが外されています。ほとんどのクエリでは、この設定をお勧めします。大規模なクエリや複雑なクエリについては、[... をバックグラウンドで実行] チェック・ボックスにチェックを付けることをお勧めします。長いクエリがバックグラウンドで実行されている間、[プロセス表示] ボタンが表示されます。[プロセス表示] をクリックすると、[プロセス詳細] ページが新しいタブで開きます。[プロセス詳細] ページから、プロセスを参照できます。また、プロセスを一時停止、再開、または終了することもできます。

3. プランの候補は、マップ・タイプおよび開始マップと共に、コストに基づいて昇順でリストされます。それぞれのプランについて [プラン表示] (統計なし) または [統計付きプランを表示] のリンクを選択すると、詳細を表示できます。

4. プランの候補のリストから、チェック・ボックスを使用して、比較するプランを選択した後、[プラン表示を統計と比較] ボタンを押してそれらを実行し、それぞれの SQL 統計を表示します。

Explain()Opens in a new tab メソッドと共に all 修飾子を使用すると、1 つのクエリの実行プランがすべて表示されます。InterSystems IRIS が最適 (最小コスト) であると見なしたプランが最初に表示され、続いて代替プランが表示されます。代替プランは、コストの昇順でリストされます。

以下の例は、最適な実行プランを表示してから、代替クエリをリストします。

  DO $SYSTEM.SQL.SetSQLStatsFlagJob(3)
  SET mysql=1
  SET mysql(1)="SELECT TOP 4 Name,DOB FROM Sample.Person ORDER BY Age"
  DO $SYSTEM.SQL.Explain(.mysql,{"all":1},,.plan)
  ZWRITE plan

%SYS.PTools.StatsSQLOpens in a new tab クラス内の possiblePlans メソッドも参照してください。