17.00 - 17.05 - コストベースの最適化 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLリクエストおよびトランザクション処理

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
ユーザー ガイド
Publication ID
B035-1142-170K-JPN
Language
日本語 (日本)

コストベースの最適化について

最適化ルーチンは、リクエストを処理するときに、次の要素を調べて最もコストのかからないアクセス方法や、ふさわしい場合は最もコストのかからない結合パスを選択しようとします。
  • 行の選択基準
  • インデックスの参照
  • インデックスおよび列についての利用可能な統計

特定のリクエストが完了するのにかかる時間が長いほど、コストも多くかかります(すべての最適化ルーチンのコスト測定単位はミリ秒です)。

最適化ルーチンは、列とインデックスのデモグラフィックと、COLLECT STATISTICS文によって収集された統計を使用します。この情報により最適化ルーチンは、単一テーブルと結合アクセスのリレーションのカーディナリティを見積もり、テーブル内のスキューを識別できるようになります。これらの要因に関する豊富な情報を活用し、コスト見積もり機能を使用することで、最適化ルーチンはクエリーを満たすためのより良い計画を生成できます。COLLECT STATISTICS (最適化ルーチン形式)の詳細については、<Teradata Vantage™ - SQLデータ定義言語-構文規則および例、B035-1144>および<Teradata Vantage™ - SQLデータ定義言語 - 詳細トピック、B035-1184>を参照してください。

次のテーブル、table_xの一部について考えてみてください。

state serial_num
28 12345
28 23456
51 12345
51 23456

次のクエリーを行なうと、フル テーブル スキャンが実行されるとします。

SELECT *
FROM table_x
WHERE state IN (28, 51)
AND   serial_num IN (12345, 23456);

ここでリクエストを次のように修正します。

SELECT *
FROM table_x
WHERE (state=28 AND serial_num=12345)
OR (state=51 AND serial_num=23456)
OR (state=28 AND serial_num=23456)
OR (state=51 AND serial_num=12345);

意味的に同一な修正済みの問合わせにより、4つのプライマリ インデックス アクセスが行なわれます。これにより、最初の問合わせを解決するために使用されるフル テーブル スキャンのパフォーマンスが著しく向上します。

パスの選択

ANDとORの両方の演算子を含んだ式は、論理的に等しい次の形式の任意のを使用して表わすことができます。
  • (A OR B) AND (C OR D)
  • (A AND C) OR (A AND D) OR (B AND C) OR (B AND D)

最初の形式は、論理積標準形、すなわちCNFとして知られています。この形式では、オペランドのペアは、かっこ内でOR演算され、かっこで囲まれたグループの間でAND演算されます。CNFを使用する利点は、式の中の個々の条件がFALSEと評価されるとすぐに、式全体がFALSEと評価され、最適化ルーチンのそれ以降の考慮対象から外せるという点です。

2番目のものは、非論理積標準形、またはDNFと呼ばれます。これらの2つの形式を基にして、さまざまなアクセス パスを選択することができます。状況に応じて、適切な方を選択します。

A、B、CおよびDが単一テーブルの列または単一列パーティションの列を参照する場合は、問合わせに指定された形式に基づいてアクセス パスが最適化ルーチンによって生成されます。最適なパスを見つけるために、ある形式が別の形式に変換されることはありません。これに対して、A、B、CまたはDが結合条件を指定している場合には、2番目の形式が最初の形式に変換されます。

以下の式を考慮してみます。

(NUSI=A OR NUSI=B) AND (X=3 OR X=4)

この場合は、AとBの値をもつ2つのNUSIのSELECTリクエストでアクセス パスが構成されているため、CNFの方がパフォーマンスが良くなります。そのあとで、条件(X=3 OR X=4)が適用されます。DNFを使用すると、NUSIを使用した4つのSELECTリクエストが必要になります。

次の式では、(NUSI_A, NUSI_B)の組合わせでNUSIが構成されています。

(NUSI_A=1 OR NUSI_A=2) AND (NUSI_B=3 OR NUSI_B=4)

この場合には、4つのNUSIを使用した4つのSELECTでアクセス パスが構成されているため、DNFの方が適しています。CNFを使用したアクセス パスでは、全件検索が必要になります。

次のように、INを使用した単一の列の比較操作が含まれている式を考えてみましょう。

column IN (value_1, value_2, …)

この場合には、式を内部的にCNFへ変換します。

column=value_1 OR column=value_2 OR …

したがって、どちらの形式を使用しても同じアクセス パスが生成されます。

次の例のように、INリストを使用した複数列の比較操作が式に含まれているとします。

column_1 IN (value_1, value_2, …)
AND column_2 IN (value_3, …)

この場合には、最適化ルーチンは、内部でこの構文を次のような論理積標準形に変換します。

(column_1=value_1 OR column_1=value_2 OR …)
 AND (column_2=value_3 OR …)

変換された形式、つまり論理積標準形(CNF)が、2番目の形式とどのように異なっているかに注意してください。2番目の形式は、次の例で示されている通り、非論理積標準形(DNF)になっています。

(column_1=value_1 AND column_2=value_3)
OR (column_1=value_2 AND column_2=value_4)
OR …

重要なのは、意味的に同じ問合わせを異なる構文で表わすと、そのアクセス方法が違ってくる場合があるということです。

最適化ルーチンのコスト予想について

コスト予想の目的は、最適化ルーチンに正確なコストの見積もりを提供して、さまざまな操作をさまざまな方法で実行することです。そうすることで、最適化ルーチンはコスト ベースの決定を下すことができます。

操作のコストは、サブシステムが無負荷のシステム上で操作を実行する際に必要とするサービス時間です。最適化ルーチンは、CPU、I/O、およびBYNETコンポーネント サービス時間の見積もりに基づいて、操作の実行方法を比較し、総コストが最も低い方法を選択します。予想コスト値を増やすことによって、特定のアプローチを除外するか、その可能性を大きく減らす必要があるような状況があるため、経験則的コスト コンポーネントも、さまざまなサブシステム コスト コンポーネントの集合に含まれます。

全体のコストは、サブシステム コストの一次結合であり、最適化ルーチンが利用可能なさまざまな方法を比較する際に使用する値です。コスト予想公式と方法では、個々のサブシステム レベルの値が保持されます。これは、CPU、I/O、BYNET、および経験則的コンポーネント値によって計算される所定の全体コストを確認するためです。この情報を使用して、I/O集約アプローチとCPU集約アプローチを区別することができます。こうすることにより、予想されたコスト値がひどく不正確である場合に、アナリストがエラーの原因を明確に特定することができます。

SQL INSERT EXPLAINリクエストまたはTeradata Index Wizardのいずれかを使って、クエリー計画の個別のステップのサブシステム コスト値を取得し、次の列セットで、クエリー キャプチャ データベース テーブルのQueryStepsに、適切な情報を提供することができます。
  • EstCPUCost
  • EstIOCost
  • EstNetworkCost
  • EstHRCost

詳細については、問合わせ獲得機能および<Teradata Vantage™ - SQLデータ定義言語-構文規則および例、B035-1144>の「INSERT EXPLAIN」を参照してください。

問合わせログが有効な場合、システムは、次の列セットにあるDBQLStepTbl内の同じ情報を取得します。
  • EstCPUCost
  • EstIOCost
  • EstNetCost
  • EstHRCost

詳細については、<Teradata Vantage™ - データベースの管理、B035-1093>および<Teradata Vantage™ - SQLデータ定義言語-構文規則および例、B035-1144>の「BEGIN QUERY LOGGING」を参照してください。

対応する全体コスト値は、QCD QueryStepsテーブルのコスト列と、DBQL DBQLStepTblテーブルのEstProcTime列でそれぞれ取得されます。

コストを予想する場合、2つの入力データ カテゴリがあります。1つ目は、実行時環境を特徴付ける値で、2つ目は、最適化されるリクエスト内に含まれるデータベース エンティティを特徴付ける値です。実行時環境にはハードウェア プラットフォーム、オペレーティング システム、I/Oサブシステム、およびデータベース アルゴリズムが含まれています。これらは、基本的にTeradataデータベース システムのすべての製品セット要素です。I/Oスループット レート、CPU速度、BYNET能力、およびコード パスの長さすべてが、実行時環境パフォーマンスの要素の例です。CPUとI/Oの両方のコスト見積もりは、最適化対象のリクエストを通してアクセスされるデータを含むデータ ブロックのサイズによって異なります。

問合わせ計画中、最適化ルーチンは、統計、動的AMPサンプル、およびデータベース ソースから導出されるさまざまな見積もりを含む、さまざまな種類のメタデータを使用して、データベース エンティティの特徴を見積もります。

最適化ルーチンのコスト プロファイル

コスト プロファイルは、Teradata Databaseが最適化ルーチンの原価計算係数、特定の機能の有効化と無効化、およびその他のシステム設定に使用するステム固有の値(コスト プロファイル定数)のセットです。コスト プロファイルは、システム全体に適用したり、プロファイルに関連付けてそのプロファイルが割り当てられているユーザーにのみ適用したりできます。

次のテーブルは、最適化ルーチンのコスト プロファイル定数とその実行内容の一部をまとめたものです。

コスト プロファイルの設定および変更を行なえるのは、Teradataサポート センターの担当者のみです。
コスト プロファイル定数 説明
COLLECT STATISTICSコスト プロファイル定数
StatsDefaultTimeThreshold 統計再収集用のデフォルト時間しきい値を日単位で定義します。データベースは、統計が最後に収集されてからこの日数が経過するまで統計を再収集しません。

この設定は、COLLECT STATISTICSリクエストでUSING THRESHOLD DAYSが指定されていない場合にのみ適用されます。

StatsDefaultUserChangeThreshold 統計再収集用のユーザー決定デフォルト データ変更パーセンテージしきい値を定義します。データベースは、統計が最後に収集されてからこのパーセンテージだけデータ デモグラフィックが変化するまで統計を再収集しません。

この設定は、COLLECT STATISTICSリクエストでUSING THRESHOLD PERCENTが指定されていない場合にのみ適用されます。

StatsSysChangeThresholdOption 統計再収集用のシステム決定デフォルト変更しきい値オプションを定義します。データベースは、統計が最後に収集されてからこのパーセンテージだけデータ デモグラフィックが変化するまで統計を再収集しません。

この設定は、DefaultUserChangeThresholdが無効になっており、COLLECT STATISTICSリクエストでUSING THRESHOLD PERCENTが指定されていない場合にのみ適用されます。

StatsSysSampleOption 統計再収集用のシステム決定デフォルト サンプリング オプションを定義します。

この設定は、COLLECT STATISTICSリクエストでUSING SAMPLEオプションが指定されていない場合にのみ適用されます。

パーティション化コスト プロファイル定数
PPICacheThrP 複数のインデックス パーティション化を伴うディスク読み取り操作に使用されるメモリの最大量を指定します。
パラメータ化された値ピーク処理実行時のコスト プロファイル定数
TacticalResp1 問合わせを戦術的リクエストまたは意志決定支援リクエストとして扱うかどうかを判断するAMP当たりの実行時CPUコスト。

値はAMPでの問合わせ実行にかかるCPU時間から導出されます。

定義により、AMP当たりのCPU時間が1秒以下であるリクエストは戦術的であると見なされ、AMP当たりのCPU時間が1秒であるリクエストは意思決定支援リクエストと見なされます。

TacticalResp2 問合わせが高優先度リクエストとして実行依頼されるとき、問合わせを戦術的または意志決定支援問合わせとして扱うかどうかを判断するAMP当たりの実行時CPUコスト。

値はAMPでの問合わせ実行にかかるCPU時間から導出されます。

HighParsingPTThreshold 実行時CPUコストと比較された構文解析コストの比率のしきい値。これを元に、リクエストの構文解析コストが高いかどうかについて判断が行なわれます。
HighParsingRTThreshold 構文解析コストが高い問合わせについて実行時のメリットを判断するための乗算係数のしきい値。

構文解析コストが高いリクエストがある場合、特定計画と汎用計画の実行時CPU時間には、特定のCPU構文解析時間に少なくともこの値を乗じた分だけ差があることになります。

LowParsingPTThreshold 構文解析コストが低い問合わせについて実行時のメリットを判断するための乗算係数のしきい値。

構文解析コストが低いリクエストがある場合、特定計画と汎用計画の実行時CPU時間には、特定のCPU構文解析時間に少なくともこの値を乗じた分だけ差があることになります。

UseHiPriority キャッシュ アルゴリズムで高優先度ベースの判断を有効または無効にするフラグ。
ElapsedTimeThreshold 特定計画の実行の経過時間(構文解析時間と実行時間の合計)が同等の汎用計画の経過時間を超えることを示す乗算係数のしきい値。
EstimateCostFilter 特定計画の見積もりコストが、同等の汎用計画の見積もりコストよりも良いことを示す係数のしきい値。

見積もりコストの比較に使用されます。

CompareEstimates リクエストに対して汎用計画と特定計画のどちらを使用するかを判断するために、見積もりに基づく比較を有効または無効にするコスト プロファイル フラグ。

統計およびコストの見積もり

統計を利用できる場合は、最適化ルーチンのコストの見積もりに次の特性が適用されます。
  • 最適化ルーチンは、デモグラフィック情報に基づいてアクセス選択性を評価します。

    統計がテーブルのプライマリ インデックスで最近収集されている場合、最適化ルーチンでカーディナリティを正確に見積もることができる可能性が高くなります。統計が古い場合、派生統計のフレームワークを使用して、その問題を最小限に抑える必要があります。

  • 最適化ルーチンは、プライマリ インデックス統計を使用して、テーブルのカーディナリティを見積もります。

    したがって、アクセス計画および結合計画を最適なものにするために、これらの統計を最新のものに保っておく必要があります。行の総数の10%を上回る行が追加または削除された場合には、新しい統計を収集してください。

使用できる統計がない場合、あるいはテーブルにプライマリ インデックスがない場合、最適化ルーチンは動的に選択したAMPから取り出した情報に基づいて、テーブル内の合計行数を見積もります(動的AMPサンプリングを参照)。

これは、次のような理由のため、テーブルのカーディナリティの正確な見積もりではない可能性があります。
  • テーブルが比較的小さく、AMP全体に不均等に分散されている。
  • あるテーブルに選択されたNUPIが原因で、そのテーブルの行がAMP全域に不均等に分散されている。

上記のいずれかの特性が真である場合は、平均から大きくかけ離れた行数をもつAMPが存在している可能性があります。

行数を見積もるときに、そのようなAMPが選択された場合には、最適化ルーチンが間違った見積もりを行なうので、もっとも効率的な処理を行なうための最適な方法(結合、アクセス パス、その他)を選択できないこともあります。

古い統計が使用されると、最適化ルーチンは、動的に取り出されたAMPから得た見積もり情報に基づいて作成される結合計画よりも、さらに悪い結合計画を作成する場合があります。

唯一異なっているのは、古い統計に基づく計画は常に間違っていますが、動的AMPサンプルに基づく計画はある程度正確な統計的見積もりを提供する可能性があるという点です。

インデックスによるアクセスを行なわせる

リクエストを処理する際に必ずインデックスが使用されるようにするには、インデックス付きの列の値に、問合わせのWHERE句で制約を指定します。

このようなWHERE制約に複数のNUSIが適用される場合で、対象となるテーブルが非常に大きい場合には、ビット マッピングによって、もっとも効率的な検索が行なわれます。テーブルが小さい場合には、最適化ルーチンは、選択性がもっとも高い(インデックス値あたりの行がもっとも少ない)と見積もられたインデックスを選択します。

NUSI列の統計情報を利用できない場合には、最適化ルーチンは、インデックス値が均等に分散されていると仮定します。最適化ルーチンは、テーブルIDに基づいて動的に1つのAMPを選択し、そのAMP上の対象になるテーブルの行の総数をそのAMP上の固有のインデックス値の数で割ることで、インデックス値あたりの行数を見積もります。インデックス値が均等に分散されていない場合は、その見積もりは正しくないため、アクセス パフォーマンスが低下する原因となります。

インデックスによるアクセスのための指針

EXPLAINリクエスト修飾子またはクライアントVisual Explainユーティリティを常に使用して、最適化ルーチンがクエリーを実行するために生成する計画を確認するようにしてください。

最適化ルーチンは、次の指針に従って、テーブル列に対するインデックスによるアクセスを行ないます。
  • NoPIテーブルの場合、列アクセスはセカンダリ インデックスまたは結合インデックスを使用して行なわれます。当該の列にセカンダリ インデックスまたは結合インデックスが定義されていない場合、アクセスは常にフル テーブル スキャンを使用して行なわれます。
  • テーブル データにもっとも速くアクセスする場合は、UPIを使用する。
  • 等式の制約を含んだリクエストを処理する場合にのみUPIを使用する。
  • 次の処理が可能なときには、結合処理で最大の性能が得られる。
    • あるテーブルのUPI値を別のテーブルの固有インデックス値(UPIまたはUSI)と照合する。
    • 結合のための等式またはIN条件にプライマリ インデックスのみを使用する。
  • 行のソートや再分散を避けるために、単一AMP内で行選択または結合処理を行なうためにはNUPIを使用する。
  • テーブルの統計情報が利用できない場合には、単一AMPの情報に基づいて、インデックスを使用するときのコストを見積もる。この見積もりでは、インデックス値が均等に分散されているものと仮定しています。分散が均等になっていない場合には、性能が低下します。
  • インデックスを構成するすべての列に対して等式の制約を使用するリクエストを処理する場合にのみ、複合インデックスを使用する。

    同じテーブルの複数列インデックスの一部でもある単一の列にインデックスを定義できることに注意してください。

  • 等式または範囲指定の制約に複数の非固有セカンダリ インデックスが含まれていて、それを大さなテーブルに対して使用する場合にのみ、ビット マッピングを使用する。
  • 大さなテーブルの再分散およびソートを避けるために、入れ子結合では、非固有インデックス列またはUSI列を使用する。

    例えば、次の条件があるとします。

    table_1.column_1 = 1 AND table_1.column_2 = table_2.nusi

    table_1が複製されてtable_1.nusiと結合される入れ子結合を使用しない場合には、table_1とtable_2の両方を、マージ結合の前にソートおよび再分散する必要が生じる場合があります。この結合でtable_2が大きい場合、パフォーマンスは高くありません。