SQL問合せ計画の表示

コマンドの問合せ計画を表示するには、最新の問合せ計画をシステムPLAN表に格納する方法か、またはSQLコマンド・キャッシュにキャッシュされたすべてのコマンドとその問合せ計画を表示する方法のいずれかを使用します。

次の項では、この2つの方法について説明します。

システムPLAN表にある問合せ計画の表示

オプティマイザによって、問合せ計画が準備されます。実行する最後のSQL文について、計画がシステムのPLAN表に格納されるように指示することができます。

計画を生成してシステムPLAN表に格納するようにTimesTenに指示します。
文の準備で、その文に対してODBC SQLPrepare関数またはJDBC Connection.prepareStatement()メソッドをコールします。PLAN表に計画が格納されます。
SYS.PLAN表に生成された計画を読み取ります。

格納された計画は、コマンドの再準備が実行されるたびに自動的に更新されます。再準備は、次の1つ以上の状況が発生すると自動的に実行されます。

文内の表が変更された場合。
索引が作成または廃棄された場合。
アプリケーションで、統計の更新時に、ttOptUpdateStats組込みプロシージャのinvalidateオプションを使用してコマンドが無効化された場合。
ユーザーがttOptCmdCacheInvalidate組込みプロシージャを使用してコマンドを無効にした場合。

ノート:

「SQLコマンド・キャッシュ内のコマンドの無効化の制御」を参照してください。組込みプロシージャの詳細は、『Oracle TimesTen In-Memory Databaseリファレンス』のttOptUpdateStatsおよびttOptCmdCacheInvalidateを参照してください。

このような場合は、計画がどのように変更されたかをPLAN表で確認します。

計画をシステムPLAN表に格納するようにTimesTenに指示する方法

システムのPLAN表で計画を表示する前に、計画生成オプションを次のように設定して、PLAN表内でのエントリの作成を有効にします。

トランザクション・レベルのオプティマイザのヒントについては、ttOptSetFlag組込みプロシージャを呼び出してGenPlanフラグを有効にします。
文レベルのオプティマイザのヒントについては、TT_GENPLAN(1)を設定します。これはこの文でのみ有効です。文が実行された後、計画生成オプションによって、トランザクション・レベルのオプティマイザ・ヒントGenPlanの値が受け入れられます。

ノート:

文レベルとトランザクション・レベルのオプティマイザ・ヒントの詳細は、「オプティマイザ・ヒントの使用による実行計画の変更」を参照してください。

これによって、それ以降にODBC SQLPrepare関数またはJDBC Connection.prepareStatement()メソッドのコールがトランザクションにあれば、そのすべてのコールから得られた計画が現在のSYS.PLAN表に保存されるようになります。

SYS.PLAN表に格納できる計画は1つのみです。このため、ODBC SQLPrepare関数またはJDBC Connection.prepareStatement()メソッドがコールされるたびに、その時点で表に格納されていた計画は上書きされます。

コマンドの準備時に計画生成オプションが設定されていた場合は、コマンドは計画生成時にも再度コンパイルされます。したがって、別の問合せのための計画がSYS.PLAN表内にある場合でも、計画は作成されます。

ttIsqlのshowplanを使用して問合せおよびオプティマイザのヒントをテストできます。これにより計画生成が可能になるとともに、トランザクション内にある問合せ計画も示します。自動コミット・モードを無効にする必要があります。

autocommit 0;
showplan 1;

PLAN表からの問合せ計画の読取り

計画の生成を有効にしてコマンドの準備が終了すると、SYS.PLAN表の1つ以上の行にコマンドの計画が格納されます。表の行数は、コマンドがどの程度複雑かによって変わります。各行には、Oracle TimesTen In-Memory Databaseシステム表およびビュー・リファレンスのシステム表およびビューで説明されているとおり、列が7つあります。

次の例では、次の問合せを使用して問合せ計画を生成します。

Command> SELECT COUNT(*) FROM t1, t2, t3 
WHERE t3.b/t1.b > 1 
AND t2.b <> 0 
AND t1.a = -t2.a
AND t2.a = t3.a

次の表に示すとおり、オプティマイザによって次の5つのSYS.PLAN行が生成されます。各行は、計画の1つのステップで、問合せの処理時に実行される処理を示します。

ステップ	レベル	処理	表名	索引名	条件	その他の条件
1	3	TblLkRangeScan	t1	IX1	該当なし	該当なし
2	3	TblLkRangeScan	t2	IX2(D)	該当なし	t2.b <> 0
3	2	MergeJoin	該当なし	該当なし	t1.a = -t2.a	該当なし
4	2	TblLkRangeScan	t3	IX3(D)	該当なし	該当なし
5	1	MergeJoin	該当なし	該当なし	t2.a = t3.a	t3.b / t1.b > 1

SYS.PLAN表の各列の詳細は、「PLAN表の各列の説明」を参照してください。

PLAN表の各列の説明

SYS.PLAN表には、7つの列があります。

列1 (ステップ)

処理の順序を示します(常に1から開始)。この例は、次の順序で表ロック範囲スキャンを使用します。
1. 表t1のIX1で、表ロック範囲スキャンを実行します。
2. 表t2のIX2で、表ロック範囲スキャンを実行します。
3. t1とt2などでマージ結合を実行します。
列2 (レベル)

問合せ処理を表す結合ツリー図での操作の位置を示します。この例の結合ツリーは、次のとおりです。

図jointree.epsの説明
列3 (処理)

実行される処理のタイプを示します。このフィールドで使用される可能性がある値とそれぞれが表す表スキャンのタイプについては、『Oracle TimesTen In-Memory Databaseシステム表およびビュー・リファレンス』のシステム表およびビューでSYS.PLANを参照してください。

オプティマイザが実行した処理内容がすべてユーザーに表示されるわけではありません。パフォーマンスの解析にとって重要な処理のみがSYS.PLAN表に表示されます。TblLkは、シリアライズ可能分離モードまたはコミット読取り分離モードでのランタイムに使用可能なオプティマイザ・ヒントです。表ロックは、準備時に行ロックが無効になっている場合にのみスキャン時に使用されます。
列4 (表名)

スキャンされる表を示します。この列は、実行される処理がスキャンの場合にのみ使用されます。それ以外の場合、この列はNULLです。
列5 (索引名)

使用される索引を示します。この列は、処理が、既存の索引(ハッシュまたは範囲スキャン)を使用する索引スキャンの場合にのみ使用されます。それ以外の場合、この列はNULLです。スキャンが降順(小から大ではなく、大から小)の場合、範囲索引の名前に「(D)」が付きます。
列6 (条件)

処理に条件が含まれる場合に、その条件を示します。条件が使用されるのは、索引スキャンとMergeJoinの処理のみです。条件文字列は、1,024文字に制限されています。

範囲スキャンの場合、この列は条件がないことを表すNULLになる場合があります。範囲スキャンに、フィルタリングの他に次のような2つの便利な特徴があるため、オプティマイザは、表スキャンよりも範囲スキャンを優先する場合があります。
- 行が(索引キーで)ソートされて戻される。
- 行を戻す処理が高速である(特に、表のデータがまばらな場合)。
この例では、ソートに範囲スキャンを使用しています。このスキャンでは、条件は評価されません。
列7 (その他の条件)

処理の実行時に適用される他の条件を示します。これらの条件は、直接的にスキャンまたは結合で使用されることはありませんが、スキャンまたは結合によって戻された各行で評価されます。

たとえば、この例に対して生成された計画のステップ2では、範囲スキャンが表t2で実行されます。そのスキャンの実行時には、条件t2.b <> 0も評価されます。同様に、最後のマージ結合が終了すると、条件t3.b / t1.b > 1の評価が可能になります。

SQLコマンド・キャッシュに格納されているコマンドに関連付けられた問合せ計画の表示

問合せ計画情報は、問合せの監視およびトラブルシューティングに使用します。

ノート:

ttSQLCmdQueryPlan組込みプロシージャを使用するその他の理由については、「パフォーマンス管理およびトラブルシューティングのコマンド」を参照してください。

ttSQLCmdQueryPlan組込みプロシージャを使用すると、コマンド・キャッシュ内の特定の文またはすべての文の問合せ計画が表示されます。キャッシュされているSQL問合せの詳細なランタイム問合せ計画が表示されます。デフォルトでは、すべての問合せ計画が表示されますが、コマンド出力から取得したコマンドIDを指定すると、指定したコマンドの問合せ計画のみが表示されます。

ノート:

特定のコマンドの問合せ計画を表示するには、ttSQLCmdCacheInfo組込みプロシージャで表示されるコマンドIDを指定する必要があります。「SQLコマンド・キャッシュに格納されたコマンドの表示」を参照してください。

この組込みプロシージャを実行すると表示される計画データは、次のとおりです。

コマンドID
問合せテキスト(最大1024文字)
ランタイム問合せ計画での現在の処理のステップ番号
問合せ計画ツリーでの現在の処理のレベル番号
現在のステップの処理名
使用された表の名前
表の所有者
使用された索引の名前
索引条件(使用されており、利用可能な場合)
索引以外の条件(使用されており、利用可能な場合)

ノート:

「PLAN表からの問合せ計画の読取り」を参照してください。データのソースは同じでない場合がありますが、情報の位置および説明は、システムPLAN表の問合せ計画と同じです。

ttSQLCmdQueryPlan組込みプロセスには、生データ形式で問合せ計画が表示されます。また、この出力の形式化バージョンには、ttIsql explainコマンドを実行できます。「SQLコマンド・キャッシュでの文に関する問合せ計画の表示」を参照してください。

次の例では、ttSQLCmdQueryPlan組込みプロシージャを使用してSQLコマンド・キャッシュにあるすべてのSQL問合せ計画または1つのSQL問合せ計画を表示する方法を示します。

例: すべてのSQL問合せ計画の表示

コマンド・キャッシュに格納されている、コマンドに関連付けられたすべてのSQL問合せ計画を表示するには、ttIsqlユーティリティ内でttSQLCmdQueryPlan組込みプロシージャを使用します。

次の例は、引数を指定しないでttSQLCmdQueryPlan組込みプロシージャを実行した場合の出力を示しています(この場合、すべての有効な問合せの詳細なランタイム問合せ計画が表示されます)。無効な問合せの場合は、問合せ計画は表示されず、問合せテキストが表示されます。

問合せ計画は、簡潔な形式で表示されます。先頭に列見出しを表示するには、ttSQLCmdQueryPlan組込みプロシージャをコールする前にvertical onを実行してください。

ノート: 複雑な式の場合、元の式の出力に問題が生じることがあります。

Command> call ttSQLCmdQueryPlan();

< 528079360, select * from t7 where x7 is not null or exists (select 1 from t2,t3 
where not 'tuf' like 'abc'), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL> >
< 528079360, <NULL>, 0, 2, RowLkSerialScan                , T7 
, PAT                        ,                                , ,  >
< 528079360, <NULL>, 1, 3, RowLkRangeScan                 , T2 
, PAT                        , I2                             , , NOT(LIKE( tuf 
,abc ,NULL ))  >
< 528079360, <NULL>, 2, 3, RowLkRangeScan                 , T3 
, PAT                        , I2                             , ,  >
< 528079360, <NULL>, 3, 2, NestedLoop                     , 
,                                ,                                , ,  >
< 528079360, <NULL>, 4, 1, NestedLoop(Left OuterJoin)     , 
,                                ,                                , ,  >
< 528079360, <NULL>, 5, 0, Filter                         , 
,                                ,                                , , X7 >
< 527576540, call ttSQLCmdQueryPlan(527973892), <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL> >
< 527576540, <NULL>, 0, 0, Procedure Call                 , 
,                                ,                                , ,  >
< 528054656, create table t2(x2 int,y2 int, z2 int), <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 528066648, insert into t2 select * from t1, <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL> >
< 528066648, <NULL>, 0, 0, Insert                         , T2 
, PAT                        ,                                , ,  >
< 528013192, select * from t1 where exists (select * from t2 where x1=x2) or 
y1=1, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 528061248, create index i1 on t3(y3), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL> >
< 528070368, call ttOptSetOrder('t3 t4 t2 t1'), <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL> >
< 528070368, <NULL>, 0, 0, Procedure Call                 , 
,                                ,                                , ,  >
< 528018856, insert into t2 select * from t1, <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL> >
< 527573452, call ttSQLCmdCacheInfo(527973892), <NULL>, <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL> >
< 527573452, <NULL>, 0, 0, Procedure Call                 , 
,                                ,                                , ,  >
< 528123000, select * from t1 where x1 = 1 or x1 = (select x2 from t2,t3 where 
z2=t3.x3), <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 528123000, <NULL>, 0, 2, RowLkSerialScan                , T1 
, PAT                        ,                                , ,  >
< 528123000, <NULL>, 1, 6, RowLkRangeScan                 , T2 
, PAT                        , I2                             , ,  >
< 528123000, <NULL>, 2, 6, RowLkRangeScan                 , T3 
, PAT                        , I2                             , ,  Z2 = X3; >
< 528123000, <NULL>, 3, 5, NestedLoop                     , 
,                                ,                                , ,  >
< 528123000, <NULL>, 4, 4, Materialized View              , 
,                                ,                                , ,  >
< 528123000, <NULL>, 5, 3, GroupBy                        , 
,                                ,                                , ,  >
< 528123000, <NULL>, 6, 2, Filter                         , 
,                                ,                                , ,  X1 = 
colum_name; >
< 528123000, <NULL>, 7, 1, NestedLoop(Left OuterJoin)     , 
,                                ,                                , ,  >
< 528123000, <NULL>, 8, 0, Filter                         , 
,                                ,                                , ,  X1 = 1; >

例: 単一のSQL問合せ計画の表示

あるコマンドに関連付けられた問合せ計画を表示するには、ttSQLCmdQueryPlan組込みプロシージャの入力として、そのコマンドのコマンドIDを指定します。これにより、SQLコマンド・キャッシュから単一の問合せ計画が表示されます。値が指定されない場合、ttSQLCmdCacheInfo組込みプロシージャはTimesTenキャッシュの現在のすべてのコマンドに関する情報を表示します。

次の例は、コマンドID 528078576で識別されるコマンドの問合せ計画を表示します。これは、簡潔な形式で表示されます(先頭に列見出しを表示するには、ttSQLCmdQueryPlan組込みプロシージャをコールする前にvertical onを実行してください)。

ノート: 複雑な式の場合は、元の式の出力に問題が生じます。

Command> call ttSQLCmdQueryPlan( 528078576);
< 528078576, select * from t1 where 1=2 or (x1 in (select x2 from t2, t5 where y2 
in (select y3 from t3)) and y1 in (select x4 from t4)), <NULL>, <NULL>, <NULL>, 
<NULL>, <NULL>, <NULL>, <NULL>, <NULL> >
< 528078576, <NULL>, 0, 4, RowLkSerialScan                , T1 
, PAT                        ,                                , ,  >
< 528078576, <NULL>, 1, 7, RowLkRangeScan                 , T2 
, PAT                        , I2                             , ,  >
< 528078576, <NULL>, 2, 7, RowLkRangeScan                 , T5 
, PAT                        , I2                             , ,  >
< 528078576, <NULL>, 3, 6, NestedLoop                     , 
,                                ,                                , ,  >
< 528078576, <NULL>, 4, 6, RowLkRangeScan                 , T3 
, PAT                        , I1                             ,  ( (Y3=Y2; ) ) ,  >
< 528078576, <NULL>, 5, 5, NestedLoop                     , 
,                                ,                                , ,  >
< 528078576, <NULL>, 6, 4, Filter                         , 
,                                ,                                , ,  X1 = X2; >
< 528078576, <NULL>, 7, 3, NestedLoop(Left OuterJoin)     , 
,                                ,                                , ,  >
< 528078576, <NULL>, 8, 2, Filter                         , 
,                                ,                                , ,  >
< 528078576, <NULL>, 9, 2, RowLkRangeScan                 , T4 
, PAT                        , I2                             , ,  Y1 = X4; >
< 528078576, <NULL>, 10, 1, NestedLoop(Left OuterJoin)     , 
,                                ,                                , ,  >
< 528078576, <NULL>, 11, 0, Filter                         , 
,                                ,                                , ,  >
13 rows found.
Command>