データベースでは、オプティマイザーは入力された各SQLクエリに対して最適な実行計画の生成を試みますが、最適な実行計画の生成には、多くの場合、リアルタイムで有効な統計情報と正確な行数推定が必要です。統計情報とは、実際にはオプティマイザー統計情報（optimizer statistics）を指し、データベース内のテーブルや列の情報を記述するデータの集合です。これは、コストモデルが最適な実行計画を選択する上で非常に重要な部分です。オプティマイザーコストモデル（optimizer cost model）は、クエリで扱われるテーブル、列、述語などのオブジェクトの統計情報に依存して、計画の選択と最適化を行います。正確で有効な統計情報は、オプティマイザーが最適な実行計画を選択するのに役立ちます。
OceanBaseは、テーブルレベル、列レベルの基本統計情報やヒストグラムなどの統計情報カテゴリをサポートしており、定期的な自動収集、手動収集、オンライン収集、統計情報の有効期限が大幅に切れた場合の自動非同期収集など、さまざまな収集戦略も提供しています。ほとんどのシステムでは、ユーザーは通常、統計情報の具体的な問題について気にする必要はありません。なぜなら、オプティマイザーは定期的にタスクを実行して、更新が必要なテーブルの統計情報を収集するからです。しかし、APシナリオでは、超大規模なテーブルや、大量更新後にリアルタイムクエリを提供するテーブルが存在する可能性があります。このような場合、デフォルトの統計情報収集戦略では統計情報の収集が間に合わず、実行計画の生成に影響を与える可能性があります。以下では、APシナリオの一部のケースにおける統計情報の収集方法について、具体的に紹介します。
統計情報の概要
統計情報の分類
統計情報は主に以下のカテゴリに分けられます：
テーブル（インデックステーブルを含む）統計情報：行数、マクロブロック数、マイクロブロック数、平均行長などが含まれ、テーブルのスキャンコストを推定するために使用されます。
列レベルの統計情報
- 列値の分布：最大値、最小値、平均列長、異なる値の数（NDV）。
- データの偏り：ヒストグラム（Histogram）を用いてデータの分布状況を記述します。
- NULL値の割合：オプティマイザーがNULL値に関連するクエリを処理するのを支援します。
OceanBaseがサポートする統計情報収集方法
自動収集：オプティマイザーの定期タスクにより、デフォルトでは毎日、テーブルの統計情報更新が必要かどうか分析されます。
手動収集：ユーザーはSQLコマンドを使用して統計情報収集をトリガーでき、超大規模なテーブルや特定のクエリ最適化に適しています。また、ユーザーが統計情報を収集する際には、収集の並列度、粒度、ヒストグラムのバケット数などの収集戦略を指定できます。
オンライン収集：バッチインポート、PDML、
CREATE TABLE ... ASなどのシナリオでは、GATHER_OPTIMIZER_STATISTICSヒントとシステム変数_optimizer_gather_stats_on_load（デフォルトで有効）を使用してオンライン統計情報収集を行えます。また、ダイレクトロード機能のAPPENDヒントを使用してオンライン統計情報収集を実現することもできます。
統計情報の更新メカニズム
しきい値による更新トリガー：テーブルデータの変化量が一定の割合（デフォルトでデータ量が10倍を超える変化）を超えると、統計情報の非同期更新がトリガーされます。
パーティションテーブルのサポート：OceanBaseはパーティションレベルの統計情報更新と管理をサポートしています。
APシナリオにおける統計情報の最適化戦略
カスタマイズされた収集戦略
- 選択的収集：APシナリオのコアクエリテーブルやキーチャーに対して、個別に収集タスクを設定します。
- パーティション優先：使用頻度が最も高い、または変化量が最も大きいパーティションを優先的に更新します。
並列度の設定
- 超大規模なテーブルの統計情報を収集する際には、収集の並列度を適切に設定します。
更新頻度の動的調整
- テーブルデータの更新パターンに基づいて、統計情報の更新頻度を柔軟に設定し、不必要なオーバーヘッドを避けます。
シナリオ例
統計情報収集ウィンドウの調整
デフォルトでは、OceanBaseオプティマイザーはメンテナンスウィンドウを用いて毎日の自動統計情報収集を行い、統計情報が反復的に更新されるよう保証します。月曜日から日曜日までのタスクのデフォルト開始時刻は22:00で、最大収集時間は4時間です。詳細は以下の表を参照してください。
メンテナンスウィンドウ名 |
開始時刻/頻度 |
最大収集時間 |
|---|---|---|
| MONDAY_WINDOW | 22:00/per week | 4 hours |
| TUESDAY_WINDOW | 22:00/per week | 4 hours |
| WEDNESDAY_WINDOW | 22:00/per week | 4 hours |
| THURSDAY_WINDOW | 22:00/per week | 4 hours |
| FRIDAY_WINDOW | 22:00/per week | 4 hours |
| SATURDAY_WINDOW | 22:00/per week | 4 hours |
| SUNDAY_WINDOW | 22:00/per week | 4 hours |
説明
- OceanBaseデータベースV4.3.5では、V4.3.5 BP1バージョン以降、メンテナンスウィンドウ
SATURDAY_WINDOWおよびSUNDAY_WINDOWのタスクのデフォルト開始時刻と最大収集時間は、デフォルト開始時刻6:00、最大収集時間20時間からデフォルト開始時刻22:00、最大収集時間4時間に変更されました。 - OceanBaseデータベースV4.2.5では、V4.2.5 BP2バージョン以降、メンテナンスウィンドウ
SATURDAY_WINDOWおよびSUNDAY_WINDOWのタスクのデフォルト開始時刻と最大収集時間は、デフォルト開始時刻6:00、最大収集時間20時間からデフォルト開始時刻22:00、最大収集時間4時間に変更されました。
業務の実際の状況に応じて、メンテナンスウィンドウを適切に設定する必要があります。例えば、メンテナンスウィンドウが業務のピーク時間帯と重なる場合は、メンテナンスウィンドウの開始時刻を調整するか、特定の日には統計情報の収集を行わないようにできます。業務環境においてテーブル数が非常に多い場合や、超大規模なテーブルが多数存在する場合は、メンテナンスウィンドウの収集時間を調整できます。
以下は設定例です。
-- 月曜日の自動統計情報収集を無効にする
CALL DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
-- 月曜日の自動統計情報収集を有効にする
CALL DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');
-- 月曜日の自動統計情報収集の開始時刻を午後8時に設定する
CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2022-09-12 20:00:00');
-- 月曜日の自動統計情報収集の持続時間を6時間に設定する
-- 6時間 <=> 6 * 60 * 60 * 1000 * 1000 <=> 21600000000 us
CALL DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW', 'JOB_ACTION', 'DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(21600000000)');
超大規模テーブルの統計情報収集戦略
超大規模なテーブルが存在するシナリオでは、オプティマイザーのデフォルトの統計情報収集戦略では、テーブルの統計情報を1回のメンテナンスウィンドウで収集しきれない可能性があります。そのため、超大規模テーブルに対しては適切な収集戦略を設定する必要があります。超大規模テーブルの統計情報収集において、時間がかかる主なポイントは以下の3つです：
- テーブルのデータ量が膨大で、収集には全表スキャンが必要となり、時間がかかる。
- ヒストグラム収集には複雑な計算が伴い、追加の時間コストが発生する。
- 大規模パーティションテーブルでは、デフォルトでサブパーティション、パーティション、全表の統計情報とヒストグラムを収集するため、コストは3 * (cost(全表スキャン) + cost(ヒストグラム))となる。
上記の時間がかかるポイントに基づき、テーブルの実際の状況や関連するクエリ状況に応じて最適化できます。推奨事項は以下の通りです：
適切なデフォルト収集並列度を設定します。なお、並列度を設定した後は、関連する自動収集タスクを業務の低負荷時間帯に実行するよう調整し、業務への影響を避ける必要があります。並列度は8以内に制御することを推奨します。設定方法は以下の通りです。
-- OracleまたはMySQLの業務テナントは同じです： CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'degree', '8');デフォルトの列のヒストグラム収集方法を設定します。データ分布が均一な列については、ヒストグラムを収集しないよう設定することを検討します。
-- OracleまたはMySQLの業務テナントは同じです。 -- 1. このテーブルのすべての列のデータ分布が均一な場合、以下の方法で全列のヒストグラム収集を無効に設定できます。 CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'method_opt', 'for all columns size 1'); -- 2. このテーブルでデータ分布が不均一な列がごく少数しかなく、それらの列のみヒストグラム収集が必要で、他の列は不要な場合、以下の方法で設定できます(c1, c2はヒストグラムを収集、c3, c4, c5は収集しない)。 CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'method_opt', 'for columns c1 size 254, c2 size 254, c3 size 1, c4 size 1, c5 size 1');デフォルトのパーティションテーブルの収集粒度を設定します。ハッシュパーティションやキーパーティションなどの一部のパーティションテーブルでは、グローバル統計情報のみを収集するか、パーティションからグローバルを推定する収集方法を設定することも検討できます。
-- OracleまたはMySQLの業務テナントは同じです。 -- 1. グローバル統計情報のみを収集するように設定します。 CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'granularity', 'GLOBAL'); -- 2. パーティションからグローバルを推定する収集方法を設定します。 CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'granularity', 'APPROX_GLOBAL AND PARTITION');大規模テーブルのサンプリング方式で統計情報を収集する設定は慎重に使用してください。大規模テーブルのサンプリング収集を設定すると、初期バージョンではヒストグラムのサンプル数も大きくなり、逆効果となる可能性があります。サンプリング方式での収集設定は、ヒストグラムを収集せずに基本統計情報のみを収集するシナリオにのみ適しています。
-- OracleまたはMySQLの業務テナントは同じです。例：granularityを削除する場合 -- 1. 全列のヒストグラム収集を無効に設定します。 CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'method_opt', 'for all columns size 1'); -- 2. サンプリング比率を10%に設定します。 CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'estimate_percent', '10');
その他、設定済みのデフォルト収集ポリシーをクリア/削除する必要がある場合は、クリアする属性{attribute}を指定するだけで、以下の方法で行えます。
-- OracleまたはMySQLの業務テナントは同じです。例：granularityを削除する場合
CALL DBMS_STATS.delete_table_prefs('database_name', 'table_name', 'granularity');
関連する収集ポリシーを設定した後、設定が成功したかどうかを確認する必要がある場合、以下の方法で確認できます。
-- OracleまたはMySQLの業務テナントは同じです。例：指定された並列度degreeを取得する場合
SELECT DBMS_STATS.GET_PREFS('degree', 'database_name','table_name') from dual;
上記の方法以外に、大規模テーブルの統計情報を手動で収集した後、関連する統計情報をロックすることも検討できます。テーブルの統計情報がロックされると、自動収集は更新されなくなります。これは、データ特性の変化が大きくなく、データ値に敏感でないシナリオに適しています。ロックされた統計情報を再収集する必要がある場合は、まずロックを解除する必要があります。
-- OracleまたはMySQLの業務テナントは同じです。テーブルの統計情報をロックする場合
CALL DBMS_STATS.LOCK_TABLE_PREFS('database_name', 'table_name');
-- OracleまたはMySQLの業務テナントと同じで、テーブルの統計情報をロック解除する場合
CALL DBMS_STATS.UNLOCK_TABLE_PREFS('database_name', 'table_name');
関連ドキュメント
統計情報の詳細および使用方法については、以下のドキュメントを参照してください：
統計情報にはテーブル統計情報（Table Level Statistics）と列統計情報（Column Level Statistics）の2種類があります。統計情報の種類の詳細については、統計情報の概要を参照してください。
OceanBaseデータベースのオプティマイザーは、手動での統計情報収集と自動での統計情報収集をサポートしています。統計情報収集の詳細および操作方法については、統計情報収集方法の概要を参照してください。
統計情報管理の詳細な操作手順については、統計情報管理の章を参照してください。
シンプルな例を通じて統計情報の使用方法を理解するには、例を見るを参照してください。