動的サンプリング

Oracle

Why 動的サンプリング

  • サンプル時間は、全体のクエリ実行時間に比べて小さい。
  • 現在のデータベースの統計だけでは最適なプランが作成されないため、動的サンプリングによってパフォーマンスが向上します。
  • クエリは複数回実行される可能性があるため、最初の解析フェーズで少し遅れると全体的にかなりの節約につながります。

When 動的サンプリング

  • 以下のような場合、再帰SQLを使用してデータをサンプリングし、同様のパターンを持つSQL文の間で永続化および共有化できる動的統計を生成します。

　　- 欠落統計：欠落しているデータベース統計がある場合、動的統計がサンプリングされます。新しく作成されたオブジェクトであるか、または統計が収集される前に統計がロックされているために欠落している可能性があります。動的統計がオプティマイザに役立つはずですが、これらの統計は従来のデータベース統計と比較して低品質とみなされます。

　　- 古い統計：統計が最後に収集されてから表の10％以上の行が変更された場合、統計は古いとみなされます。失効した統計値は、表の行数の変更や、重複した値の数、上位および下位の列値などの列統計の不正確さのために、カーディナリティーの見積もりに影響する可能性があります。

　　- 不十分な統計：既存のデータベース統計は、最適な実行計画を生成するには不十分です。短期的には、動的統計は、列のグループや式の拡張統計がないことや、データスキューを特定するヒストグラムがないことを補うことができます。必要な統計がすべて存在する場合でも、複雑な述部、操作または結合のためにカーディナリティを正しく見積もることができない可能性があるため、動的サンプリングが必要な場合があります。

　　- パラレル実行：パラレル実行は、通常、長時間実行されるプロセスを高速化するために使用されます。長期実行プロセスの場合、動的統計のサンプリングに関連する時間はクエリの実行時間に比べて些細なので、実行計画が最適であることを確認するために少し時間を費やす価値があります。

　　- SQL計画ディレクティブ：1つ以上の使用可能なSQLプラン・ディレクティブが存在すると、動的統計のサンプリングがトリガーされます。 SQLプラン・ディレクティブは、オプティマイザが、前回のSQL文の実行または類似の問合せ式を使用した文からの操作または並列度（DOP）のカーディナリティでの偽装を識別すると作成されます。

設定

  • 動的統計は、OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータとDYNAMIC_SAMPLINGヒントによって制御できます。ここでは、個々の設定に関連する機能について説明します。次の例は、システム、セッション、および文レベルで動的統計を制御する方法を示しています。
ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING=11;
ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=11;

SELECT /*+ dynamic_sampling(users 11) */ * from users;

  • ほとんどの場合、デフォルト値 “2"を変更する必要はありません。これは、SQL計画ディレクティブの導入とその働きにより、Oracle 12cではもっと真実かもしれません。オプティマイザが操作の重複度または並列度（DOP）の誤った点を特定した場合、SQL計画ディレクティブを作成して短期間で動的サンプリングを強制することができます。 SQL計画ディレクティブが存在すると、DBMS_STATSが統計を収集する方法に影響し、データベース統計の問題の根本原因を修正し、SQL計画ディレクティブを作成し、動的サンプリングを不要にします。

  • 動的統計は、V$SQL_REOPTIMIZATION_HINTSビューのOPT_ESTIMATEヒ ントとしてSGAに格納できます。ビューとOPT_ESTIMATEヒントの両方が文書化されていません。動的統計の格納は、同じ文が再度解析された場合、統計の再サンプリングを行う必要がないことを意味します。

SELECT HINT_TEXT FROM V$SQL_REOPTIMIZATION_HINTS;

HINT_TEXT
---------------------------------------------------------------------------------------------------------------------------
OPT_ESTIMATE (@"SEL$2" TABLE "KOKBF$0"@"SEL$2" ROWS=26.000000 )
OPT_ESTIMATE (@"SEL$7ABE1C4F" JOIN ("S"@"SEL$2" "CO"@"SEL$2" "CU"@"SEL$2" "CX"@"SEL$2" "TS"@"SEL$2" "T"@"SEL$2" "O"@"SEL$2" "KSPPCV"@"SEL$2" "KSPPI"@"SEL$2") ROWS=10.000000 )
OPT_ESTIMATE (@"SEL$7ABE1C4F" JOIN ("CO"@"SEL$2" "CU"@"SEL$2" "CX"@"SEL$2" "TS"@"SEL$2" "T"@"SEL$2" "O"@"SEL$2" "KSPPCV"@"SEL$2" "KSPPI"@"SEL$2") ROWS=1.000000 )
OPT_ESTIMATE (@"SEL$7ABE1C4F" JOIN ("CU"@"SEL$2" "CX"@"SEL$2" "TS"@"SEL$2" "T"@"SEL$2" "O"@"SEL$2" "KSPPCV"@"SEL$2" "KSPPI"@"SEL$2") ROWS=10.000000 )
OPT_ESTIMATE (@"SEL$7ABE1C4F" JOIN ("CX"@"SEL$2" "TS"@"SEL$2" "T"@"SEL$2" "O"@"SEL$2" "KSPPCV"@"SEL$2" "KSPPI"@"SEL$2") ROWS=1.000000 )
OPT_ESTIMATE (@"SEL$7ABE1C4F" JOIN ("TS"@"SEL$2" "T"@"SEL$2" "O"@"SEL$2" "KSPPCV"@"SEL$2" "KSPPI"@"SEL$2") ROWS=10.000000 )
OPT_ESTIMATE (@"SEL$7ABE1C4F" JOIN ("T"@"SEL$2" "O"@"SEL$2" "KSPPCV"@"SEL$2" "KSPPI"@"SEL$2") ROWS=10.000000 )
OPT_ESTIMATE (@"SEL$7ABE1C4F" JOIN ("O"@"SEL$2" "KSPPCV"@"SEL$2" "KSPPI"@"SEL$2") ROWS=43.000000 )
OPT_ESTIMATE (@"SEL$2" TABLE "O"@"SEL$2" MIN=43.000000 )