The statistics collection process of the OceanBase Database optimizer is time-consuming, especially the process of automatic statistics collection. Because the collection involves all tables in the current database, it is quite likely that statistics on some tables fail to be collected for some reasons. If statistics collection failures cannot be diagnosed and reported in time, no statistics are available for generating execution plans. As a result, poor plans are generated and the database runs abnormally.
Starting from OceanBase Database V4.2.0, monitoring and diagnostics for statistics collection is supported. This feature is mainly used to monitor and give feedback on the status of statistics collection.
Monitor the real-time statistics collection status
After manual or automatic statistics collection is initiated, you can query the (G)V$OB_OPT_STAT_GATHER_MONITOR view for the real-time statistics collection status, including the execution duration of the current collection task, the number of tables on which the statistics are collected, and the statistics collection strategy, so as to monitor the collection of statistics.
When you query the GV$OB_OPT_STAT_GATHER_MONITOR view in the sys tenant, the real-time statistics collection status of all tenants is displayed. When you query the view in a user tenant, the real-time statistics collection status of the current tenant is displayed. The V$OB_OPT_STAT_GATHER_MONITOR view shows the real-time statistics collection status on the current OBServer node for the current tenant.
You can execute the DESC statement to query the (G)V$OB_OPT_STAT_GATHER_MONITOR view. Here is an example:
obclient [none]> USE oceanbase;
Database changed
obclient [oceanbase]> DESC GV$OB_OPT_STAT_GATHER_MONITOR;
+-----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------+------+-----+---------+-------+
| TENANT_ID | bigint(20) | NO | | NULL | |
| SVR_IP | varchar(46) | NO | | NULL | |
| SVR_PORT | bigint(20) | NO | | NULL | |
| SESSION_ID | bigint(20) | NO | | NULL | |
| TRACE_ID | varchar(64) | NO | | NULL | |
| TASK_ID | varchar(36) | NO | | NULL | |
| TYPE | varchar(16) | NO | | | |
| TASK_START_TIME | datetime(6) | NO | | NULL | |
| TASK_DURATION_TIME | bigint(20) | NO | | NULL | |
| TASK_TABLE_COUNT | bigint(20) | NO | | NULL | |
| COMPLETED_TABLE_COUNT | bigint(20) | NO | | NULL | |
| RUNNING_TABLE_OWNER | varchar(128) | NO | | NULL | |
| RUNNING_TABLE_NAME | varchar(256) | NO | | NULL | |
| RUNNING_TABLE_DURATION_TIME | bigint(20) | NO | | NULL | |
+-----------------------------+--------------+------+-----+---------+-------+
14 rows in set
The fields in the (G)V$OB_OPT_STAT_GATHER_MONITOR view are described as follows:
TENANT_ID: the ID of the tenant.SVR_IP: the IP address of the OBServer node where the collection task resides.SVR_PORT: the port number of the OBServer node where the collection task resides.SESSION_ID: the ID of the session where the collection task is executed.TRACE_ID: the ID of the trace to which the collection task belongs.TASK_ID: the ID of the collection task.TYPE: the collection mode. Currently, theMANUAL GATHERandAUTO GATHERmodes are supported.TASK_START_TIME: the start time of the collection task.TASK_DURATION_TIME: the duration of the entire collection task. Unit: microseconds.TASK_TABLE_COUNT: the number of tables whose statistics are to be collected in the collection task.COMPLETED_TABLE_COUNT: the number of tables whose statistics have been collected in the collection task.RUNNING_TABLE_OWNER: the owner of the table whose statistics are being collected.RUNNING_TABLE_NAME: the name of the table whose statistics are being collected.RUNNING_TABLE_DURATION_TIME: the duration (in microseconds) of statistics collection on the table whose statistics are being collected.
The following example performs manual histogram statistics collection on table test.t1 by using the default strategy.
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');
The real-time statistics collection status of the specified MySQL tenant is returned as follows:
obclient> SELECT * FROM oceanbase.GV$OB_OPT_STAT_GATHER_MONITOR WHERE tenant_id = 1006;
+-----------+----------------+----------+------------+-----------------------------------+--------------------------------------+---------------+----------------------------+--------------------+------------------+-----------------------+---------------------+--------------------+-----------------------------+
| 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 |
+-----------+----------------+----------+------------+-----------------------------------+--------------------------------------+---------------+----------------------------+--------------------+------------------+-----------------------+---------------------+--------------------+-----------------------------+
| 1006 | xxx.xxx.xxx.xxx | 2882 | 3221575784 | YB42AC1E87C6-0005FF6AD9F825FB-0-0 | c921f339-1bc8-11ee-a7ea-52583362494c | MANUAL GATHER | 2023-07-06 14:46:07.691856 | 609652 | 1 | 0 | test | t1 | 609653 |
+-----------+----------------+----------+------------+-----------------------------------+--------------------------------------+---------------+----------------------------+--------------------+------------------+-----------------------+---------------------+--------------------+-----------------------------+
1 row in set
Query the historical statistics collection status
You can query related views to obtain the status of historical statistics collection tasks at the tenant level and table level.
Tenant-level query
You can query the DBA_OB_TASK_OPT_STAT_GATHER_HISTORY view for the details of historical statistics collection tasks, including the statistics collection status of a table and the list of failed automatic statistics collection tasks.
You can execute the DESC statement to query the DBA_OB_TASK_OPT_STAT_GATHER_HISTORY view. Here is an example:
obclient [none]> USE oceanbase;
Database changed
obclient [oceanbase]> DESC DBA_OB_TASK_OPT_STAT_GATHER_HISTORY;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| TENANT_ID | bigint(20) | NO | | NULL | |
| TASK_ID | varchar(36) | NO | | NULL | |
| TYPE | varchar(16) | NO | | | |
| STATUS | varchar(8) | NO | | | |
| TABLE_COUNT | bigint(20) | YES | | NULL | |
| FAILED_COUNT | bigint(20) | YES | | NULL | |
| START_TIME | datetime(6) | NO | | NULL | |
| END_TIME | datetime(6) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
The fields in the DBA_OB_TASK_OPT_STAT_GATHER_HISTORY view are described as follows:
TENANT_ID: the ID of the tenant.TASK_ID: the ID of the collection task.TYPE: the collection mode. Currently, theMANUAL GATHERandAUTO GATHERmodes are supported.STATUS: the status of the collection task. Currently, theSUCCESSandFAILEDstates are supported.TABLE_COUNT: the number of tables whose statistics have been collected in the collection task.FAILED_COUNT: the number of tables whose statistics failed to be collected in the collection task.START_TIME: the start time of the collection task.END_TIME: the end time of the collection task.
The following example queries the DBA_OB_TASK_OPT_STAT_GATHER_HISTORY view for the historical statistics collection status of a specified tenant.
obclient [test]> USE oceanbase;
Database changed
obclient [oceanbase]> SELECT * FROM DBA_OB_TASK_OPT_STAT_GATHER_HISTORY WHERE TENANT_ID = 1006;
+-----------+--------------------------------------+---------------+---------+-------------+--------------+----------------------------+----------------------------+
| TENANT_ID | TASK_ID | TYPE | STATUS | TABLE_COUNT | FAILED_COUNT | START_TIME | END_TIME |
+-----------+--------------------------------------+---------------+---------+-------------+--------------+----------------------------+----------------------------+
| 1006 | 12551575-1a73-11ee-97fa-0eac862eb0b0 | AUTO GATHER | SUCCESS | 28 | 0 | 2023-07-04 22:00:00.669344 | 2023-07-04 22:00:06.636230 |
| 1006 | 32163b43-1bc9-11ee-a7ea-52583362494c | MANUAL GATHER | SUCCESS | 1 | 0 | 2023-07-06 14:49:03.775825 | 2023-07-06 14:49:05.729871 |
| 1006 | 44783d8c-1b3c-11ee-8ac6-d28f59026246 | AUTO GATHER | SUCCESS | 19 | 0 | 2023-07-05 22:00:00.767591 | 2023-07-05 22:00:22.035235 |
| 1006 | a1487d0e-185a-11ee-8ac6-d28f59026246 | AUTO GATHER | SUCCESS | 304 | 0 | 2023-07-02 06:00:00.673996 | 2023-07-02 06:00:29.273704 |
| 1006 | c921f339-1bc8-11ee-a7ea-52583362494c | MANUAL GATHER | SUCCESS | 1 | 0 | 2023-07-06 14:46:07.691856 | 2023-07-06 14:46:09.712585 |
| 1006 | db64f8b8-1bc5-11ee-a7ea-52583362494c | MANUAL GATHER | SUCCESS | 1 | 0 | 2023-07-06 14:25:09.839887 | 2023-07-06 14:25:12.686117 |
| 1006 | e7dc9678-19a9-11ee-95a7-2646ab385e11 | AUTO GATHER | SUCCESS | 23 | 0 | 2023-07-03 22:00:00.596163 | 2023-07-03 22:00:05.079117 |
+-----------+--------------------------------------+---------------+---------+-------------+--------------+----------------------------+----------------------------+
7 rows in set
Table-level query
You can query the DBA_OB_TABLE_OPT_STAT_GATHER_HISTORY view for the historical statistics collection status of a table.
You can execute the DESC statement to query the DBA_OB_TABLE_OPT_STAT_GATHER_HISTORY view. Here is an example:
obclient [none]> USE oceanbase;
Database changed
obclient [oceanbase]> DESC DBA_OB_TABLE_OPT_STAT_GATHER_HISTORY;
+--------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+--------------+------+-----+---------+-------+
| OWNER | varchar(128) | NO | | | |
| TABLE_NAME | varchar(256) | NO | | | |
| TASK_ID | varchar(36) | NO | | NULL | |
| STATUS | varchar(8) | NO | | | |
| START_TIME | datetime(6) | YES | | NULL | |
| END_TIME | datetime(6) | YES | | NULL | |
| MEMORY_USED | bigint(20) | YES | | NULL | |
| STAT_REFRESH_FAILED_LIST | text | YES | | NULL | |
| PROPERTIES | text | YES | | NULL | |
+--------------------------+--------------+------+-----+---------+-------+
9 rows in set
The fields in the DBA_OB_TABLE_OPT_STAT_GATHER_HISTORY view are described as follows:
OWNER: the owner of the table.TABLE_NAME: the name of the table.TASK_ID: the ID of the collection task.STATUS: the statistics collection status of the table. Currently, theSUCCESSandFAILEDstates are supported.START_TIME: the time when statistics collection on the table starts.END_TIME: the time when statistics collection on the table ends.MEMORY_USED: the memory used during statistics collection on the table.STAT_REFRESH_FAILED_LIST: the IP addresses of servers on which statistics cache refresh fails during statistics collection on the table.PROPERTIES: the properties set for statistics collection on the table.
The following example queries the DBA_OB_TABLE_OPT_STAT_GATHER_HISTORY view for the historical statistics collection status of all tables under the test user.
obclient [test]> USE oceanbase;
Database changed
obclient [oceanbase]> SELECT * FROM DBA_OB_TABLE_OPT_STAT_GATHER_HISTORY WHERE OWNER = 'test';
+-------+--------------+--------------------------------------+---------+----------------------------+----------------------------+-------------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------+
| OWNER | TABLE_NAME | TASK_ID | STATUS | START_TIME | END_TIME | MEMORY_USED | STAT_REFRESH_FAILED_LIST | PROPERTIES |
+-------+--------------+--------------------------------------+---------+----------------------------+----------------------------+-------------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------+
| test | test_tbl3_h | 12551575-1a73-11ee-97fa-0eac862eb0b0 | SUCCESS | 2023-07-04 22:00:03.235058 | 2023-07-04 22:00:03.363855 | 22176 | NULL | GRANULARITY:AUTO;METHOD_OPT:FOR ALL COLUMNS SIZE AUTO;DEGREE:1;ESTIMATE_PERCENT:100.000000;BLOCK_SAMPLE:0;STALE_PERCENT:-1.000000; |
| test | test_tbl2_hr | 12551575-1a73-11ee-97fa-0eac862eb0b0 | SUCCESS | 2023-07-04 22:00:02.994304 | 2023-07-04 22:00:03.232528 | 131208 | NULL | GRANULARITY:AUTO;METHOD_OPT:FOR ALL COLUMNS SIZE AUTO;DEGREE:1;ESTIMATE_PERCENT:100.000000;BLOCK_SAMPLE:0;STALE_PERCENT:-1.000000; |
| test | test_tbl1_hr | 12551575-1a73-11ee-97fa-0eac862eb0b0 | SUCCESS | 2023-07-04 22:00:02.619126 | 2023-07-04 22:00:02.989421 | 131208 | NULL | GRANULARITY:AUTO;METHOD_OPT:FOR ALL COLUMNS SIZE AUTO;DEGREE:1;ESTIMATE_PERCENT:100.000000;BLOCK_SAMPLE:0;STALE_PERCENT:-1.000000; |
| test | t1 | 32163b43-1bc9-11ee-a7ea-52583362494c | SUCCESS | 2023-07-06 14:49:03.775825 | 2023-07-06 14:49:05.729871 | 30084 | NULL | GRANULARITY:AUTO;METHOD_OPT:FOR ALL COLUMNS SIZE AUTO;DEGREE:1;ESTIMATE_PERCENT:100.000000;BLOCK_SAMPLE:0;STALE_PERCENT:-1.000000; |
| test | test_tbl14 | 44783d8c-1b3c-11ee-8ac6-d28f59026246 | SUCCESS | 2023-07-05 22:00:16.057930 | 2023-07-05 22:00:16.232034 | 7936 | NULL | GRANULARITY:AUTO;METHOD_OPT:FOR ALL COLUMNS SIZE AUTO;DEGREE:1;ESTIMATE_PERCENT:100.000000;BLOCK_SAMPLE:0;STALE_PERCENT:-1.000000; |
| test | tbl1 | 44783d8c-1b3c-11ee-8ac6-d28f59026246 | SUCCESS | 2023-07-05 22:00:15.578265 | 2023-07-05 22:00:16.049143 | 8160 | NULL | GRANULARITY:AUTO;METHOD_OPT:FOR ALL COLUMNS SIZE AUTO;DEGREE:1;ESTIMATE_PERCENT:100.000000;BLOCK_SAMPLE:0;STALE_PERCENT:-1.000000; |
| test | t1 | c921f339-1bc8-11ee-a7ea-52583362494c | SUCCESS | 2023-07-06 14:46:07.691856 | 2023-07-06 14:46:09.712585 | 30084 | NULL | GRANULARITY:AUTO;METHOD_OPT:FOR ALL COLUMNS SIZE AUTO;DEGREE:1;ESTIMATE_PERCENT:100.000000;BLOCK_SAMPLE:0;STALE_PERCENT:-1.000000; |
| test | t1 | db64f8b8-1bc5-11ee-a7ea-52583362494c | SUCCESS | 2023-07-06 14:25:09.839887 | 2023-07-06 14:25:12.686117 | 30084 | NULL | GRANULARITY:AUTO;METHOD_OPT:FOR ALL COLUMNS SIZE AUTO;DEGREE:1;ESTIMATE_PERCENT:100.000000;BLOCK_SAMPLE:0;STALE_PERCENT:-1.000000; |
| test | t2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+--------------+--------------------------------------+---------+----------------------------+----------------------------+-------------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set
Delete historical statistics
You can use one of the following methods to delete historical statistics in OceanBase Database:
- Use
DBMS_STATS.PURGE_STATS($timestamp)for manual deletion. - Set a scheduled task for automatic deletion. The task automatically deletes historical statistics that exceeds the retention period (31 days by default).
The following example uses DBMS_STATS.PURGE_STATS($timestamp) to manually delete historical statistics saved before the specified timestamp.
/* In MySQL mode */
obclient [test]> CALL DBMS_STATS.PURGE_STATS ('2021-09-26 19:02:12.675729');
Query OK, 0 rows affected
/* In Oracle mode */
obclient [SYS]> CALL DBMS_STATS.PURGE_STATS(TO_TIMESTAMP('2021-09-26 19:02:12.675729', 'YYYY-MM-DD HH24:MI:SS.FF'));
Query OK, 0 rows affected