【IRIS/Cache】【SQL】クエリプランを出力しよう

はじめに
EXPLAINの利用（IRIS)
1. ターミナルで動作を確認する
2. テキストファイルへの出力
ShowPlanの利用（IRIS/Cache）
おわりに

はじめに

CacheやIRISのバージョンを上げるとき、SQL オプティマイザが大幅に変わり、今まで順調に動作していたクエリが、突然遅くなる事がありました。

クエリの数が1,000を超えていたため、1つずつ確認する時間が取れず、クエリプランをテキストに出力して、機械的にチェックを行い難を逃れました。

今回は、その時に使用したクエリプランのテキスト出力をご紹介致します。

EXPLAINの利用（IRIS)

EXPLAIN コマンドを実行することでクエリ実行プランを生成できます。

ターミナルで動作を確認する

では、ターミナルでコマンドの確認をしてみましょう。
　※クエリ自体は7行目になります。

:sql

SAMPLE>:sql
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <<nothing>>.
Enter <command>, 'q' to quit, '?' for help.
[SQL]SAMPLE>>EXPLAIN select name from developer_data.Sample where code='B659'
3.      EXPLAIN select name from developer_data.Sample where code='B659'
 
Plan
"<plans>
 <plan>
 <sql>
 select name from developer_data.Sample where code='B659' /*#OPTIONS {""DynamicSQL"":1} */
 </sql>
 <cost value=""451.41""/>
 Read index map developer_data.Sample.idx, using the given %SQLUPPER(code), and looping on ID.
 For each row:
     Read master map developer_data.Sample.IDKEY, using the given idkey value.
     Output the row.
 </plan>
</plans>"
 
1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0004s/4/141/0ms
          execute time(s)/globals/cmds/disk: 0.0275s/1,498/46,443/0ms
                          cached query class: %sqlcq.SAMPLE.cls7
---------------------------------------------------------------------------

<cost>タグの下にクエリプランが記載されています。
念のため、管理ポータルのクエリプランと比較してみましょう。

クエリプランと相対コストが一致していることが分かります。

では、いよいよテキストへの出力になります。
適当なクラスを作成し、下記関数を作成してみました。

テキストファイルへの出力

簡単ですが、SQLを実行するコマンドとファイルへの出力サンプルになります。

ClassMethod explain(filePath As %String, query As %String)
{
	s stm = ##class(%SQL.Statement).%New()
	d stm.%Prepare("EXPLAIN "_query)
	s tre = stm.%Execute()
	s:( tre.%Next() ) txt = tre.%GetData(1)
	
	Open filePath:"NWS"
	Use filePath Write txt,!
	Close filePath
}

さて、関数実行時に作成されたファイルを確認してみると・・・

いいですね。
ちゃんとプランが出力されています。
後は、不要なタグ等を除去する処理を追加すれば、クエリプランの出力完成です！

ShowPlanの利用（IRIS/Cache）

CacheでもIRISでも両方使える汎用性を求めるのであれば「ShowPlan」を使います。

ClassMethod showPlan(filePath As %String, query As %String)
{
	s oldstat=$SYSTEM.SQL.SetSQLStatsJob(3)
	s sql=1
	s sql(1)=query
	d $system.SQL.ShowPlan(.sql,1,1)
	d $system.SQL.SetSQLStatsJob(oldstat)
	
	Open filePath:"NWS"
	f pos=1:1:%plan Use filePath w %plan(pos),!
	Close filePath
}

「ShowPlan」の第２引数に「1」を渡す事で、クエリプランが「%plan」に格納されます。
　→「0」だと、そのまま出力されるので、ターミナルで確認する場合は0の方が良いです。

後はテキストに出力、若しくはグローバルに格納すれば良いですね。
手に入ったクエリプランをどう料理するかは、使用者にお任せします。

一先ず、出力結果を確認したいと思います。

<plan>
<sql>
select name from developer_data.Sample where code='B659'
</sql>
<cost value="451.41"/>
<stats>
 <ModuleName>MAIN</ModuleName>
 <TimeSpent>0.0003373333333333333333</TimeSpent>
 <GlobalRefs>3</GlobalRefs>
 <CommandsExecuted>215</CommandsExecuted>
 <DiskWait>0</DiskWait>
 <RowCount>1</RowCount>
 <ModuleCount>1</ModuleCount>
 <Counter>3</Counter>
</stats>
 <stats>
  <ModuleName>FIRST</ModuleName>
  <TimeSpent>0.000054</TimeSpent>
  <GlobalRefs>2</GlobalRefs>
  <CommandsExecuted>45</CommandsExecuted>
  <DiskWait>0</DiskWait>
  <RowCount>0</RowCount>
  <ModuleCount>0</ModuleCount>
  <Counter>3</Counter>
 </stats>
 <stats>
  <ModuleName>B</ModuleName>
  <TimeSpent>0.00004966666666666666667</TimeSpent>
  <GlobalRefs>3</GlobalRefs>
  <CommandsExecuted>42</CommandsExecuted>
  <DiskWait>0</DiskWait>
  <RowCount>0</RowCount>
  <ModuleCount>1</ModuleCount>
  <Counter>3</Counter>
 </stats>
Read index map developer_data.Sample.idx, using the given %SQLUPPER(code), and looping on ID.
For each row:
    Read master map developer_data.Sample.IDKEY, using the given idkey value.
    Output the row.
</plan>

「EXPLAIN」と比較して、圧倒的に情報量が増えていますね。

色々分析をするのであれば、こちらの方が良いかもしれません。
「EXPLAIN」は簡易版といったところでしょうか。