前文では、OceanBaseデータベースオプティマイザーの統計情報と行数推定メカニズムについて説明しました。業務シナリオにおいて、どのように計画を通じて統計情報が有効であるか、または統計情報が期限切れでないかを分析するのか、そして現在の計画がどのような方法で行数を推定しているのかを計画分析するにはどうすればよいのかを解説しました。この節では、いくつか具体的な例を挙げて順次説明していきます。
プラン分析を通じて現在のプランがどのような方式で行数を推定しているかを確認する方法
まず、以下のように c1 列に基づいてHashパーティションを作成し、パーティション数を4とした t_part テーブルを作成し、10000行のデータを挿入します。
obclient [TEST]> create table t_part(c1 int, c2 int, c3 int) partition by hash(c1) partitions 4;
Query OK, 0 rows affected (0.164 sec)
obclient [TEST]> insert into t_part select mod(level,500),mod(level,1000),level from dual connect by level<=10000;
Query OK, 10000 rows affected (0.186 sec)
Records: 10000 Duplicates: 0 Warnings: 0
obclient [TEST]> commit;
Query OK, 0 rows affected (0.040 sec)
統計情報がない場合、オプティマイザーは動的サンプリング方式で行数を推定しようとします。Optimization Infoの estimation method:[DYNAMIC SAMPLING FULL] を確認すると、現在のプランが動的サンプリング方式を使用していることがわかります。set optimizer_dynamic_sampling = 0; の場合、動的サンプリングは無効になり、オプティマイザーはデフォルトの統計情報を使用します。
-- 統計情報がないため、動的サンプリングを試行
obclient [TEST]> explain extended select * from t_part where c1 > 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |PX COORDINATOR | |9960 |29434 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|9960 |20651 | |
| |2 | └─PX PARTITION ITERATOR| |9960 |907 | |
| |3 | └─TABLE FULL SCAN |T_PART |9960 |907 | |
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(T_PART.C1(0x7f5fd0c20c90), T_PART.C2(0x7f5fd0c22010), T_PART.C3(0x7f5fd0c22330))(0x7f5fd0cab130)]), filter(nil), rowset=256 |
| 1 - output([INTERNAL_FUNCTION(T_PART.C1(0x7f5fd0c20c90), T_PART.C2(0x7f5fd0c22010), T_PART.C3(0x7f5fd0c22330))(0x7f5fd0cab130)]), filter(nil), rowset=256 |
| dop=1 |
| 2 - output([T_PART.C1(0x7f5fd0c20c90)], [T_PART.C2(0x7f5fd0c22010)], [T_PART.C3(0x7f5fd0c22330)]), filter(nil), rowset=256 |
| force partition granule |
| 3 - output([T_PART.C1(0x7f5fd0c20c90)], [T_PART.C2(0x7f5fd0c22010)], [T_PART.C3(0x7f5fd0c22330)]), filter([T_PART.C1(0x7f5fd0c20c90) > 1(0x7f5fd0c21600)]), rowset=256 |
| access([T_PART.C1(0x7f5fd0c20c90)], [T_PART.C2(0x7f5fd0c22010)], [T_PART.C3(0x7f5fd0c22330)]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T_PART.__pk_increment(0x7f5fd0c22a20)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "TEST"."T_PART"@"SEL$1") |
| PARALLEL( AUTO ) |
| OPTIMIZER_FEATURES_ENABLE('4.2.4.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T_PART: |
| table_rows:10000 |
| physical_range_rows:10000 |
| logical_range_rows:10000 |
| index_back_rows:0 |
| output_rows:9960 |
| table_dop:1 |
| dop_method:Auto DOP |
| avaiable_index_name:[T_PART] |
| stats info:[version=0, is_locked=0, is_expired=0] |
| dynamic sampling level:1 |
| estimation method:[DYNAMIC SAMPLING FULL] |
| Plan Type: |
| DISTRIBUTED |
| Note: |
| Degree of Parallelisim is 1 because of Auto DOP |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
55 rows in set
obclient [TEST]> set optimizer_dynamic_sampling = 0;
Query OK, 0 rows affected (0.035 sec)
-- 統計情報がないため、動的サンプリングを使用できず、デフォルトの統計情報を使用
obclient [TEST]> explain extended select * from t_part where c1 > 1;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |16 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |16 | |
| |2 | └─PX PARTITION ITERATOR| |1 |16 | |
| |3 | └─TABLE FULL SCAN |T_PART |1 |16 | |
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(T_PART.C1(0x7f60d6420c90), T_PART.C2(0x7f60d6422010), T_PART.C3(0x7f60d6422330))(0x7f60d64ab020)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(T_PART.C1(0x7f60d6420c90), T_PART.C2(0x7f60d6422010), T_PART.C3(0x7f60d6422330))(0x7f60d64ab020)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([T_PART.C1(0x7f60d6420c90)], [T_PART.C2(0x7f60d6422010)], [T_PART.C3(0x7f60d6422330)]), filter(nil), rowset=16 |
| force partition granule |
| 3 - output([T_PART.C1(0x7f60d6420c90)], [T_PART.C2(0x7f60d6422010)], [T_PART.C3(0x7f60d6422330)]), filter([T_PART.C1(0x7f60d6420c90) > 1(0x7f60d6421600)]), rowset=16 |
| access([T_PART.C1(0x7f60d6420c90)], [T_PART.C2(0x7f60d6422010)], [T_PART.C3(0x7f60d6422330)]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T_PART.__pk_increment(0x7f60d6422a20)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "TEST"."T_PART"@"SEL$1") |
| PARALLEL( AUTO ) |
| OPTIMIZER_FEATURES_ENABLE('4.2.4.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T_PART: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:0 |
| table_dop:1 |
| dop_method:Auto DOP |
| avaiable_index_name:[T_PART] |
| stats info:[version=0, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[DEFAULT] |
| Plan Type: |
| DISTRIBUTED |
| Note: |
| Degree of Parallelisim is 1 because of Auto DOP |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
55 rows in set
次に、クエリ内の条件を変更して、パーティションカットにより1つのパーティションだけが取得されるようにします。クエリ（explain extended select * from t_part where c1 = 1）のプランは以下のとおりです。統計情報は収集されていませんが、比較的正確な行数が推定されています。同様に、Optimization Infoの estimation method:[DEFAULT, STORAGE] を確認すると、行数の推定方式がデフォルトの統計情報とストレージ層の推定であることがわかります。では、24行はどのように推定されたのでしょうか？まず、プランのQUERY RANGEが (MIN ; MAX) であることがわかります。QUERY RANGEによりストレージ層から返される行数は2400行です。また、述語条件 c1 = 1 の選択率は統計情報が利用できないため、デフォルトの統計情報に基づいて計算するしかありません。デフォルトの列の NDV は100なので、c1 = 1 の選択率の推定値は 1/100 となります。推定された行数：2400 * 1/100 = 24 となります。上記のシナリオでは、QUERY RANGEによって比較的正確な行数を得ることができますが、QUERY RANGEを抽出できない述語条件については、依然としてデフォルトの統計情報に基づいて選択率を計算しなければならないため、最終的な行数の推定結果には誤差が生じる可能性があります。
obclient [TEST]> explain extended select * from t_part where c1 = 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |TABLE FULL SCAN|T_PART|24 |104 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_PART.C1(0x7f61b0a20c90)], [T_PART.C2(0x7f61b0a22010)], [T_PART.C3(0x7f61b0a22330)]), filter([T_PART.C1(0x7f61b0a20c90) = 1(0x7f61b0a21600)]), rowset=256 |
| access([T_PART.C1(0x7f61b0a20c90)], [T_PART.C2(0x7f61b0a22010)], [T_PART.C3(0x7f61b0a22330)]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T_PART.__pk_increment(0x7f61b0a22a20)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "TEST"."T_PART"@"SEL$1") |
| PARALLEL( AUTO ) |
| OPTIMIZER_FEATURES_ENABLE('4.2.4.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T_PART: |
| table_rows:2400 |
| physical_range_rows:2400 |
| logical_range_rows:2400 |
| index_back_rows:0 |
| output_rows:23 |
| table_dop:1 |
| dop_method:Auto DOP |
| avaiable_index_name:[T_PART] |
| stats info:[version=0, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[DEFAULT, STORAGE] |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of Auto DOP |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
47 rows in set (0.038 sec)| Query Plan |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |TABLE FULL SCAN|T_PART|1 |4 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_PART.C1(0x7f5ffd620c90)], [T_PART.C2(0x7f5ffd622010)], [T_PART.C3(0x7f5ffd622330)]), filter([T_PART.C1(0x7f5ffd620c90) = 1(0x7f5ffd621600)]), rowset=16 |
| access([T_PART.C1(0x7f5ffd620c90)], [T_PART.C2(0x7f5ffd622010)], [T_PART.C3(0x7f5ffd622330)]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T_PART.__pk_increment(0x7f5ffd622a20)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "TEST"."T_PART"@"SEL$1") |
| PARALLEL( AUTO ) |
| OPTIMIZER_FEATURES_ENABLE('4.2.4.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T_PART: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:1 |
| table_dop:1 |
| dop_method:Auto DOP |
| avaiable_index_name:[T_PART] |
| stats info:[version=0, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[DEFAULT, STORAGE] |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of Auto DOP |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
47 rows in set
最後に、まず T_PART の統計情報を収集した後、再度 explain extended select * from t_part where c1 = 1 と explain extended select * from t_part where c1 > 1 のプランを照会します。その結果、explain extended select * from t_part where c1 = 1 のプランでは行数の推定が正確であることがわかります。Optimization Infoの est_method:[OPTIMIZER STATISTICS] を確認すると、現在のプランで使用されている行数の推定方式が収集された統計情報に基づいて行数を推定していることがわかります。同時に、explain extended select * from t_part where c1 > 1 のプランでも行数の推定結果が正確であることがわかります。これは、収集された統計情報に基づいて正確な述語の選択率を計算し、さらにストレージ層で推定された行数を加えることで、正確な行数の推定結果が得られるためです。
obclient [TEST]>call dbms_stats.gather_table_stats('TEST','T_PART');
Query OK, 0 rows affected (0.19 sec)
obclient [TEST]> explain extended select * from t_part where c1 > 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |PX COORDINATOR | |9980 |19368 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|9980 |13681 | |
| |2 | └─PX PARTITION ITERATOR| |9980 |908 | |
| |3 | └─TABLE FULL SCAN |T_PART |9980 |908 | |
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(T_PART.C1(0x7f603da20c90), T_PART.C2(0x7f603da22010), T_PART.C3(0x7f603da22330))(0x7f603daab0a0)]), filter(nil), rowset=256 |
| 1 - output([INTERNAL_FUNCTION(T_PART.C1(0x7f603da20c90), T_PART.C2(0x7f603da22010), T_PART.C3(0x7f603da22330))(0x7f603daab0a0)]), filter(nil), rowset=256 |
| dop=1 |
| 2 - output([T_PART.C1(0x7f603da20c90)], [T_PART.C2(0x7f603da22010)], [T_PART.C3(0x7f603da22330)]), filter(nil), rowset=256 |
| force partition granule |
| 3 - output([T_PART.C1(0x7f603da20c90)], [T_PART.C2(0x7f603da22010)], [T_PART.C3(0x7f603da22330)]), filter([T_PART.C1(0x7f603da20c90) > 1(0x7f603da21600)]), rowset=256 |
| access([T_PART.C1(0x7f603da20c90)], [T_PART.C2(0x7f603da22010)], [T_PART.C3(0x7f603da22330)]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T_PART.__pk_increment(0x7f603da22a20)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "TEST"."T_PART"@"SEL$1") |
| PARALLEL( AUTO ) |
| OPTIMIZER_FEATURES_ENABLE('4.2.4.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T_PART: |
| table_rows:10000 |
| physical_range_rows:10000 |
| logical_range_rows:10000 |
| index_back_rows:0 |
| output_rows:9979 |
| table_dop:1 |
| dop_method:Auto DOP |
| avaiable_index_name:[T_PART] |
| stats info:[version=1720598262361958, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[OPTIMIZER STATISTICS] |
| Plan Type: |
| DISTRIBUTED |
| Note: |
| Degree of Parallelisim is 1 because of Auto DOP |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
55 rows in set (0.037 sec)
obclient [TEST]> explain extended select * from t_part where c1 = 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |TABLE FULL SCAN|T_PART|20 |104 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_PART.C1(0x7f5fc6e20c90)], [T_PART.C2(0x7f5fc6e22010)], [T_PART.C3(0x7f5fc6e22330)]), filter([T_PART.C1(0x7f5fc6e20c90) = 1(0x7f5fc6e21600)]), rowset=256 |
| access([T_PART.C1(0x7f5fc6e20c90)], [T_PART.C2(0x7f5fc6e22010)], [T_PART.C3(0x7f5fc6e22330)]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T_PART.__pk_increment(0x7f5fc6e22a20)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "TEST"."T_PART"@"SEL$1") |
| PARALLEL( AUTO ) |
| OPTIMIZER_FEATURES_ENABLE('4.2.4.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T_PART: |
| table_rows:2400 |
| physical_range_rows:2400 |
| logical_range_rows:2400 |
| index_back_rows:0 |
| output_rows:20 |
| table_dop:1 |
| dop_method:Auto DOP |
| avaiable_index_name:[T_PART] |
| stats info:[version=1720598262361958, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[OPTIMIZER STATISTICS, STORAGE] |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of Auto DOP |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
47 rows in set
もちろん、上記は理解を容易にするためにいくつかの簡単な例を挙げただけです。実際のビジネスシナリオでは、プランが最適でない場合、既存のプランを分析して現在の行数の推定に問題がないかどうかを確認できます。行数の推定に問題がある場合は、上記の例を参考に統計情報に問題がないかどうかを確認できます。統計情報に問題がある場合は、統計情報を再収集してからプランに変化があるかどうかを確認することができます。このセクションでは、OceanBaseデータベースのオプティマイザーにおける統計情報と行数の推定の応用について、あくまでも一例として紹介しています。実際のビジネスシナリオでは、プランはより複雑になり、複雑な述語の選択率の計算もより複雑になるため、実際のアプリケーションシナリオに応じて具体的に分析する必要があります。
システム変数の設定による推定行の制御
実行計画の選択精度を向上させるためには、オプティマイザーによる基数推定が重要な役割を果たします。しかし、不正確な基数推定は実行計画に偏りを生じさせる可能性があります。基数推定は通常、述語独立仮説や結合包含仮説など、一連の仮定に依存しています。
実際のアプリケーションでは、ユーザーのデータモデルがこれらの仮定と完全に一致しないことが多く、その結果、オプティマイザーが大きな偏りを持つ推定結果を生成し、理想的でない実行計画を選択する可能性があります。そのため、オプティマイザーはシステム変数cardinality_estimation_modelを設定することで一部の仮定を制御し、複数の基数推定結果を生成することで、実行計画の選択を最適化する必要があります。