简介：
数据库统计信息是数据库自动收集的“数据指纹”，记录表的大小、字段值的分布规律（如重复率、最大值/最小值）等关键指标。这些信息如同导航系统的实时交通数据，帮助查询优化器快速判断最佳执行路径。例如，当统计信息显示某字段90%的值重复时，优化器会放弃低效的索引扫描，转而选择全表扫描。通过动态更新统计信息（如每日或数据变化时），数据库能持续适应业务数据的波动，避免因数据量激增或分布倾斜导致的性能下降。其核心价值在于将“盲目猜测”转化为“量化决策”，使海量数据查询始终高效稳定。
既然统计信息如此重要，那如何管理呢？统计信息的管理分为两种，手动管理和自动管理，如下举例说明：
手动收集统计信息（DBMS_STATS）：
obclient [test3]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oceanbase |
| test |
| test3 |
+--------------------+
5 rows in set (0.449 sec)
obclient [test3]> show tables;
+-----------------+
| Tables_in_test3 |
+-----------------+
| t1 |
+-----------------+
10 rows in set (0.676 sec)
--查看某张表上次收集统计信息的时间
obclient(root@test1)[test3]> select OWNER,TABLE_NAME,LAST_ANALYZED from oceanbase.dba_tab_statistics where table_name = 'T1';
+-------+------------+----------------------------+
| OWNER | TABLE_NAME | LAST_ANALYZED |
+-------+------------+----------------------------+
| test3 | t1 | 2025-08-12 14:23:39.220490 |
+-------+------------+----------------------------+
1 row in set (0.276 sec)
obclient [test3]> CALL DBMS_STATS.GATHER_SCHEMA_STATS ('test3',degree=>8,method_opt=>'FOR ALL COLUMNS SIZE AUTO'); --收集整个数据库下的表和索引，包含直方图
Query OK, 0 rows affected (19.241 sec)
obclient [test3]> CALL DBMS_STATS.GATHER_TABLE_STATS('test3','t1',degree=>4,method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY', force=>TRUE); --收集指定表
Query OK, 0 rows affected (0.615 sec)
obclient [test3]> CALL DBMS_STATS.GATHER_TABLE_STATS('test3', 't1', degree=>4, method_opt=>'FOR COLUMNS name SIZE 5'); --收集直方图指定列的名字和桶的个数
Query OK, 0 rows affected (0.868 sec)
obclient [test3]> CALL DBMS_STATS.GATHER_INDEX_STATS('test3','IND1',degree=>4,tabname=>'t1',method_opt=>'FOR ALL COLUMNS SIZE REPEAT'); --索引不能用method_opt
ERROR 1327 (42000): Undeclared variable: method_opt
obclient [test3]> CALL DBMS_STATS.GATHER_INDEX_STATS('test3','IND1',degree=>4,tabname=>'t1'); --收集索引的统计信息
Query OK, 0 rows affected (0.881 sec)
参数说明：
method_opt=>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
SIZE integer：指定收集列的直方图桶的个数，范围在 [1-2048]。 --这个选项需要指定列和桶的数量，手动指定
SIZE REPEAT：仅仅只收集已经有收集过的直方图的列的直方图，使用之前收集直方图设置的桶个数。 --这个类似之前收集过，修复一下直方图数据
SIZE AUTO：由数据库优化器来决定是否收集列的直方图，取决于列的使用情况，桶个数使用默认值为 254。 --这个数据库自动判断
SIZE SKEWONLY：仅仅只收集数据分布不均匀的列的直方图，直方图桶个数使用默认值为 254。 --这个针对数据倾斜的列
手动收集统计信息（ANALYZE）：
obclient [test3]> ANALYZE TABLE t1 COMPUTE STATISTICS FOR ALL COLUMNS SIZE 100; --收集表的所有列并收集直方图信息
ERROR 1235 (0A000): The Oracle-mode analyze syntax is used in the disable sql extension MySQL-mode not supported
obclient [test3]> ANALYZE TABLE t1 COMPUTE STATISTICS FOR ALL INDEXED COLUMNS SIZE 100; --收集表的所有索引列并收集直方图信息
ERROR 1235 (0A000): The Oracle-mode analyze syntax is used in the disable sql extension MySQL-mode not supported
obclient [test3]> ANALYZE TABLE t1 COMPUTE STATISTICS FOR ALL HIDDEN COLUMNS SIZE 100; --收集表的所有隐藏列并收集直方图信息
ERROR 1235 (0A000): The Oracle-mode analyze syntax is used in the disable sql extension MySQL-mode not supported
obclient [test3]> ANALYZE TABLE t1 COMPUTE STATISTICS FOR COLUMNS name SIZE 100; --收集指定列的统计信息并收集直方图信息
ERROR 1235 (0A000): The Oracle-mode analyze syntax is used in the disable sql extension MySQL-mode not supported
参数说明：FOR ALL [INDEXED | HIDDEN] COLUMNS：收集表的统计信息，同时收集指定列（所有列、索引列或隐藏列）的直方图信息。
注意：M模式不支持，O模式支持。
自动收集统计信息：
自动统计信息收集中，判断一个表的统计信息是否过期，主要依据上一次统计信息收集时间到本次收集期间该表的做增/删/改的比例（默认 10%），可以通过如下视图查看：
obclient [test3]> select * from oceanbase.DBA_TAB_MODIFICATIONS where table_owner='test3' and table_name = 't1';
+-------------+------------+----------------+-------------------+---------+---------+---------+------------+-----------+---------------+
| TABLE_OWNER | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | INSERTS | UPDATES | DELETES | TIMESTAMP | TRUNCATED | DROP_SEGMENTS |
+-------------+------------+----------------+-------------------+---------+---------+---------+------------+-----------+---------------+
| test3 | t1 | NULL | NULL | 0 | 0 | 0 | 2025-10-20 | NULL | NULL |
| test3 | t1 | NULL | NULL | 0 | 0 | 0 | 2025-08-11 | NULL | NULL |
+-------------+------------+----------------+-------------------+---------+---------+---------+------------+-----------+---------------+
2 rows in set (0.220 sec)
参数说明：
INSERTS/UPDATES/DELETES：统计自上一次自动收集统计信息以来的 DML 执行次数，转储合并后显示的是 DML 修改的记录个数。
TRUNCATED：指示上一次自动收集统计信息后该表、分区是否被 Truncate 过。
DROP_SEGMENTS：自上次分析以来删除的分区和子分区段数。
自动收集统计信息的策略管理：
查看收集策略：
obclient [test3]> SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT') FROM DUAL; --查看全局的策略
+---------------------------------------+
| DBMS_STATS.GET_PREFS('STALE_PERCENT') |
+---------------------------------------+
| 10 |
+---------------------------------------+
1 row in set (0.033 sec)
obclient [test3]> SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3') FROM DUAL; --查看指定SCHEMA的策略，这种方式默认还是全局的，不能直接查询SCHEMA的策略
+------------------------------------------------+
| DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3') |
+------------------------------------------------+
| 10 |
+------------------------------------------------+
1 row in set (0.003 sec)
obclient [test3]> SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3', 'T1') FROM DUAL; --查看指定表的策略
+------------------------------------------------------+
| DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3', 'T1') |
+------------------------------------------------------+
| 10 |
+------------------------------------------------------+
1 row in set (0.012 sec)
注意：查看策略基本上就是全局和表的级别，没有SCHEMA级别，即使SQL能执行成功。
设置收集策略：
obclient [test3]> CALL DBMS_STATS.SET_GLOBAL_PREFS('INCREMENTAL', 'TRUE'); --设置全局的收集策略
Query OK, 0 rows affected (0.059 sec)
obclient [test3]> SELECT DBMS_STATS.GET_PREFS('INCREMENTAL') FROM DUAL; --查看策略
+-------------------------------------+
| DBMS_STATS.GET_PREFS('INCREMENTAL') |
+-------------------------------------+
| TRUE |
+-------------------------------------+
1 row in set (0.004 sec)
obclient [test3]> CALL DBMS_STATS.SET_SCHEMA_PREFS('test3','DEGREE','8'); --设置SCHEMA的收集策略
Query OK, 0 rows affected (0.015 sec)
obclient [test3]> SELECT DBMS_STATS.GET_PREFS('DEGREE') FROM DUAL;
+--------------------------------+
| DBMS_STATS.GET_PREFS('DEGREE') |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set (0.004 sec)
obclient [test3]> SELECT DBMS_STATS.GET_PREFS('DEGREE', 'test3') FROM DUAL; --并未生效，不能直接通过这样的方式查询SCHEMA下的策略
+-----------------------------------------+
| DBMS_STATS.GET_PREFS('DEGREE', 'test3') |
+-----------------------------------------+
| NULL |
+-----------------------------------------+
1 row in set (0.004 sec)
obclient [test3]> CALL DBMS_STATS.SET_TABLE_PREFS('test3','t1','STALE_PERCENT','50'); --设置表的收集策略
Query OK, 0 rows affected (0.066 sec)
obclient [test3]> SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT') FROM DUAL;
+---------------------------------------+
| DBMS_STATS.GET_PREFS('STALE_PERCENT') |
+---------------------------------------+
| 10 |
+---------------------------------------+
1 row in set (0.004 sec)
obclient [test3]> SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3', 'T1') FROM DUAL;
+------------------------------------------------------+
| DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3', 'T1') |
+------------------------------------------------------+
| 50 |
+------------------------------------------------------+
1 row in set (0.004 sec)
obclient [test3]> CALL DBMS_STATS.SET_SCHEMA_PREFS('test3','STALE_PERCENT','29');
Query OK, 0 rows affected (0.190 sec)
obclient [test3]> SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT') FROM DUAL; --查看全局的策略
+---------------------------------------+
| DBMS_STATS.GET_PREFS('STALE_PERCENT') |
+---------------------------------------+
| 10 |
+---------------------------------------+
1 row in set (0.004 sec)
obclient [test3]> SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3') FROM DUAL; --SCHEMA策略并未生效，不能直接通过这样的方式查询SCHEMA下的策略
+------------------------------------------------+
| DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3') |
+------------------------------------------------+
| 10 |
+------------------------------------------------+
1 row in set (0.004 sec)
obclient [test3]> SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3', 'T1') FROM DUAL; --需要使用SCHEMA下表的方式间接确认SCHEMA的策略
+------------------------------------------------------+
| DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3', 'T1') |
+------------------------------------------------------+
| 29 |
+------------------------------------------------------+
1 row in set (0.004 sec)
obclient [test3]> CALL DBMS_STATS.DELETE_TABLE_PREFS('test3', 'T1', 'STALE_PERCENT'); --删除表的策略
Query OK, 0 rows affected (0.192 sec)
obclient [test3]> SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3', 'T1') FROM DUAL;
+------------------------------------------------------+
| DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3', 'T1') |
+------------------------------------------------------+
| 10 |
+------------------------------------------------------+
1 row in set (0.011 sec)
obclient [test3]> CALL DBMS_STATS.DELETE_SCHEMA_PREFS('test3', 'STALE_PERCENT'); --删除SCHEMA的策略
Query OK, 0 rows affected (0.061 sec)
obclient [test3]> SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3', 'T1') FROM DUAL;
+------------------------------------------------------+
| DBMS_STATS.GET_PREFS('STALE_PERCENT', 'test3', 'T1') |
+------------------------------------------------------+
| 10 |
+------------------------------------------------------+
1 row in set (0.007 sec)
| 参数 | 说明 |
|---|---|
| APPROXIMATE_NDV | 是否使用估算方式计算 NDV，默认为 True。 |
| CASCADE | 是否同时收集表和索引的统计信息，默认为 True。 |
| DEGREE | 设置统计信息收集任务的并行度，默认为 1。 |
| ESTIMATE_PERCENT | 设置数据采样比例，默认使用自适应的比例。 |
| GRANULARITY | 设置统计信息收集时的分区粒度，默认为 AUTO。 |
| INCREMENTAL | 是否采用增量收集策略，默认为 False。 |
| METHOD_OPT | 设置收集直方图信息的默认选项。 |
| STALE_PERCENT | 设置导致统计信息过期的数据变更百分比，默认 10%。 |
| STATS_RETENTION | 设置历史统计信息的保留时间，默认 31 天。 |
注意：以上为参数的设置，在设置时，需要看清楚，有全局的、有SCHEMA级别、有表级的，在设置参数值时，最好带上单引号。在删除SCHEMA和表级的策略后，会恢复为全局的策略。
查看自动收集统计信息策略：
obclient [test3]> select JOB_NAME,JOB_ACTION,REPEAT_INTERVAL,ENABLED,NEXT_RUN_DATE,MAX_RUN_DURATION from oceanbase.DBA_SCHEDULER_JOBS where JOB_NAME like '%WINDOW';
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
| JOB_NAME | JOB_ACTION | REPEAT_INTERVAL | ENABLED | NEXT_RUN_DATE | MAX_RUN_DURATION |
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
| FRIDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-24 22:00:00.000000 | 14400 |
| MONDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-20 22:00:00.000000 | 14400 |
| SATURDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-25 22:00:00.000000 | 14400 |
| SUNDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-26 22:00:00.000000 | 14400 |
| THURSDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-23 22:00:00.000000 | 14400 |
| TUESDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-21 22:00:00.000000 | 14400 |
| WEDNESDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-22 22:00:00.000000 | 14400 |
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
7 rows in set (0.008 sec)
| 维护窗口 | 日期 |
|---|---|
| MONDAY_WINDOW | 星期一 |
| TUESDAY_WINDOW | 星期二 |
| WEDNESDAY_WINDOW | 星期三 |
| THURSDAY_WINDOW | 星期四 |
| FRIDAY_WINDOW | 星期五 |
| SATURDAY_WINDOW | 星期六 |
| SUNDAY_WINDOW | 星期日 |
开启和关闭指定窗口的自动收集任务：
obclient [test3]> CALL DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
Query OK, 0 rows affected (0.547 sec)
obclient [test3]> select JOB_NAME,JOB_ACTION,REPEAT_INTERVAL,ENABLED,NEXT_RUN_DATE,MAX_RUN_DURATION from oceanbase.DBA_SCHEDULER_JOBS where JOB_NAME like '%WINDOW';
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
| JOB_NAME | JOB_ACTION | REPEAT_INTERVAL | ENABLED | NEXT_RUN_DATE | MAX_RUN_DURATION |
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
| FRIDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-24 22:00:00.000000 | 14400 |
| MONDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 0 | 2025-10-20 22:00:00.000000 | 14400 |
| SATURDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-25 22:00:00.000000 | 14400 |
| SUNDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-26 22:00:00.000000 | 14400 |
| THURSDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-23 22:00:00.000000 | 14400 |
| TUESDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-21 22:00:00.000000 | 14400 |
| WEDNESDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-22 22:00:00.000000 | 14400 |
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
7 rows in set (0.010 sec)
obclient [test3]> CALL DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');
Query OK, 0 rows affected (0.438 sec)
obclient [test3]> select JOB_NAME,JOB_ACTION,REPEAT_INTERVAL,ENABLED,NEXT_RUN_DATE,MAX_RUN_DURATION from oceanbase.DBA_SCHEDULER_JOBS where JOB_NAME like '%WINDOW';
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
| JOB_NAME | JOB_ACTION | REPEAT_INTERVAL | ENABLED | NEXT_RUN_DATE | MAX_RUN_DURATION |
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
| FRIDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-24 22:00:00.000000 | 14400 |
| MONDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-20 22:00:00.000000 | 14400 |
| SATURDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-25 22:00:00.000000 | 14400 |
| SUNDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-26 22:00:00.000000 | 14400 |
| THURSDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-23 22:00:00.000000 | 14400 |
| TUESDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-21 22:00:00.000000 | 14400 |
| WEDNESDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-22 22:00:00.000000 | 14400 |
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
7 rows in set (0.001 sec)
修改自动收集任务的参数：
obclient(root@test1)[test3]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2025-10-26 01:00:00');
ERROR 5935 (HY000): The date is invalid. Please check wether they are the same day in a week, or the day is passed.
obclient(root@test1)[test3]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2025-10-27 01:00:00');
Query OK, 0 rows affected (0.015 sec)
obclient(root@test1)[test3]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2025-10-27 02:00:00');
Query OK, 0 rows affected (0.032 sec)
obclient(root@test1)[test3]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2025-10-28 01:00:00');
ERROR 5935 (HY000): The date is invalid. Please check wether they are the same day in a week, or the day is passed.
obclient(root@test1)[test3]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2025-11-01 01:00:00');
ERROR 5935 (HY000): The date is invalid. Please check wether they are the same day in a week, or the day is passed.
obclient(root@test1)[test3]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2025-11-03 01:00:00');
Query OK, 0 rows affected (0.016 sec)
obclient(root@test1)[test3]> select JOB_NAME,JOB_ACTION,REPEAT_INTERVAL,ENABLED,NEXT_RUN_DATE,MAX_RUN_DURATION from oceanbase.DBA_SCHEDULER_JOBS where JOB_NAME like '%WINDOW';
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
| JOB_NAME | JOB_ACTION | REPEAT_INTERVAL | ENABLED | NEXT_RUN_DATE | MAX_RUN_DURATION |
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
| FRIDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-24 22:00:00.000000 | 14400 |
| MONDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-11-03 01:00:00.000000 | 14400 |
| SATURDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-25 22:00:00.000000 | 14400 |
| SUNDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-26 22:00:00.000000 | 14400 |
| THURSDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-23 22:00:00.000000 | 14400 |
| TUESDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-21 22:00:00.000000 | 14400 |
| WEDNESDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-22 22:00:00.000000 | 14400 |
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
7 rows in set (0.001 sec)
obclient [test3]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW','JOB_ACTION','DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(18000000000)');
Query OK, 0 rows affected (0.105 sec)
obclient [test3]> select JOB_NAME,JOB_ACTION,REPEAT_INTERVAL,ENABLED,NEXT_RUN_DATE,MAX_RUN_DURATION from oceanbase.DBA_SCHEDULER_JOBS where JOB_NAME like '%WINDOW';
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
| JOB_NAME | JOB_ACTION | REPEAT_INTERVAL | ENABLED | NEXT_RUN_DATE | MAX_RUN_DURATION |
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
| FRIDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-24 22:00:00.000000 | 14400 |
| MONDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(18000000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-11-03 01:00:00.000000 | 14400 |
| SATURDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-25 22:00:00.000000 | 14400 |
| SUNDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-26 22:00:00.000000 | 14400 |
| THURSDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-23 22:00:00.000000 | 14400 |
| TUESDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-21 22:00:00.000000 | 14400 |
| WEDNESDAY_WINDOW | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(14400000000) | FREQ=WEEKLY; INTERVAL=1 | 1 | 2025-10-22 22:00:00.000000 | 14400 |
+------------------+--------------------------------------------------------+-------------------------+---------+----------------------------+------------------+
7 rows in set (0.001 sec)
注意（PPT上没有的知识
）：在设置指定窗口的收集任务参数时，修改时间是重点，这个时间不能是之前已经设置过的、不能是非窗口的日期，也就是说可以设置下次任务的时间，日期的话必须是该窗口的日期，如：上次收集任务周一是2025-10-20 01:00:00，那么下次有效的设置可以为2025-10-27 01:00:00，2025-10-27 02:00:00，2025-11-03 02:00:00 。这个理解为每周对应一天，周一不能用其他任务的日期，步长就是以周为单位进行的，这次周一日期，下次周一日期，下下次周一日期，不能用其他日期（如周二的日期）。
