InterSystems Symposia 2011

C hé SQL パフォーマンスチューニング
Caché
パフォ マンスチ
ニングテクニカルコンサルタント
上中進太郎
インターシステムズジャパン

Agenda• Caché SQL 概要– データ・インデックスグローバルの構造– 埋め込み埋め込みSQL
SQL、動的SQLSQLが処理される仕組みが処理される仕組み
SQL、動的– クエリのロジック• SQL
SQLパフォーマンスチューニング
パフォーマンスチューニング
– クエリプラン– チューニング• TuneTable
TuneTable、
bl 、Index追加
Index
d 追加
– クエリキーワード• SQL
SQLパフォーマンス計測
パフォーマンス計測– %SYS.PToolsユーティリティ
SYS.PToolsユーティリティ

21

InterSystems Symposia 2011

Caché SQL概要• CachéCachéののSQLはどうやって動いてる？
SQLはどうやって動いてる？
– サンプルクラス定義– グローバル構造（データ、インデックス）– 埋め込み埋め込みSQLSQLの仕組みの仕組み– 動的動的SQLSQLの仕組みの仕組み– SQL
SQLロジックの基本的な考え⽅
ロジックの基本的な考え⽅

3

Caché SQL概要

サンプルクラス定義
Class Symposia.Person Extends %Persistent{Property RegID As %Integer;
Property Name As %String;
Property CompanyName As %String;I d RegIDIndex
Index
R IDI d On
O RegID
R ID [ UUniquei]];Index CoNameIndex On CompanyName [ Type =bitmap ];}42

InterSystems Symposia 2011

Caché SQL概要

データグローバル^Symposia.PersonD=10Symposia.PersonD=10 ← ID
IDを採を採番する
番する^Symposia.PersonD(1)=$lb(“”,774,“Symposia.PersonD(1)=$lb(“”,774,“⼭⽥太郎⼭⽥太郎","","ロイヤル薬品証券ロイヤル薬品証券")")^Symposia.PersonD(2)=$lb(“”,894,“
Symposia.PersonD(2)=$lb(“”,894,“鈴⽊花⼦鈴⽊花⼦”,””,”サイバー技研興業サイバー技研興業“)“)• データは
データは$List$List形式で格納されている形式で格納されている– $List$Listの構造の構造②Ⓝ17②Ⓝ
17⑫Ⓣ⑫ⓉIInterSystems⑤Ⓣ⑫ⓉInterSystems
I t S t⑤ⓉT⑤Ⓣ⑤ⓉTokyo
T kTokyo↑各データの前にデータ⻑、データ型がバイナリ形式で格納されている
5

Caché SQL概要

インデックスグローバル• 標準インデックス^Symposia.PersonI
Symposia.PersonI("RegIDIndex",("RegIDIndex",774774,,1)="“^Symposia.PersonI
Symposia.PersonI("RegIDIndex",Si PI("RegIDIndex",894
I("R("R IDI d " 894,894,2)="“) "“グローバル名((インデックス名,グローバル名
インデックス名,対象プロパティ
対象プロパティ1,1,・・・・,ID),ID)=$LB(インデックスデータ=$LB(インデックスデータ))• ビットマップ
ビットマップインデックス
インデックス^Symposia.PersonI
Symposia.PersonI(“(“CoNameIndex
CoNameIndex“,”“,” ロイヤル薬品証券
ロイヤル薬品証券””,1)=$c(150,1,1,0,1,0)/*$bit(01=$c(150,1,1,0,1,0)/*$bit(01)*/

グローバル名((インデックス名,グローバル名
インデックス名,対象データ
対象データ,chunk),chunk)=010000001000. . . . . . .
１chunkに最⼤64000bit
63InterSystems Symposia 2011

クエリを実⾏したらどのように処理されているのか？

Caché SQL概要• 埋め込み埋め込みSQL
SQL、、クラスクエリは– そのルーチンやクラスにクエリが展開される
SQL1.mac

SQL1.int

7

クエリを実⾏したらどのように処理されているのか？

Caché SQL概要

• 動的
動的SQL
SQLや外部からのや外部からのxDBC
xDBC接続では接続では– クエリキャッシュができる– クエリキャッシュをクラス等で参照するには SMP
SMP→→構成
構成→
→SQL
SQL設定
設定→
→
⼀般SQL
⼀般
SQL設定
設定→
→クエリキャッシュ
クエリキャッシュ--ソース保存 = はい に変更

xDBC経由のSQLや%ResultSet API の場合%sqlcq.[NS name].[n].mac
新しい%SQL.Statement APIの場合%sqlcq.[NS name].cls[n].cls
がクエリキャッシュとして⽣成される*使⽤しているバージョンによって異なるクエリキャッシュ名となる可能性がある。

SQL2mac
84InterSystems Symposia 2011

Caché SQL概要

クエリのロジックを予想してみる
Select RegID from Symposia.Person
where CompanyName = ʻAAAʼ Order by Name
^Symposia.PersonD=93
^Si P
D 93
^Symposia.PersonD(1)=$lb(“”,774,”⼭⽥太郎","ロイヤル薬品証券")^Symposia.PersonD(2)=$lb(“”,894,“鈴⽊花⼦”,“サイバー技研興業“)・・・^Symposia.PersonI("CoNameIndex"," AAA",1)=$bit(00010000010000001)^Symposia.PersonI("CoNameIndex"," BBB",1)=$bit(00001)$bit(00001) ・・・

9CompanyNameもIndex指定してあるので、^Symposia.PersonI(“CoNameIndex”,”AAA”,n)を参照して該当するデータグローバルのID、Name取得し、その後ソートのためにNameをキーにテンポラリグローバル^CacheTemp(Name,id)=RegIDに⼀度格納してから、最終的にデータを取りだす

SQLパフォーマンスチューニング

パフォーマンス調査の第⼀歩• いちいちロジックを予想したり、ルーチンを⾒たりはしていられないので・・・• クエリプランをみよう！
– 管理管理ポータル
ポータルののSQL→SQL→SQL⽂の実⾏
SQL⽂の実⾏→→クエリ
プランの表⽰• クエリを実⾏しなくても表⽰できる– 管理ポ
管理ポータルの管理ポータルのSQL
タルのSQL→
タルのSQL→スキスキーマ→スキーマ
マ→クエリキャマ→ッシュ• 動的動的SQL
SQL、、xDBCクエリのプランが確認できる
xDBCクエリのプランが確認できる– STUDIOSTUDIOでクエリを選択して右クリックでクエリを選択して右クリック→SQL⽂に対するプラン表⽰
SQL⽂に対するプラン表⽰105InterSystems Symposia 2011

SQLパフォーマンスチューニング

クエリプランの具体的な⾒⽅• 英語で処理内容を表記– 次ペジでキ ワ ド紹介
次ページでキーワード紹介• 相対コスト
相対コストととは– 同⼀クエリ同⼠で⽐較できる相対的なコスト。主にインデックス追加前後の効果を測定できる。相対コストが低いプランのほうが良い。
– 異なるクエリ同⼠のコスト⽐較は意味がない。
ク⼠
ト⽐較 意味が11

SQLパフォーマンスチューニング

クエリプラン キーワード• Call module n– この表記がある場合は、そのこの表記がある場合は、そのmodulemodule部分を部分を先に呼び出している

12

6

InterSystems Symposia 2011

SQLパフォーマンスチューニング

クエリプラン キーワード
キーワード

意味

Read master map

データグローバルを参照

Read index map

インデックスグローバルを参照

using the given yyy

主にクエリのパラメータとして与えられたyyyを使⽤してインデックス or データ本体の値を取得

looping on xxx

xxxでインデックス or データ本体をループwith a %STARTSWITH range
condition

前⽅⼀致条件でループ

13

SQLパフォーマンスチューニング

クエリプラン キーワード
キーワード

意味

Add ID bit to bitmap temp-file A

各モジュールでの検索結果をテンポ
ラリ領域にビットマップ形式で保存

Add a row to temp-file A,
subscripted
by %SQLSTRING(AAA) andID,with node data of BBB.

各モジュールの検索結果をテンポラ
リ領域に サブスクリプト にAAAとID
を配列形式で、データ部にBBBを保存 例： ^temp(AAA,ID)=BBB

A
Accumulate
l t theth max(xxx).
()xxxを計算する。Maxの場合は、⽐較、を計算する M の場合は ⽐較
Suｍの場合は⾜し算など(((index map INDEXNAME)UNION (bitmap temp-file A))
UNION (bitmap temp-file B))

INDEXあるいはテンポラリ領域の複数の結果をUNION処理

147

InterSystems Symposia 2011

SQLパフォーマンスチューニング

クエリプランで特に注意する点• Read master map Symposia.Table1.IDKEY,
Symposia.Table1.IDKEY,
looping on ID
ID..

– これは全データグローバルをループして参照しているということなので、データ件数の多いテーブルの場合はよくない。
– Read master map Symposia.Table1.IDKEY, using
the given idkey value. は問題ない。

• Add a row to temp
temp--file A– テンポラリグローバルに書いているということなので、件数にもよりますが、あまりよくない。この内容がインデックス追加のヒントになることもあります。

15

SQLパフォーマンスチューニング

クエリチューニング• まずは
まずはTuneTable
TuneTable！
！
– 管理ポータル
管理ポータル→→SQL→SQL→SQLスキーマを参照
SQLスキーマを参照• スキーマを表⽰してスキーマ全体のチューニング
スキ マを表⽰してスキ マ全体のチング• テーブルを個別に表⽰してテーブルのチューニング

– $SYSTEM.SQL.TuneTable
SYSTEM.SQL.TuneTable((tablename)tablename)• TuneTable
TuneTableは以下の⼆つを計測しているは以下の⼆つを計測している
以下を計測して る– 選択性（選択性（SELECTIVITY
SELECTIVITY））– エクステントサイズ（エクステントサイズ（EXTENTSIZE
EXTENTSIZE））
16

8

InterSystems Symposia 2011

SQLパフォーマンスチューニング

選択性 (SELECTIVITY)• 選択性はデータの分散度合いを選択性はデータの分散度合いを抽出して抽出して計測している
計測している–N %– 1 → ユニークであることを表している
ク あとを表し＊性別のフィールドの選択性は５０％になる。
→実際のデータ分布は関係ない• 例えばデータ上は男性７０％、⼥性３０％であったとしても 「男性」 「⼥性」というデータが抽出されたのても、「男性」、「⼥性」というデータが抽出されたのであれば、５０％となる。
• ９９％のデータが０の「削除フラグ」でも、削除フラグ＝１のデータが抽出されると５０％となる→ 管理ポータルから値を指定することも可能
17

SQLパフォーマンスチューニング

エクステントサイズ• テーブルの総データ件数を計測• エクステントサイズは複数テーブル間のエクステントサイズは複数テーブル間のJOIN
JOINがが発⽣する場合に、どのテーブルから絞り込みを発⽣す 場合に どブルから絞り込 を開始するか、決定するのに使⽤される。
SELECT
count(*)
FROM
Symposia.Table2,Symposia.Table3,Symposia.Table4
y p, y p, y p
WHERE
T2Key=T3Key AND T2Key=T4Key AND
T2Value %STARTSWITH 'A' AND
T3Value %STARTSWITH 'B' AND
18

T4Value %STARTSWITH 'C'

9InterSystems Symposia 2011

SQLパフォーマンスチューニング

TuneTableはいつ⾏う？
• システム稼働前に実⾏し、空のテーブルについては予測値を⼊れておく。
• 通常は定期的に⾏う必要はない。パフォ
マンスに問題が通常は定期的に⾏う必要はない パフォーマンスに問題が発⽣していない場合は特に⾏う必要がない。
• 各テーブルの選択性やエクステントサイズのテーブル間の⽐率が⼤きく変わった場合は効果あり• 注意点– TuneTable
TuneTable実⾏中はシステムに実⾏中はシステムに負荷がかかります
負荷がかかります。
– TuneTable
TuneTable実⾏直後、関連する実⾏直後、関連するテーブルのすべてのクエリキャッシュがパージされます→ッシュがパージされます→次回実⾏時に新たに⽣成されるため負荷がかかります。

稼働直後
稼働

トランB
トランA
マスタ２
マスタ１

１年後

19

SQLパフォーマンスチューニング

インデックスを追加しよう• どんなインデックスを追加する？

– Q. 標準インデックス？ビットマップ？
– A. 対象プロパティの対象プロパティの個別値
個別値がが10000
10000〜〜20000
20000個を超える個を超える場合には標準インデックスを使⽤したほうが良い。

• データの分布を考えてインデックスを選択するとさらに良い。
– 10
10万件に万件に11件しかない「削除フラグ＝１」を検出したい→件しかない「削除フラグ＝１」を検出したい→標準• 条件に条件にAND
AND 検索や検索やOROR検索が多い
検索が多い→ビットマップ検索が多い→
ビ トマ プ• COUNT()COUNT()クエリを⾼速化したい
クエリを⾼速化したい→→ビットマップ– 条件なし条件なしCount()
Count()の⾼速化の⾼速化→→エクステントビットマップ
2010InterSystems Symposia 2011

SQLパフォーマンスチューニング

インデックスを追加しよう• 対象テーブルにフィールドが多い場合はインデックスデー
タを活⽤• ある特定のクエリに対して専⽤インデックスを設定して⾼ある特定 ク リに対し 専⽤インデ ク を設定し ⾼速化したい→速化したい→標準インデックス• クエリプランの Add a row to temptemp-file X をヒントにしてみる
SELECT P1 FROM Symposia.Table1
Symposia Table1
WHERE P2 = 'V2411ʼ ORDER BY P3,P4→ Index SpecialIndex On (P2, P3, P4) [Data = P1];

21

SQLパフォーマンスチューニング

インデックスを使わせる• クエリプロセッサがインデックスを使わない⽅法を選択することがある。

– レコード件数が少ないので、インデックスを⾒るよりデータを直接⾒たほうが早いと判断する– 恣意的にインデックスを使わせたい場合は選択性、エクステントサイズなどを変更する• 条件となるフィールドが関数を使⽤しているとインデッ
クスを使⽤できない– 例：
例：TimeStamp
TimeStamp型フィールド型フィールド((SalesDate)
SalesDate)を⽇付(2000を⽇付(2000--0101-01)と⽐較
01)と⽐較

SELECT * FROM Symposia.Table5 where SalesDate > ʻ2000
ʻ2000--01
01--01
00:00:00ʼ OK
SELECT * FROM Symposia.Table5 where {fnCONVERT(SalesDate,SQL_DATECONVERT(SalesDate,SQL_DATE)})} > ʻ2000ʻ2000-01
01--01ʼ NG
SELECT * FROM Symposia.Table5 where SalesDate > {fnCONVERT(ʻ2000--01
CONVERT(ʻ200001--01ʼ,SQL_TIMESTAMP)} OK
2211

InterSystems Symposia 2011

SQLパフォーマンスチューニング

インデックスを使わせる• 最近実際にあったケース– Oracle からデータをSQLからデータをSQL Gatewayで移⾏
Gatewayで移⾏
DATE型は時間情報まで含むので、型は時間情報まで含むので、Caché
DATE型は時間情報まで含むので型は時間情報まで含むので CachéはCachéは– Oracle のDATETIMESTAMP型に変換
TIMESTAMP型に変換– クエリは ⽇付を
⽇付をTO_DATETO_DATE関数で指定しているので、関数で指定しているので、インデックスを使わない！
• 解決策
案1. 指定をTO_DATE
指定をTO_DATEではなくではなくTO_TIMESTAMP
TO_TIMESTAMPに変更に変更
Where visitdate = TO_DATE(ʻ2011/06/03ʼ,ʼyyyy/mm/
TO_DATE(ʻ2011/06/03ʼ,ʼyyyy/mm/ddddʼ)ʼ)
Where visitdate = TO_TIMESTAMP
TO_TIMESTAMP(ʻ2011/06/03ʼ,ʼyyyy/mm/(ʻ2011/06/03ʼ,ʼyyyy/mm/ddddʼ)ʼ)

案2. 時間情報はいらない場合、移⾏プログラムを作成してCachéしてCaché側でも側でもDATE
DATE型を利⽤する型を利⽤する
23

SQLパフォーマンスチューニング

ストアド関数をうまく使う• 複雑な条件をもつクエリをストアド関数を使って簡略化する。

– SELECT count(*) FROM Symposia.Table2 where T2Value
like '%12%' or T2Value like '%23%' or T2Value
like '%34%' or T2Value like '%45%' or T2Valuelike '%56%' or T2Value like '%67%' or T2Value
like '%78%' or T2Value like '%89%' or T2Value like '%90%ʻ

条件に合致するストアド関数を⽤意する →SELECT count(*) FROM Symposia.Table2
whereSymposia.Table2_MyFunction1(T2Value)=1

24* ストアド関数を使う場合、相対コストは余りあてにならない12

InterSystems Symposia 2011

SQLパフォーマンスチューニング

クエリキーワード1• SQL
SQLにキーワードを付与して、動作をコントロールすることにキーワードを付与して、動作をコントロールすることができますINSERT/UPDATE/DELETE⽤キ⽤キワ ド
INSERT/UPDATE/DELETE⽤キーワード
⽤キーワード• INSERT/UPDATE/DELETE– %NOLOCK• データ追加・更新時にレコードのロックを実⾏しない。レコードの競合が発⽣しないことが分かっている場合に使⽤可能。例：深夜のオンラインユーザ切断中のバッチ処理など– %NOCHECK• 外部キーの参照整合性チェックやデータの妥当性検証を⾏わない。例：外部システムやH/Wい。例：外部システムやH/Wから出⼒されたデータの追加などから出⼒されたデータの追加など
デ– %NOINDEX• インデックスを⽣成しない。全レコード追加後にまとめて%BuildIndices()
BuildIndices()メソッド等を使って⾃分で⽣成する必要ありメソッド等を使って⾃分で⽣成する必要あり– %NOTRIGGER
25• トリガを実⾏しない。

SQLパフォーマンスチューニング

クエリキーワード2• SELECTSELECT⽤キーワード
⽤キーワード(FROM(FROM句の後につける）
句の後につける）– %IGNOREINDICES• 指定されたインデックスを使⽤しない指定されたインデ クスを使⽤しない

SELECT * FROM %IGNOREINDICES Symposia.Table6.FlagIndex
Symposia.Table6 where Flag=0 and P1 ='Aʻ
ほとんどのデータでFlag=0
ほとんどのデータでFlag=0なのでインなのでインデックスを参照しないように指定
デックスを参照しないように指定する– JOIN
JOINするテーブルの順序をコントロールするキーワー
するテーブルの順序をコントロールするキーワー
ド• %FULL%FULL、、%INORDER、%INORDER、%STARTTABLE– 各クエリ最適化オプションを実⾏しないように指定するキーワード
26• %NOFLATTEN%NOFLATTEN、、%NOMERGE、%NOMERGE、%NOSVSO%NOSVSO、、%NOTOPOPT、%NOTOPOPT、%NOUNIONOROPT

13

InterSystems Symposia 2011SQLパフォーマンス計測

パフォーマンスを計測しよう• %SYS.PTools.SQLStats– クエリの実⾏時間を測定し、データベースに保存する– 有効にすると、クエリキャッシュ内に計測⽤のコードが埋め込有効にすると クエリキャッシ 内に計測⽤のコ ドが埋め込まれる• 設定⽅法– Do $$SYSTEM.SQL.SetSQLStatsSYSTEM.SQL.SetSQLStats(n)(n)
0 : 計測⽤コード⽣成を無効にする
1 : 計測⽤コードを⽣成するが、測定は⾏わない
2 : 計測⽤コードを⽣成し、クエリの開始・終了を測定する
3 : 計測⽤コードを⽣成し、クエリの開始・終了、モジュール毎の時間も測定する

27

SQLパフォーマンス計測

測定する項⽬・結果確認• 測定する項⽬– グローバル参照数
ドの実⾏⾏数– ココードの実⾏⾏数– クエリ・モジュールの呼び出された回数– クエリ・モジュールの実⾏時間– クエリによって返された⾏数• 結果の確認– SQLStatsSQLStatsクラス内に定義されている、クラス内に定義されている、View
View
[%SYS
[%SYS_PTools.SQLStatsView[%SYS
SYS_PTools.SQLStatsView]
PTools SQLStatsView]
SQLStatsView] を使⽤するのが良い。
を使⽤するのが良い• 利⽤する可能性がある場合は$SYSTEM.SQL.SetSQLStats(1)SYSTEM.SQL.SetSQLStats(1)を設定しておくとよいを設定しておくとよい– PTools
PTools未設定状態から、有効にする場合はクエリキャッシュ未設定状態から、有効にする場合はクエリキャッシュをパージする必要あり。

2814

InterSystems Symposia 2011

SQLパフォーマンス計測

結果確認クエリ１
SELECT RoutineName,
RoutineName, ModuleName
ModuleName,, ModuleCount
ModuleCount,,
GlobalRefs,, LinesOfCode
GlobalRefsLinesOfCode,, TotalTime
TotalTime,, RowCount,RowCount,QueryType,, StartTime,
QueryTypeStartTime, QueryText
FROM %%SYS_PTools.SQLStatsView
SYS_PTools.SQLStatsView
WHERE Namespace= 'SYMPOSIA'

29

SQLパフォーマンス計測

結果確認クエリ２
SELECT RoutineName as クエリキャッシュ名,
クエリキャッシュ名,
ModuleName as モジュール名
モジュール名,, SUM(SUM(ModuleCountModuleCount)) AS
クエリ実⾏回数,, AVG(クエリ実⾏回数
AVG(TotalTime(TotalTime)) AS 平均実⾏時間,
平均実⾏時間,SUM(TotalTime
SUM(TotalTime)) AS 合計実⾏時間,
合計実⾏時間, AVG(GlobalRefs
AVG(GlobalRefs)) AS
平均グローバル参照数,, AVG(LinesOfCode
平均グローバル参照数
AVG(LinesOfCode)) AS 平均コー
ド実⾏⾏数,, QueryText as クエリテキスト
ド実⾏⾏数
FROM %%SYS_PTools.SQLStatsView
SYS_PTools.SQLStatsView
WHERE NameSpace = ʻSYMPOSIA'GROUP BY RoutineName,RoutineName, ModuleName
ORDER BY 合計実⾏時間 DESC
＊クエリ単位で集計し、合計実⾏時間でソートすることでシ＊クエリ単位で集計し、合計実⾏時間でソートすることでシステムへの影響が⼤きいクエリを洗い出すことができる。
30

15

InterSystems Symposia 2011

SQLパフォーマンス計測

SQLStatsの注意点• Select 句の場合、クエリの開始からFetch
句の場合、クエリの開始からFetchの終の終了までを計測しています。
了までを計測しています。
→クライアントでクライアントでFetch
Fetch毎に処理をしている場合毎に処理をしている場合は、その時間も含まれます。＝サーバでかかった時間を計測しているわけではありません。
• システムワイドの設定です。
• １度クエリを実⾏するごとに複１度クエリを実⾏するごとに複数回レコードの数回レコードの更新が実⾏されます。システムに負荷がかかり更新が実⾏されます。システムに負荷がかかります。
31

SQLパフォーマンス計測

クエリパラメータが必要な場合• 同じクエリのパフォーマンスに⼤きな差がある
場合は、クエリパラメータにパフォーマンスが依存していることが考えられます。
依存していることが考えられます• クエリパラメータはクエリパラメータはView
View[%SYS_PTools.Query_With_Parameters[%
SYS_PTools.Query_With_Parameters ]を参照して取得することができます（参照して取得することができます（2011.1~）
2011.1~）– 直接の参照関係はないので、上記直接の参照関係はないので、上記View
ViewののR Ti
RunTimeとRunTimeとSQLStatsView
SQLS
SQLStatsViewの
Vi のStartTime
S
StartTimeを⽐Tiを⽐較します。

3216

InterSystems Symposia 2011

SQLパフォーマンス計測

SQLベンチマークを実⾏する• 収集したクエリ情報、クエリパラメータを利⽤して、再度SQL、再度SQLを順番に実⾏することができます。
を順番に実⾏することができます。
(2011.1~)(2011 1 )##class(%SYS.PTools.SQLBenchMarkQueries##class(%
SYS.PTools.SQLBenchMarkQueries).Run(ns,1)).Run(ns,1)
実⾏結果の確認

SELECT QueryIdQueryId->QueryText
QueryText,*,* FROM%SYS_PTools.SQLBenchMarkResults
WHERE QueryId
QueryId-->NameSpaceNameSpace='SYMPOSIAʻ='SYMPOSIAʻ*クエリ毎に結果を集計してまとめているわけではないので注意。クエリ毎に⾒るには、 QueryId
QueryId-->QueryText等でのQueryText等でのGroup
Group bybyが必要。
が必要。
33

17