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つのテーブルのJoin方式は 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データベースはシステム変数、クエリヒント、およびシステムパラメータの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;
クエリヒントによる動的サンプリングの制御
DYNAMIC_SAMPLING ヒントは、クエリが動的サンプリングを使用するかどうかを制御します。構文は以下のとおりです：
/*+DYNAMIC_SAMPLING( [[ qb_name_option ] table_name ] INTNUM1 [, INTNUM2 ] ) */
DYNAMIC_SAMPLING ヒント構文の各フィールドの意味は以下のとおりです：
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シナリオでビジネスがそれを許容できない場合は、この機能を無効にすることを選択できます。
- 動的サンプリングは統計情報収集の補完的な手段に過ぎません。ビジネスシナリオではこの機能に完全に依存せず、基本的な統計情報収集は引き続き行う必要があります。