手动收集统计信息

PROCEDURE GATHER_DATABASE_STATS_JOB_PROC();

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');

# 不收集直方图的场景
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);

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 t_part 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');

# 不收集直方图的场景
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');

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');

-- 示例：验证 test.t1 是否收集成功
SELECT table_name, num_rows, last_analyzed
FROM oceanbase.DBA_TAB_STATISTICS
WHERE owner = 'test' AND table_name = 'T1' AND object_type = 'TABLE';

SELECT
  tenant_id,
  svr_ip,
  svr_port,
  session_id,
  trace_id,
  task_id,
  type,
  task_start_time,
  task_duration_time,
  task_table_count,
  completed_table_count,
  running_table_owner,
  running_table_name,
  running_table_duration_time,
  running_table_progress
FROM oceanbase.GV$OB_OPT_STAT_GATHER_MONITOR
WHERE type = 'MANUAL GATHER';

-- 查询手动收集任务的历史执行情况
SELECT
  tenant_id,
  task_id,
  type,
  status,
  table_count,
  failed_count,
  start_time,
  end_time
FROM oceanbase.DBA_OB_TASK_OPT_STAT_GATHER_HISTORY
WHERE type = 'MANUAL GATHER'
ORDER BY start_time DESC;

-- 查询手动收集过程中失败的表（若无失败则无返回）
SELECT
  owner,
  table_name,
  task_id,
  status,
  start_time,
  end_time,
  stat_refresh_failed_list,
  properties
FROM oceanbase.DBA_OB_TABLE_OPT_STAT_GATHER_HISTORY
WHERE status = 'FAILED'
ORDER BY start_time DESC;

SELECT
  table_name,
  num_rows,        -- 行数
  avg_row_len,     -- 平均行长度
  sample_size,     -- 采样大小
  last_analyzed,   -- 最后收集时间
  stale_stats,     -- 是否过期
  stattype_locked  -- 是否锁定
FROM oceanbase.DBA_TAB_STATISTICS
WHERE owner = 'test' AND table_name = 'T1' AND object_type = 'TABLE';

SELECT
  column_name,
  num_distinct,    -- 不同值数量
  num_nulls,       -- NULL 值数量
  num_buckets,     -- 桶个数
  histogram,       -- 直方图类型
  last_analyzed
FROM oceanbase.DBA_TAB_COL_STATISTICS
WHERE owner = 'test' AND table_name = 'T1';

SELECT stattype_locked
FROM oceanbase.DBA_TAB_STATISTICS
WHERE owner = 'test' AND table_name = 'BIG_TABLE' AND object_type = 'TABLE';