統計情報の手動収集

OceanBaseデータベースのオプティマイザーは、主にDBMS_STATSパッケージとANALYZEステートメントの2つの方法で統計情報を手動で収集します。

使用方法と制限

• セッションレベルでob_query_timeout変数を大きく設定することを推奨します。これは現在のセッションにのみ影響し、他の操作を妨げることはありません。
• 現在、システムでは統計情報収集時間の見積もりをサポートしていません。そのため、統計情報の収集を完了するためには、ob_query_timeoutを十分に長く設定することを推奨します。特にスキーマレベルの統計情報を収集する際に、タイムアウトによって中断されても、既に収集済みの統計情報は保持されます。これにより、タイムアウト後にすべてのデータを再収集する必要がなくなり、時間を節約できます。

DBMS_STATSパッケージを使用した統計情報の収集

OceanBaseデータベースのオプティマイザーは、 DBMS_STATS (MySQLモード)/DBMS_STATS (Oracleモード) パッケージを使用して、テーブルレベルおよびスキーマレベルの統計情報とインデックス統計情報を収集することをサポートしています。これらはそれぞれ、ストアドプロシージャ gather_table_stats、gather_schema_stats、および gather_index_stats を呼び出すことで実行されます。

PROCEDURE gather_table_stats (
  ownname            VARCHAR2,
  tabname            VARCHAR2,
  partname           VARCHAR2 DEFAULT NULL,
  estimate_percent   NUMBER DEFAULT AUTO_SAMPLE_SIZE,
  block_sample       BOOLEAN DEFAULT FALSE,
  method_opt         VARCHAR2 DEFAULT DEFAULT_METHOD_OPT,
  degree             NUMBER DEFAULT NULL,
  granularity        VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
  cascade            BOOLEAN DEFAULT NULL,
  stattab            VARCHAR2 DEFAULT NULL,
  statid             VARCHAR2 DEFAULT NULL,
  statown            VARCHAR2 DEFAULT NULL,
  no_invalidate      BOOLEAN DEFAULT FALSE,
  stattype           VARCHAR2 DEFAULT 'DATA',
  force              BOOLEAN DEFAULT FALSE
);

PROCEDURE gather_schema_stats (
  ownname            VARCHAR2,
  estimate_percent   NUMBER DEFAULT AUTO_SAMPLE_SIZE,
  block_sample       BOOLEAN DEFAULT FALSE,
  method_opt         VARCHAR2 DEFAULT DEFAULT_METHOD_OPT,
  degree             NUMBER DEFAULT NULL,
  granularity        VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
  cascade            BOOLEAN DEFAULT NULL,
  stattab            VARCHAR2 DEFAULT NULL,
  statid             VARCHAR2 DEFAULT NULL,
  statown            VARCHAR2 DEFAULT NULL,
  no_invalidate      BOOLEAN DEFAULT FALSE,
  stattype           VARCHAR2 DEFAULT 'DATA',
  force              BOOLEAN DEFAULT FALSE
);

PROCEDURE gather_index_stats (
  ownname            VARCHAR2,
  indname            VARCHAR2,
  partname           VARCHAR2 DEFAULT NULL,
  estimate_percent   NUMBER DEFAULT AUTO_SAMPLE_SIZE,
  stattab            VARCHAR2 DEFAULT NULL,
  statid             VARCHAR2 DEFAULT NULL,
  statown            VARCHAR2 DEFAULT NULL,
  degree             NUMBER DEFAULT NULL,
  granularity        VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
  no_invalidate      BOOLEAN DEFAULT FALSE,
  force              BOOLEAN DEFAULT FALSE,
  tabname            VARCHAR2  DEFAULT NULL
);

DBMS_STATS.GATHER_TABLE_STATS (
ownname             VARCHAR2,
tabname             VARCHAR2,
partname            VARCHAR2 DEFAULT NULL,
estimate_percent    NUMBER DEFAULT AUTO_SAMPLE_SIZE,
block_sample        BOOLEAN  DEFAULT FALSE,
method_opt          VARCHAR2 DEFAULT DEFAULT_METHOD_OPT,
degree              NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
granularity         VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
cascade             BOOLEAN DEFAULT NULL,
no_invalidate       BOOLEAN DEFAULT FALSE,
stattype            VARCHAR2 DEFAULT 'DATA',
force               BOOLEAN DEFAULT FALSE,
hist_est_percent    NUMBER DEFAULT AUTO_SAMPLE_SIZE,
hist_block_sample   BOOLEAN DEFAULT NULL
);

DBMS_STATS.GATHER_SCHEMA_STATS (
ownname            VARCHAR2,
estimate_percent   NUMBER DEFAULT AUTO_SAMPLE_SIZE,
block_sample       BOOLEAN DEFAULT FALSE,
method_opt         VARCHAR2 DEFAULT DEFAULT_METHOD_OPT,
degree             NUMBER DEFAULT NULL,
granularity        VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
cascade            BOOLEAN DEFAULT NULL,
no_invalidate      BOOLEAN DEFAULT FALSE,
stattype           VARCHAR2 DEFAULT 'DATA',
force              BOOLEAN DEFAULT FALSE);

DBMS_STATS.GATHER_INDEX_STATS (
ownname            VARCHAR2,
indname            VARCHAR2,
partname           VARCHAR2 DEFAULT NULL,
estimate_percent   NUMBER DEFAULT AUTO_SAMPLE_SIZE,
stattab            VARCHAR2 DEFAULT NULL,
statown            VARCHAR2 DEFAULT NULL,
degree             NUMBER DEFAULT NULL,
granularity        VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
no_invalidate      BOOLEAN DEFAULT FALSE,
force              BOOLEAN DEFAULT FALSE,
tabname            VARCHAR2 DEFAULT NULL);

詳細なパラメータの説明については、以下を参照してください：

• GATHER_TABLE_STATS (MySQLモード)/GATHER_TABLE_STATS (Oracleモード)
• GATHER_SCHEMA_STATS (MySQLモード)/GATHER_SCHEMA_STATS (Oracleモード)
• GATHER_INDEX_STATS (MySQLモード)/GATHER_INDEX_STATS (Oracleモード)

DBMS_STATS パッケージが提供する収集ポリシーは、デフォルト設定の Prefs を使用します。Prefs の設定に関する詳細は、 収集ポリシー構成パラメータ（Prefs）管理機能を参照してください。

例

DBMS_STATSパッケージによる統計情報収集の関連例は以下のとおりです：

• ユーザーuserのテーブルtbl1のグローバルレベルの統計情報を収集します。

  CALL dbms_stats.gather_table_stats('user', 'tbl1', granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE AUTO');
  

• ユーザーuserのテーブルt_part1のパーティションレベルの統計情報を収集し、並列度は64で、データ分布が不均一な列のヒストグラムのみを収集します。

  CALL dbms_stats.gather_table_stats('user', 't_part1', degree=>'64', granularity=>'PARTITION', method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  

• ユーザーuserのテーブルt_subpart1のすべての統計情報を収集し、並列度は128で、データの50％のみを収集し、すべての列のヒストグラムはオプティマイザー内部で決定されます。

  CALL dbms_stats.gather_table_stats('user', 't_subpart1', degree=>'128', estimate_percent=> '50', granularity=>'ALL', method_opt=>'FOR ALL COLUMNS SIZE AUTO');
  

テーブルレベルおよびインデックス統計情報収集ポリシーGRANULARITYの関連性

デフォルトでは、テーブル統計情報を収集する際に、そのテーブル上のすべてのインデックス統計情報も同時に収集されます。インデックステーブル上の一部の統計情報は、基準テーブルの統計情報から得られるため、行数や平均行長（対応するいくつかの列を通じて計算）などが含まれます。これにより、統計情報の取得を高速化できるだけでなく、テーブル全体のデータを再スキャンすることを回避できます。ローカルインデックスとグローバルインデックスでは、GRANULARITYに違いがあり、以下の表に示されています。

ANALYZEステートメントによる統計情報の収集

OracleモードおよびMySQLモードにおいて、OceanBaseデータベースはANALYZEステートメントを使用して統計情報を収集することをサポートしています。

analyze_stmt:
ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name_list WITH INTNUM BUCKETS

analyze_stmt:
ANALYZE TABLE table_name [use_partition] analyze_statistics_clause

analyze_stmt:
ANALYZE TABLE table_name [use_partition] analyze_statistics_clause

use_partition:
  PARTITION (parition_name [,partition_name...])
| SUBPARTITION(subpartition_name [,subpartition_name...])

analyze_statistics_clause:
  COMPUTE STATISTICS [analyze_for_clause]
| ESTIMATE STATISTICS [analyze_for_clause] [SAMPLE INTNUM {ROWS | PERCENTAGE}]

analyze_for_clause:
  FOR TABLE
| FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
| FOR COLUMNS [size_clause] column [size_clause] [,column [size_clause]...]

size_clause:
  SIZE integer
| SIZE REPEAT
| SIZE AUTO
| SIZE SKEWONLY

column:
  column_name
| (column_name [,column_name])

手動統計情報収集の実践シナリオ

パーティションテーブル以外の統計情報収集

テーブルのデータ量と列数の積が1,000万以下の場合、以下のコマンドを使用してパーティションテーブル以外の統計情報を収集することを推奨します。以下の例では、テーブル test.t1 に10列あり、データ量は100万行です。

CREATE TABLE test.t1(c1 INT, c2 INT, c3 INT, c4 INT, c5 INT, c6 INT, c7 INT, c8 INT, c9 INT, c10 INT);
CREATE TABLE t2 (c1 INT PRIMARY KEY);
INSERT INTO t2 VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
INSERT /*+APPEND*/ INTO t1 SELECT * FROM (SELECT s0.c1 c1, s1.c1 c2, s2.c1 c3, s3.c1 c4, s4.c1 c5, s5.c1 c6, s0.c1 * 10 c7, s1.c1 * 10 c8, s2.c1 * 10 c9, s3.c1 * 10 c10 FROM t2 s0, t2 s1, t2 s2, t2 s3, t2 s4, t2 s5);

# ヒストグラムを収集しない場合
CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't1', method_opt=>'for all columns size 1');

# ヒストグラムを収集し、デフォルトポリシーを使用する場合
CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't1');

テーブルのデータ量と列数の積が1,000万を超える場合、データ業務状況とシステムリソース設定に基づいて一定の並列度を設定し、統計情報の収集を高速化することを推奨します。以下の例では、テーブル test.t1 のデータ量が1,000万行に増加したため、8つの並列度を使用します。

# ヒストグラムを収集しない場合
CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't1', degree=>8, method_opt=>'for all columns size 1');

# ヒストグラムを収集し、デフォルトポリシーを使用する場合
CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't1', degree=>8);

パーティションテーブルの統計情報収集

パーティションテーブルと非パーティションテーブルを比較すると、パーティションテーブルの統計情報収集戦略では、パーティションごとの統計情報収集を考慮する必要があります。システムリソースに余裕がある場合、パーティションテーブルの統計情報収集戦略として、非パーティションテーブルの並列度をさらに2倍に増やすことを推奨します。

以下の例では、同一のシナリオにおいて、test.t_part テーブルは10列あり、データ量は100万行です。パーティションごとの統計情報収集が追加されたため、並列度は2に増加しました。

CREATE TABLE t_part(c1 INT, c2 INT, c3 INT, c4 INT, c5 INT, c6 INT, c7 INT, c8 INT, c9 INT, c10 INT) PARTITION BY HASH(c1) PARTITIONS 128;
CREATE TABLE t2 (c1 INT PRIMARY KEY);
INSERT INTO t2 VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
INSERT /*+APPEND*/ INTO t1 SELECT * FROM (SELECT s0.c1 c1, s1.c1 c2, s2.c1 c3, s3.c1 c4, s4.c1 c5, s5.c1 c6, s0.c1 * 10 c7, s1.c1 * 10 c8, s2.c1 * 10 c9, s3.c1 * 10 c10 FROM t2 s0, t2 s1, t2 s2, t2 s3, t2 s4, t2 s5);

パーティションテーブルに対しては、上記の並列度の増加に加えて、パーティション推導方式による収集も検討できます。これは、パーティションごとの統計情報を収集し、それに基づいてグローバルな統計情報を推導することで、収集効率を向上させる方法です。例えば、同じシナリオで並列度を増やさず、収集するパーティションの方式を調整する場合、例は以下のとおりです。

# ヒストグラムを収集しないシナリオ
CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't_part', granularity=>'APPROX_GLOBAL AND PARTITION', method_opt=>'for all columns size 1');

# ヒストグラムを収集するシナリオでデフォルトの戦略を使用する
CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't_part', granularity=>'APPROX_GLOBAL AND PARTITION');

パーティションテーブルの統計情報の収集と更新戦略に関する詳細については、パーティションテーブルの統計情報の収集と更新戦略を参照してください。

スキーマ（データベース）レベルの統計情報収集

OceanBaseデータベースのDBMS_STATSシステムパッケージは、単一テーブルに対する手動の統計情報収集に加えて、ユーザー下のすべてのテーブルに対する統計情報収集も提供します。

特定のユーザー下のすべてのテーブルの統計情報を収集するのは時間がかかるため、業務のオフピーク時にこの機能を使用することを推奨します。

そのユーザー下のすべてのテーブルのデータ量が小規模な場合（データ量が100万行以下）、以下の例のようにtestユーザーの統計情報を収集するコマンドを直接使用できます。

# ヒストグラムを収集しない場合
CALL DBMS_STATS.GATHER_SCHEMA_STATS('test', method_opt=>'for all columns size 1');

# ヒストグラムを収集し、デフォルトのポリシーを使用する場合
CALL DBMS_STATS.GATHER_SCHEMA_STATS('test');

そのユーザー下に大規模なテーブルが存在する場合（行数が数千万レベル）、業務のオフピーク時に並列度を増やして収集できます。

# ヒストグラムを収集しない場合
CALL DBMS_STATS.GATHER_SCHEMA_STATS('test', degree=>'16', method_opt=>'for all columns size 1');


# ヒストグラムを収集し、デフォルトのポリシーを使用する場合
CALL DBMS_STATS.GATHER_SCHEMA_STATS('test', degree=>'16');

そのユーザー下に非常に大きなテーブルが存在する場合（行数が1億を超える）、その非常に大きなテーブルに対して並列処理を強化して個別に収集し、その後その非常に大きなテーブルの統計情報をロックしてから上記のコマンドを使用してユーザー全体の統計情報を収集します。収集完了後には、その非常に大きなテーブルの統計情報のロックを解除し、その後は増分モードで収集を続けます。例：

CALL DBMS_STATS.GATHER_TABLE_STATS('test', 'big_table', degree=>128, method_opt=>'for all columns size 1');

CALL DBMS_STATS.LOCK_TABLE_STATS('test','big_table');

CALL DBMS_STATS.GATHER_SCHEMA_STATS('TEST', degree=>'16', method_opt=>'for all columns size 1');

CALL DBMS_STATS.UNLOCK_TABLE_STATS('test','big_table');