OceanBaseデータベースV4.2.0以降では、オプティマイザーの動的サンプリング機能がサポートされています。この機能はSQL実行時に必要な統計情報を収集し、オプティマイザーがより良い実行計画を生成するのを支援することで、クエリ性能を最適化します。OceanBaseデータベースのSQLクエリ文はすべて自動的に動的サンプリング機能を適用され、計画の生成をより正確にし、実行計画の実行をより効率的にします。
原理の紹介
動的サンプリングは、オプティマイザーが十分な統計情報を得るだけでなく、計画生成段階でデータベースオブジェクトに対して事前サンプリングを行い、サンプリングによって行数を推定し、コストモデルに利用することで、より良い実行計画を生成します。
OceanBaseデータベースの動的サンプリング機能には、以下のような利点があります：
- 動的サンプリングは、古い統計情報に基づいて、あるいは情報が不足している場合でもより正確な統計情報を得ることができます。
- 動的サンプリングは、複雑な述語や結合述語を含むクエリにおいても、より正確な統計情報を提供します。
- 動的サンプリングは、統計情報収集の時間とコストを削減できます。例えば、大規模なワイドテーブルでは、従来の統計情報収集が非常に時間とリソースを消費する可能性があります。
- 動的サンプリングは、クエリの即時性を向上させます。動的サンプリングは、クエリ実行時にデータの変化に適応するために実行計画を動的に調整できるためです。
SQLクエリを実行する際、OceanBaseデータベースのオプティマイザーは、最適な実行計画を選択するためにテーブルとインデックスの統計情報を収集する必要があります。統計情報が不正確または不完全である場合、使用される実行計画は最適ではない可能性があり、クエリ性能が低下します。基本的な統計情報は通常、自動収集または手動収集などの方法で取得されます。しかし、データ分布に変化が生じたり、統計情報が収集されていない場合や、いくつかの複雑なSQLクエリシナリオに遭遇した場合、統計情報は正確ではなくなる可能性があります。
以下の例で作成された2つのテーブルt1とt2は、どちらも1000行のデータを持っています。両テーブルとも統計情報が収集されていないと仮定します。
CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
CREATE TABLE t2(c1 INT, c2 INT, c3 INT);
CREATE INDEX idx_c1 ON t2(c1);
INSERT INTO t1 SELECT level,level,level FROM DUAL CONNECT BY level<=1000;
INSERT INTO t2 SELECT level,level,level FROM DUAL CONNECT BY level<=1000;
クエリSELECT * FROM t1, t2 WHERE t1.c1 = t2.c1 AND t1.c2 = 1000の実行計画は以下のとおりです：
+---------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------+
| ==================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------------------- |
| |0 |HASH JOIN | |98 |199 | |
| |1 |├─TABLE SCAN |T1 |10 |44 | |
| |2 |└─TABLE SCAN |T2 |1000 |61 | |
| ==================================================================== |
上記の2つのテーブルの結合方式はHASH JOINとなっています。しかし実際には、t1.c2 = 1000を満たすデータは1行しかありません。この場合、NESTED-LOOP JOINを選択し、結合条件t1.c1 = t2.c1を基底テーブルt2にダウンコンパイルすることで、t2テーブルもidx_c1インデックスを選択でき、全体の計画実行性能もより速くなります。以下の例のクエリ計画の実行性能はより良好になります。
+---------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------+
| ==================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------------------- |
| |0 |NESTED-LOOP JOIN | |1 |21 | |
| |1 |├─TABLE FULL SCAN |T1 |1 |2 | |
| |2 |└─DISTRIBUTED TABLE RANGE SCAN|T2(IDX_C1)|1 |18 | |
| ====================================================================
そのため、より正確な統計情報を取得し、オプティマイザーがより良い実行計画を選択するために、動的サンプリングという技術的手法を用いる必要があります。
OceanBaseデータベースのオプティマイザーの動的サンプリングの原理をよりよく理解するために、本記事では以下の具体的な例を通じて説明します。
CREATE TABLE t1(c1 INT, c2 INT, c3 INT, c4 INT);
CREATE INDEX idx_c1 ON t1(c1);
CREATE INDEX idx_c2 ON t1(c2);
Q1: SELECT c4 FROM t1 WHERE c1 > 1 AND c2 > 1 AND c3 > 1 GROUP BY c4;
Q2: SELECT /*+PARALLEL(2)*/c4 FROM t1 WHERE c1 > 1 AND c2 > 1 AND c3 > 1 GROUP BY c4;
上記の例のクエリについて、まず基底テーブルパスには以下の3つのパスが生成されます：
- 主テーブルパス
- インデックステーブルパス
idx_c1 - インデックステーブルパス
idx_c2
次に、動的サンプリングは上記の3つのパスに基づいてサンプリングSQLを構築します。
- 基底テーブルの行数推定：
count(*)。 c4列の基本統計情報：approx_count_distinct(c4)、sum(case when c4 is null then 1 else 0 end)。- すべての述語を満たす行数推定：
sum(case when c1 > 1 and c2 > 1 and c3 > 1 then 1 else 0 end)。 - インデックステーブルパス
idx_c1を満たすQuery Range行数推定：sum(case when c1 > 1 then 1 else 0 end)。 - インデックステーブルパス
idx_c2を満たすQuery Range行数推定：sum(case when c2 > 1 then 1 else 0 end)。
同時に、マイクロブロック数に基づいてサンプリング率を計算します。Q1クエリのサンプリング率をratioとすると、Q2クエリのサンプリング率は2*ratioとなります。以下の2つのサンプリングSQLを構築します。
DYNAMIC SAMPLING Q1:
SELECT
/*+ NO_REWRITE
NO_PARALLEL
DYNAMIC_SAMPLING(0)
QUERY_TIMEOUT(1000000)
*/
count(*),
approx_count_distinct("C4"),
Sum(CASE WHEN "C4" IS NULL THEN 1 ELSE 0 END),
Sum(CASE WHEN ( "C1" > 1 ) AND ( "C2" > 1 ) AND ( "C3" > 1 ) THEN 1 ELSE 0 END),
Sum(CASE WHEN ( "C1" > 1 ) THEN 1 ELSE 0 END),
Sum(CASE WHEN ( "C2" > 1 ) THEN 1 ELSE 0 END)
FROM "TEST"."T1" SAMPLE BLOCK(ratio) SEED(seed);
DYNAMIC SAMPLING Q2:
SELECT
/*+ NO_REWRITE
PARALLEL(64)
DYNAMIC_SAMPLING(0)
QUERY_TIMEOUT(1000000)
*/
count(*),
approx_count_distinct("C4"),
Sum(CASE WHEN "C4" IS NULL THEN 1 ELSE 0 END),
Sum(CASE WHEN ( "C1" > 1 ) AND ( "C2" > 1 ) AND ( "C3" > 1 ) THEN 1 ELSE 0 END),
Sum(CASE WHEN ( "C1" > 1 ) THEN 1 ELSE 0 END),
Sum(CASE WHEN ( "C2" > 1 ) THEN 1 ELSE 0 END)
FROM "TEST"."T1" SAMPLE BLOCK(2*ratio);
上記の例における専門用語の意味は以下のとおりです：
NO_REWRITE：書き換えパスを経由する必要がなく、基底テーブルスキャンに属し、サンプリングする必要はありません。NO_PARALLEL：並列処理を有効にしません。元のSQLで明示的に指定されていないため、デフォルトでは並列サンプリングは行われません。PARALLEL(x)：並列サンプリング。元のSQLで並列度が指定されている場合、サンプリングSQLも並列サンプリングを開始します。DYNAMIC_SAMPLING(0)：オプティマイザーが実行計画を生成する際に動的サンプリングの使用を禁止します。QUERY_TIMEOUT：サンプリングSQLの実行時間の上限。
ダイナミックサンプリングの適用シナリオ
現在、ダイナミックサンプリング機能はデフォルトで現在のユーザーのSQLに対して有効になっており、現在はベーステーブルのダイナミックサンプリングのみをサポートしています。ダイナミックサンプリング機能が無効にされていない場合、以下のシナリオでは計画生成段階でダイナミックサンプリングを使用しようとします：
- 統計情報がないシナリオ。
- クエリ条件に複雑な述語が含まれるシナリオ。例えば、
c1 like '%test%'のように、選択率計算式を使用して行数を推定できない場合。 - ユーザーがダイナミックサンプリングの使用を指定したシナリオ。
ダイナミックサンプリングの制御方法
現在、OceanBaseデータベースはシステム変数、クエリHint、およびシステム構成パラメータの3つの方法でダイナミックサンプリング機能を制御できます。また、ダイナミックサンプリングのサンプリングセットサイズは並列度によって制限されます。
システム変数によるダイナミックサンプリングの制御
システム変数 optimizer_dynamic_sampling は、ダイナミックサンプリング機能を有効または無効にするために使用されます。構文は以下のとおりです：
/* GLOBALレベルのダイナミックサンプリング機能を有効にする */
SET GLOBAL optimizer_dynamic_sampling = 1;
/* SESSIONレベルのダイナミックサンプリング機能を有効にする */
SET SESSION optimizer_dynamic_sampling = 1;
SET optimizer_dynamic_sampling = 1;
/* GLOBALレベルのダイナミックサンプリング機能を無効にする */
SET GLOBAL optimizer_dynamic_sampling = 0;
/* SESSIONレベルのダイナミックサンプリング機能を無効にする */
SET SESSION optimizer_dynamic_sampling = 0;
SET optimizer_dynamic_sampling = 0;
クエリHintによるダイナミックサンプリングの制御
DYNAMIC_SAMPLING Hintは、クエリがダイナミックサンプリングを使用するかどうかを制御するために使用されます。構文は以下のとおりです：
/*+DYNAMIC_SAMPLING( [[ qb_name_option ] table_name ] INTNUM1 [, INTNUM2 ] ) */
DYNAMIC_SAMPLING Hint構文のフィールドの意味は以下のとおりです：
qb_name_option：Query Blockの名前（オプション）。table_name：ダイナミックサンプリングを制御するテーブル名（オプション。空の場合は、クエリ全体でダイナミックサンプリングが使用されることを意味します）。INTNUM1：サンプリングのレベルを指定します（現在は0または1のみサポートされています。1はダイナミックサンプリングを有効にすることを意味し、0はダイナミックサンプリングを無効にすることを意味します）。INTNUM2：サンプリングするマイクロブロック数を指定します（32個以上のマイクロブロックを設定することを推奨します）。
以下の例は、クエリ時にテーブルt1に対してダイナミックサンプリング機能を使用するものです：
SELECT /*+DYNAMIC_SAMPLING(t1 1)*/ c4 FROM t1 WHERE c1 > 1 AND c2 > 1 AND c3 > 1 GROUP BY c4;
システム構成パラメータによるダイナミックサンプリングの制御
現在、ダイナミックサンプリングの最大利用可能クエリ時間は、現在のクエリ時間の1/10です。例えば、クエリのタイムアウト時間が10秒の場合、ダイナミックサンプリングの最大時間は1秒です。同時に、ダイナミックサンプリングの時間が長くなりすぎないように、OceanBaseデータベースは内部システム構成パラメータ _optimizer_ads_time_limit を使用してダイナミックサンプリングの時間上限を制御できます。デフォルトの時間上限は10秒で、値の範囲は[0, 300]です。構成パラメータの値が0の場合、ダイナミックサンプリング機能は無効になります。
以下の例は、現在のテナントのダイナミックサンプリングの時間上限を100に設定するものです：
obclient [oceanbase]> ALTER SYSTEM SET _optimizer_ads_time_limit = 100;
Query OK, 0 rows affected
使用上の注意
ダイナミックサンプリングは、オプティマイザーが十分な統計情報を得られるように、計画生成段階でデータベースオブジェクトに対して事前サンプリングを行い、サンプリングによる行数推定を行い、コストモデルに使用してより良い計画を生成します。
ダイナミックサンプリングは、オプティマイザーが統計情報を取得する手段を豊かにし、統計情報が利用できない場合や正確な行数推定ができない場合に、より良い解決策を提供します。しかし、ダイナミックサンプリング機能を使用する際には、以下の点に注意する必要があります：
- ダイナミックサンプリングはデフォルトでブロックサンプリングを使用するため、データのインポート後に適切なダンプ・コンパクション変更を行うことで、より良いサンプリング効果を得ることを推奨します。
- ダイナミックサンプリングは避けられない部分でハードパーソナライズされた解析時間の追加的なオーバーヘッドをもたらします。一部のTPシナリオのビジネスがこれを許容できない場合は、この機能を無効にすることができます。
- ダイナミックサンプリングは統計情報収集の補完的な手段に過ぎず、ビジネスシナリオではこの機能に完全に依存しないようにしてください。基本的な統計情報の収集は引き続き行う必要があります。