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
日本語 (日本)

最適化ルーチンの統計およびデモグラフィックの機能

最適化ルーチンはいくつかの異なる目的のために統計およびデモグラフィックを使用します。全統計または全AMPサンプル統計がない場合、問合わせ最適化では、テーブル カーディナリティの外挿(古い統計を置き換えるための外挿の使用を参照)、オブジェクト使用カウント(オブジェクト使用カウントとUDIカウントを参照)、または動的AMPサンプル見積もりを利用する必要があります。この場合、COLLECT STATISTICSリクエストで収集されるすべての統計が収集されるわけではありません。

統計およびデモグラフィックは最適化ルーチンに情報を提供し、ルーチンはその情報を使用してもっともコストのかからないアクセス計画および結合計画を作成できるような方法で問合わせを再定式化します。統計を収集するかどうかを決める際に判断しなければならない重要な事柄とは、不正確な統計に対して問合わせ最適化が実行可能かどうかということではなく、次の相対立する2つの質問です。
  • 最善の問合わせ計画を生成するには、利用可能な統計がどれだけ正確でなければならないか。
  • 問合わせ計画がどの程度まで貧弱であっても受け入れるつもりか。

常にテーブルについての正確な統計データとデモグラフィック データを入手しておくことは、非常に重要です。

例えば、テーブルの統計が古い場合、最適化ルーチンはある処理ステップ後のカーディナリティを、そのステップでの問合わせが実際には15行のみを返すときでも、10,000行と見積もることがあります。問合わせ計画の残りのステップは、ステップ1の結果をもたらした誤った見積もりによりすべて間違ったものになるため、そのリクエストのパフォーマンスは最適なものにはなりません。

最適化ルーチンは、これまでヒストグラム統計によって明らかにされてきた数多くの問題を2つの方法で回避します。
  • オブジェクト使用カウントとUDIカウントを収集することによって(オブジェクト使用カウントとUDIカウントを参照)
  • 動的AMPサンプル統計、PARTITION統計、またはその両方を、間隔ヒストグラムの属性として保存することによって。PARTITION統計は、他の統計よりもすばやく収集でき、行パーティション テーブル、列パーティション テーブル、および非パーティション テーブルに対して収集できます。

    テーブルの動的AMP統計は常に同じAMPのセットから収集されるため(動的AMPサンプリングを参照)、テーブル サイズの増加を高精度に検出するために使用することもできます(外挿カーディナリティ見積もりによるテーブルの拡大の評価を参照)。

統計およびデモグラフィックの目的

次のリストは、列およびインデックス統計とデモグラフィックの最も重要な目的についての非常に高度な説明です。
  • 最適化ルーチンは統計およびデモグラフィックを使用して、フル テーブル スキャンを実行せずに、インデックスを使用する問合わせ計画を生成する必要があるかどうかを判断します。
  • 最適化ルーチンは統計およびデモグラフィックを使用し、リクエストが指定した適合条件に基づいて中間スプールのカーディナリティを見積もります。

    中間結果の見積もりカーディナリティは、テーブルの最適結合順序とその結合を作成する際に使用される結合方式の種類の両方を決定するために非常に重要です。

    例として、2つのテーブルまたはスプールを再配置してからマージ結合するのか、またはそれら2つのテーブルまたはスプールの一方が複製されてから他方と積結合するのかという違いです。統計がどの程度正確であるかに応じて、生成された結合計画は大きく異なるため、同じ問合わせでもある結合計画を使用すると1分もかからないものであっても、別のものを使用すると終わるまでに何時間もかかってしまうという場合もあります。

  • PARTITIONシステム派生列で収集された統計によって、最適化ルーチンはより適切にコストとカーディナリティを見積もることができます。これは非パーティション テーブルと、列パーティション テーブルにも当てはまります。

    述部がPARTITION列に基づいている場合、Teradata Databaseは見積もりにPARTITION統計も使用します。例えば、WHERE PARTITION IN (3,4)など。

    システムは通常SUMMARY統計を非常に速く収集できるため、フル テーブルのこれらの統計は、ETLジョブが完了した後にTeradata Databaseがそれらを更新するときに収集するべきです。また、ETLジョブが完了した後、またはALTER TABLEリクエストを使用してパーティション式を変更したときは、行パーティション テーブルのPARTITION統計も再収集する必要があります。

    これは、できるだけ最新の統計を維持すべきもう1つの理由です。

Viewpoint統計マネージャの詳細については、<Teradata® Viewpointユーザー ガイド、B035-2206>を参照してください。

場合によっては、動的全AMP(サンプル統計を参照)または動的単一AMPサンプル統計(動的AMPサンプリングを参照)は、最適化ルーチンが最適な(または適した)結合計画を生成するには正確性が不足することもあります。しかし、テーブルの行の少数の部分母集団をサンプリングして収集した統計が、フル テーブル スキャンを使用して収集した統計と同じくらい良いという場合も多くあります。

フル テーブル統計を収集する価値は、最適化ルーチンができるだけ良い計画コスト見積もりを作成できるよう、集められるもっとも正確な情報を提供することにあります。ほとんどの場合、最適化ルーチンで派生統計を作成して伝搬することにより、古い統計情報以外のすべての統計のバランスを取ることができます(派生統計を参照)。

統計上の正確さは基本的にどの計画にも重要です。と言うのは、不正確な統計から生成された、最適状態に及ばないアクセスおよび結合計画(複雑な問合わせの最適化の場合は少なくないかもしれません)の影響は、倍数的に増加するからです。

フル テーブル統計を使用して生成された計画は、何らかの形のサンプル統計を使用して生成された計画と少なくとも同じくらい良いことが予想されます。更に、フル テーブル統計に基づいた計画のほうが、何らかの形のサンプル統計に基づいた計画よりも良い、場合によってはかなり良いと言ってもよいでしょう。

統計の自動収集と自動再収集

COLLECT STATISTICS文のTHRESHOLDオプションを使用すれば、統計を再収集するための定期再収集しきい値を設定できます。重要なことは、設定されたしきい値が満たされなかった場合は、既存の統計が古くなっていないと判断されるため、Teradata Databaseは明示的なCOLLECT STATISTICSリクエストが発行された場合でも統計を再収集しないということです。Teradata Databaseが統計の自動再収集を管理するために使用するメタデータは、TDSTATSデータベース内の複数のテーブルに保存されています。

一部の機能はCOLLECT STATISTICS文と組み合わされ、収集された統計を管理します。これらの機能には、BEGIN QUERY LOGGING文とREPLACE QUERY LOGGING文のSTATSUSAGEオプションとUSECOUNTオプションと、データベース オブジェクトを分析して、システムにより統計管理を改善できる状況を特定するための複数のAPIが含まれます。これらのAPIのマニュアルについては、<Teradata Vantage™ - アプリケーション プログラミング リファレンス、B035-1090>を参照してください。

フル テーブル統計とサンプリング統計の収集の相対的メリット

他とは切り離して考えた場合、フル テーブル統計と全AMPサンプリング統計のどちらにするかを決めるのは簡単です。通常は、フル テーブル統計を収集します。それは、そのほうが最適な問合わせ計画を作成するのに良いからです。

フル テーブル スキャンから収集された統計は定義域全体の正確な表現であるのに対し、全AMPサンプルは定義域の小さなサンプルに基づいて統計を見積もります。さらに動的AMPサンプルでは、定義域がより小規模で、カーディナリティやスキュー(ひずみ)の影響を受けやすいサンプルに基づいて、より少ない統計を見積もります。

残念ながら、実働環境でこのように決定することはあまり簡単ではありません。統計を収集するために要する時間の長さやフル テーブル統計の収集により発生するリソース消費負担を含めた、それ以外の要素も考慮しなければなりません。

オブジェクト使用カウントとUDIカウントを収集することによって最適な妥協案が得られます(オブジェクト使用カウントとUDIカウントを参照)。最適化ルーチンは、最新のINSERTカウントを収集済みの残余統計から得られた最新のカーディナリティ見積もりに加算し、そこから最新のDELETEカウントを減算することによって、最新のテーブル カーディナリティの非常に正確な見積もりを得ることができます。

ワークロードの大きい問合わせを複数実行する実働環境で、問題となるのはマルチレベルの最適化です。

レベル 最適化のタイプ 考慮事項
最低 問合わせ フル テーブル統計を収集したら問合わせの実行が速くなるなら、より正確さの劣る統計サンプルを集めるどんな理由があるか?
中程度 ワークロード フル テーブル統計を収集するとシステムの実行が遅くなる場合は、適度に正確であり、適度に良い問合わせ計画を生成するテーブル集団の統計サンプルを集めた方がよいのではないか?
トップ 組み合わせ 問合わせおよびワークロード最適化をどのように組み合わせれば、全体的なシステム パフォーマンスが最も良くなるか?

次のテーブルは、統計を収集する3つの方法のさまざまな特性を比較し、それぞれの最も生産的な利用方法について説明しています。

メソッド 特性 最良の使用法
完全統計
  • データについてのすべての統計を収集する。
  • 時間がかかる。
  • 3種類の統計収集方式で一番正確である。
  • データ ディクショナリの間隔ヒストグラムに格納する。
  • データ値に特にばらつきがあるインデックスや列に対して最良の選択である。
  • AMP当たり1,000未満の行を保持するテーブルに推奨。
  • 固有値が中程度から少数ある選択列に対して推奨する。
  • NUSIに対する収集時間は非常に高速であるため、ほとんどのNUSI(非固有セカンダリ インデックス)、PARTITION列、およびその他の選択列に推奨。
  • 全統計が値を追加し、サンプリングが満足できる統計の見積もりを提供しないすべての列セットまたはインデックスに推奨。
サンプル統計
  • データについての全統計を収集するが、テーブル内の全行にアクセスする方法ではない。
  • 完全統計より収集時間が非常に高速である。
  • データ ディクショナリの間隔ヒストグラムに格納する。
  • 非常に特異な列やインデックスを受け入れ可能。つまり、固有値の個数がテーブルのカーディナリティに近づくことを意味します。
  • 固有な列、固有なインデックスおよび特に共通点の少ないインデックスや列に対して推奨する。

    サンプル統計は非常に大さなテーブル、つまり数百億の行があるテーブルに対して有効であることが経験上わかっています。

  • カーディナリティがシステム内のAMP数の1000倍より少ないテーブルには推奨されない。
動的AMPサンプル
  • COLLECT STATISTICSよりも少ない統計を見積もる。

    見積もられる統計には、全列について値ごとのカーディナリティと平均の行が含まれる。

    非固有セカンダリ インデックスについてのみ、追加統計(インデックスごとの平均行数、AMPごとのインデックスの平均サイズ、および固有値)が見積もられる。

  • 収集時間が短いため、検出できない。
  • データ ディクショナリ内の間隔ヒストグラムではなく、テーブルのファイル システム データ ブロック記述子に格納される。
  • 自動的に実行される。ユーザーからは呼び出すことができない。
  • バッチ テーブルのINSERT…DELETE操作が、テーブル カーディナリティの10パーセントのしきい値を超えると、自動的に更新される。

    カーディナリティは、INSERTまたはDELETEリクエストの更新の合計が10パーセントのしきい値を超える場合でも、それらのリクエストによっては更新されない。

  • データ ブロック記述子でキャッシュされる。
  • インデックス付きでない選択基準、または不等条件があるインデックス付き選択には使用されない。
  • ほとんどまたはまったくスキューがなく、テーブルにシステム内のAMP数よりかなり多くの行がある場合の、カーディナリティの見積もりに適している。
  • 少々のスキューがあり、テーブルにシステム内のAMP数よりかなり多くの行がある場合、NUSI列について信頼できる統計を収集する。
  • 統計を収集するかどうかをまだ決定していない列やインデックスについて一時的なフォールバック測定として有効。

    動的AMPサンプリングは、新規に考え付いたアドホック問合わせの最適化をサポートするために、その新規問合わせ計画をサポートするには、どの部分の収集された統計が必要かが理解できるまでの間、合理的な代替メカニズムを提供します。

    Teradata Databaseは、新しい完全な統計が使用できる場合でも、動的AMPサンプルからのカーディナリティ見積もり情報を、テーブルのサイズ増加を見積もるための間隔ヒストグラムとして格納します。

既存の統計がまだ陳腐化していない場合に統計の再収集を回避するには、COLLECT STATISTICSリクエストに対して指定可能なTHRESHOLDオプションの1つ以上を使用する必要があります。再収集するインデックスまたは列セットに関する統計が満たす必要のあるしきい値を指定した場合は、Teradata Databaseを通して、さまざまな方法を使用して、収集した統計が古くなったかどうかを判断できます。既存の統計が新しさに関する基準を満たしている場合は、Teradata Databaseはそれらの統計を再収集するために発行されたリクエストを無視し、それらの統計が指定された基準を満たしている場合にのみ再収集を実行します。フル テーブル スキャンではなく、サンプリングを使用して統計の再収集に関する同様の基準を指定できます。最適化ルーチンは、これらの指令とオブジェクト使用カウント情報を使用して、未使用の統計を再収集または削除すべきタイミングを判断します。インデックス統計と列統計の再収集でのしきい値とサンプリング基準の使用については、オブジェクト使用カウントとUDIカウントと<Teradata Vantage™ - SQLデータ定義言語-構文規則および例、B035-1144>の「COLLECT STATISTICS(最適化ルーチン形式)」を参照してください。

NUSIサブテーブル統計に対する間隔ヒストグラム統計または動的AMPサンプルの使用

NUSIサブテーブルの間隔ヒストグラムに含まれる統計(間隔ヒストグラムを参照)が非常に古く、動的AMPサンプルの方がより正確なカーディナリティの見積もりを生成する場合があるため、最適化ルーチンは、次のルールのセットに基づいて、どちらの統計を使用するかを選択します。
  • AMP内のNUSIサブテーブルインデックス行の数がインデックス列内の固有値の数である。

    最適化ルーチンでは、単一のAMPからサンプリングする固有値のすべてが、システム内のAMPのすべてに存在すると仮定されます。

  • NUSIに間隔ヒストグラムがあり、そのヒストグラムに動的AMPサンプルが収集するよりも少ない固有値しかない場合、システムはインデックス値の個数を、動的AMPサンプルから入手した値の個数(プライマリ インデックスを除く)まで増やす。

    こうすると、NUSI統計が古いという問題に対処することができます。

  • 間隔ヒストグラムまたは動的AMPサンプルのいずれかからの値を上書きすることにより、テーブルのカーディナリティに、固有インデックスの固有値の個数を設定する。

これは、プライマリ インデックスの分布が均一である限り、基本テーブルに新しいデータがロードされるたびに、NUSIについての統計を更新する必要がないことを意味しています。

次の状況ではNUSI統計を収集または更新する必要があります。
  • プライマリ インデックスとNUSI列セット間に密接な相関があるテーブル
  • 単一テーブルの述部で頻繁に実行依頼される問合わせを含むワークロード

    最適化ルーチンは、単一テーブル カーディナリティを正確に見積もるために、間隔ヒストグラムを必要とします。

統計の収集方法を決める要素としての時間とリソースの消費

統計収集の経過時間とその際に消費されるリソースは、最適度の劣る問合わせ計画を生成する可能性のある全集合のサンプルセットから正確さの劣る統計を収集するためではなく、テーブルの全集合から統計を収集する処理を軽減するための重要な要素になります。

サンプル単一列PARTITION統計を収集することはできません。システムはそのようなリクエストの実行依頼を受け付けますが、実行はしません。代わりに、Teradata Databaseはサンプリング率を100パーセントに設定します。サンプル収集は、複数列PARTITION統計に対しては許可されます。

統計を収集するために要する経過時間は、以下の要素の結果により異なります。
  • 基本テーブルのカーディナリティ
  • 固有インデックス数または非インデックス列の値
  • システム構成

同じテーブルにある複数の列とインデックスから統計を収集するとしたら、その処理を完了するまでに、単一の列の統計を収集するのに比べて、2~4倍の時間がかかる可能性があります。データベース内の無数の小さなテーブルの統計を収集するのに要する時間を混合に追加するなら、それらすべてのテーブルの統計を収集するために必要な時間は驚くほど長くなるかもしれません。特に統計を取るための時間の枠がわずかしかない場合には、収集のために必要な時間が実働環境には長すぎると判断することさえありえます。

フル テーブル統計を収集することは時間がかかるだけでなく、ともすれば問合わせ処理や他のアプリケーション ワークロードに充てられるCPUリソースおよびディスクI/Oリソースを消費するため、システムにとってパフォーマンス的にも負荷になります。毎日、毎週、または毎月定期的に統計を再収集するように調整するなら、統計の収集によってシステムリソースに過剰な負荷がかかると判断するかもしれません。

これらの考慮事項をすべて調査した後、使用する実働環境では統計の再収集はすべて受け入れられない負荷であると結論する場合があるかもしれません。

統計の収集や再収集には非常に時間がかかるため、最初にテーブルの統計を収集するときには、1つ以上のサンプリング オプションとしきい値オプションのどちらか一方または両方を指定することを検討してください。

これらのオプションを使用すれば、データベース管理者に判断を仰がなくても、最適化ルーチンで統計が陳腐化するタイミングを判断できます。これらの収集オプションが指定されている場合は、最適化ルーチンで陳腐化していない統計の再収集要求が実行されないため、最適化ルーチンでリクエストの最適化に必要な統計を更新する必要がない場合はそれらの統計が不必要に再収集されないことを認識したうえで、COLLECT STATISTICSをより頻繁に発行できます。USING句のサンプリング オプションとしきい値オプションに関する詳細については、<Teradata Vantage™ - SQLデータ定義言語-構文規則および例、B035-1144>の「COLLECT STATISTICS (最適化ルーチン形式)」を参照してください。

古い統計が貧弱な問合わせ計画を生成する例

データベース オブジェクトに対して収集された統計が陳腐化しているかどうかを判断するための最適な方法は、COLLECT STATISTICS (最適化ルーチン形式)文のTHRESHOLDロジックを使用する方法です(<Teradata Vantage™ - SQLデータ定義言語-構文規則および例、B035-1144>の「COLLECT STATISTICS (最適化ルーチン形式)を参照)。

次の例は極端ですが、問題のある統計が最適化ルーチンの生成する問合わせ計画にどれほどマイナスの影響を与えるかを分かりやすく例証しています。

2つのテーブル(AとB)を考えてみます。

テーブル名 統計

は収集されたか?

統計が収集された時点でのカーディナリティ 現在のカーディナリティ
A はい 1,000 1,000,000
B いいえ 不明 75,000

あるクエリーにテーブルAとテーブルBのプロダクト ジョインが必要であり、かついずれかのテーブルがすべてのAMPに複製されていなければならない場合、最適化ルーチンはテーブルAが複製されるように選択します。それは、テーブルAについて利用可能な統計から知りうる限り、再分散しなければならないのが1,000行だけなのに対して、テーブルBからはそれより非常に多い75,000行を再分散しなければならないからです。

現実には、テーブルAの現在のカーディナリティは、その統計が収集された時点のカーディナリティより3桁大きくなっていて、1,000行ではなく100万行になっています。したがって、テーブルBの75,000行ではなくテーブルAの100万行を複製することにすると、最適化ルーチンは悪い決定をしたことになります。結果として、クエリーの実行には必要以上に長い処理時間を要します。

統計が古くなっているとみなされる環境には、一般に次の2つがあります。
  • テーブルの行数がかなり変更されている。
  • 統計を収集したテーブルのインデックスまたは列の値の範囲がかなり変更されている。

    このことは、最後に統計を収集した日時や列の特性から推察するできる場合もあります。例えば、問題の列がトランザクション日付を保管しており、その列の統計を最後に集めたのが1年前であるという場合、その列の統計が古くなっていることは確実です。

これを処理する最良の方法は、テーブルまたは単一テーブル ビューに関する統計を収集または再収集するときに1つ以上のしきい値オプションを指定することによって、統計が陳腐化するタイミングを最適化ルーチンで判断できるようにする方法です。1つ以上のしきい値オプションを使用して統計を収集または再収集する場合は、最適化ルーチンで最新の統計が陳腐化していないと判断されれば、その統計の再収集を拒否できます。

次のリクエストを使用して、テーブルの各統計に固有値の数、および統計が最後に集められた日時を入手することができます。

     HELP STATISTICS table_name;

固有インデックスの統計の場合、次の問合わせから返される行カウントを比較することによって、HELP STATISTICSから報告される値をクロス チェックできます。

     SELECT COUNT(*)
     FROM table_name;

非固有列の統計の場合、次の問合わせから返されるカウントと比較することによって、HELP STATISTICSレポートをクロス チェックできます。

     SELECT COUNT(DISTINCT columnname)
     FROM table_name;

Teradata Viewpoint統計マネージャ

統計マネージャ ポートレットでは、Teradata Database統計収集を管理することができます。これには、統計を収集および分析する機能、ジョブを作成および制御する機能、および推奨事項を管理する機能が含まれます。

統計マネージャを使用して、次のタスクを実行することができます。
  • システム上に統計を表示
  • 収集対象の統計の特定と、統計の収集スケジュールの設定
  • 欠落している統計の特定と収集
  • 古い統計の検出と更新
  • 未使用の統計の特定、および収集の中止
  • 統計が前回いつ収集されたか、およびいつ再収集するようスケジュールされているかを表示

このポートレットの使用法と機能の詳細については、<Teradata® Viewpointユーザー ガイド、B035-2206>を参照してください。