After initiating a major compaction, you can view the major compaction information through views.
The duration of a major compaction depends on the volume of data changed between two major compactions. The larger the data change, the longer the major compaction takes. In addition, the number of compaction threads and the cluster load during the major compaction also affect the duration.
View major compaction information of all tenants from the sys tenant
The sys tenant can view the tenant-level and partition-level major compaction information of all tenants by querying views.
Log in to the
systenant of the cluster as therootuser.Execute the following statements to view the major compaction information:
Query the
CDB_OB_ZONE_MAJOR_COMPACTIONorCDB_OB_MAJOR_COMPACTIONview for tenant-level major compaction information.The
CDB_OB_ZONE_MAJOR_COMPACTIONview displays the major compaction progress of all zones in all tenants.obclient> SELECT * FROM oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION;The query result is as follows:
+-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+ | TENANT_ID | ZONE | BROADCAST_SCN | LAST_SCN | LAST_FINISH_TIME | START_TIME | STATUS | +-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+ | 1 | zone1 | 1748368801021617000 | 1748368801021617000 | 2025-05-28 02:04:15.351286 | 2025-05-28 02:00:01.051592 | IDLE | | 1001 | zone1 | 1748368802924607000 | 1748368802924607000 | 2025-05-28 02:02:16.412661 | 2025-05-28 02:00:02.965810 | IDLE | | 1002 | zone1 | 1748368802035321000 | 1748368802035321000 | 2025-05-28 02:03:25.019173 | 2025-05-28 02:00:02.090687 | IDLE | | 1003 | zone1 | 1748368802977311000 | 1748368802977311000 | 2025-05-28 02:02:46.565036 | 2025-05-28 02:00:03.009192 | IDLE | | 1004 | zone1 | 1748368801786323000 | 1748368801786323000 | 2025-05-28 02:03:54.629630 | 2025-05-28 02:00:01.820863 | IDLE | +-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+ 5 rows in setIn the query result, observe the
STATUScolumn. The following table describes some of the columns.ColumnDescriptionTENANT_ID The tenant ID. ZONE The name of the zone. BROADCAST_SCN The broadcast version number of the major compaction. LAST_SCN The version number of the last major compaction. LAST_FINISH_TIME The end time of the last major compaction. START_TIME The start time of the major compaction. STATUS The status of the major compaction: IDLE: Not in a major compaction.COMPACTING: In a major compaction.VERIFYING: Verifying checksums.
The
CDB_OB_MAJOR_COMPACTIONview displays the global major compaction information of all tenants.obclient> SELECT * FROM oceanbase.CDB_OB_MAJOR_COMPACTION;The query result is as follows:
+-----------+---------------------+----------------------------+----------------------+---------------------+----------------------------+----------------------------+------------+----------+--------------+------+--------+ | TENANT_ID | FROZEN_SCN | FROZEN_TIME | GLOBAL_BROADCAST_SCN | LAST_SCN | LAST_FINISH_TIME | START_TIME | STATUS | IS_ERROR | IS_SUSPENDED | INFO | MODE | +-----------+---------------------+----------------------------+----------------------+---------------------+----------------------------+----------------------------+------------+----------+--------------+------+--------+ | 1 | 1772992803755388000 | 2026-03-09 02:00:03.755388 | 1772992803755388000 | 1772992803755388000 | 2026-03-09 02:05:15.618392 | 2026-03-09 02:00:03.777269 | IDLE | NO | NO | | TENANT | | 1001 | 1772992803664660000 | 2026-03-09 02:00:03.664660 | 1772992803664660000 | 1772992803664660000 | 2026-03-09 02:05:15.692208 | 2026-03-09 02:00:03.682327 | IDLE | NO | NO | | TENANT | | 1002 | 1772992801519693000 | 2026-03-09 02:00:01.519693 | 1772992801519693000 | 1772992801519693000 | 2026-03-09 02:05:42.538179 | 2026-03-09 16:15:11.214954 | COMPACTING | NO | NO | | WINDOW | | 1003 | 1772992804311771000 | 2026-03-09 02:00:04.311771 | 1772992804311771000 | 1772992804311771000 | 2026-03-09 02:05:16.145435 | 2026-03-09 02:00:04.335287 | IDLE | NO | NO | | TENANT | | 1004 | 1772647200462679000 | 2026-03-05 02:00:00.462679 | 1772647200462679000 | 1772647200462679000 | 2026-03-05 02:05:31.394359 | 2026-03-05 02:00:00.480895 | IDLE | NO | NO | | TENANT | +-----------+---------------------+----------------------------+----------------------+---------------------+----------------------------+----------------------------+------------+----------+--------------+------+--------+ 5 rows in setIn the query result, observe the
STATUScolumn. The following table describes some of the columns.ColumnDescriptionTENANT_ID The tenant ID. FROZEN_SCN The version number of the most recent major compaction. FROZEN_TIME The readable time corresponding to FROZEN_SCN.GLOBAL_BROADCAST_SCN The globally broadcast version of the major compaction. LAST_SCN The version of the last completed major compaction. LAST_FINISH_TIME The end time of the last major compaction. START_TIME The start time of the major compaction. STATUS The status of the major compaction: IDLE: Not in a major compaction.COMPACTING: In a major compaction.VERIFYING: Verifying checksums.
IS_ERROR YES: An error occurred during the major compaction.NO: No error occurred during the major compaction.
IS_SUSPENDED YES: The major compaction is suspended.NO: The major compaction is not suspended.
INFO The information about the major compaction. Mode The type of the major compaction: TENANT: Tenant-level major compaction.WINDOW: Window major compaction.
Query the
GV$OB_MERGE_INFOorGV$OB_TABLET_COMPACTION_HISTORYview for partition-level major compaction information.The
GV$OB_MERGE_INFOview displays basic statistics of tablet-level compactions.obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_MERGE_INFO WHERE TENANT_ID=1002 AND TABLET_ID=101106 ORDER BY START_TIME DESC LIMIT 10;The query result is as follows:
+----------------+----------+-----------+-------+-----------+--------------+---------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+ | SVR_IP | SVR_PORT | TENANT_ID | LS_ID | TABLET_ID | ACTION | COMPACTION_SCN | START_TIME | END_TIME | MACRO_BLOCK_COUNT | REUSE_PCT | PARALLEL_DEGREE | +----------------+----------+-----------+-------+-----------+--------------+---------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+ | 172.xx.xxx.xxx | 2882 | 1002 | 1 | 101106 | MEDIUM_MERGE | 1773044346622805000 | 2026-03-09 16:21:06.800772 | 2026-03-09 16:21:06.815196 | 1 | 0.00 | 1 | | 172.xx.xxx.xxx | 2882 | 1002 | 1 | 101106 | MINI_MERGE | 1773044230029441000 | 2026-03-09 16:19:46.413401 | 2026-03-09 16:19:46.420119 | 1 | 0.00 | 1 | | 172.xx.xxx.xxx | 2882 | 1002 | 1 | 101106 | MINI_MERGE | 1773044111197275001 | 2026-03-09 16:15:11.393854 | 2026-03-09 16:15:11.399243 | 1 | 0.00 | 1 | | 172.xx.xxx.xxx | 2882 | 1002 | 1 | 101106 | MAJOR_MERGE | 1772992801519693000 | 2026-03-09 02:05:24.242726 | 2026-03-09 02:05:24.258979 | 1 | 0.00 | 1 | | 172.xx.xxx.xxx | 2882 | 1002 | 1 | 101106 | MINI_MERGE | 1772992930030529000 | 2026-03-09 02:03:27.452490 | 2026-03-09 02:03:27.457785 | 1 | 0.00 | 1 | | 172.xx.xxx.xxx | 2882 | 1002 | 1 | 101106 | MINI_MERGE | 1772992801522849001 | 2026-03-09 02:00:02.426948 | 2026-03-09 02:00:02.433967 | 1 | 0.00 | 1 | | 172.xx.xxx.xxx | 2882 | 1002 | 1 | 101106 | MAJOR_MERGE | 1772906404772256000 | 2026-03-08 02:04:55.411995 | 2026-03-08 02:04:55.428263 | 1 | 0.00 | 1 | | 172.xx.xxx.xxx | 2882 | 1002 | 1 | 101106 | MINI_MERGE | 1772906530028843000 | 2026-03-08 02:03:00.677384 | 2026-03-08 02:03:00.684448 | 1 | 0.00 | 1 | | 172.xx.xxx.xxx | 2882 | 1002 | 1 | 101106 | MINI_MERGE | 1772906404777532001 | 2026-03-08 02:00:05.661945 | 2026-03-08 02:00:05.669834 | 1 | 0.00 | 1 | | 172.xx.xxx.xxx | 2882 | 1002 | 1 | 101106 | MAJOR_MERGE | 1772820003066690000 | 2026-03-07 02:02:21.913053 | 2026-03-07 02:02:21.926392 | 1 | 0.00 | 1 | +----------------+----------+-----------+-------+-----------+--------------+---------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+ 10 rows in setIn the query result, the meanings of the values in the
ACTIONcolumn are as follows:MDS_TABLE_MERGE: Persists system metadata to disk in SSTable format.MAJOR_MERGE: Tenant-level major compaction.MEDIUM_MERGE: Partition-level major compaction.MINI_MERGE: Mini compaction, converting the MemTable into a Mini SSTable.MINOR_MERGE: Minor compaction, combining multiple Mini SSTables into a new Mini SSTable, or combining multiple Mini SSTables with one Minor SSTable into a new Minor SSTable.META_MAJOR_MERGE: A special type of compaction that combines data from before a specified point in time into a Meta Major SSTable. Its data format is the same as that of a Major SSTable, excluding multi-version data and uncommitted transaction data.
For more information about the other fields in the
GV$OB_MERGE_INFOview, see GV$OB_MERGE_INFO.The
GV$OB_TABLET_COMPACTION_HISTORYview displays the detailed history of tablet-level compactions.obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_TABLET_COMPACTION_HISTORY WHERE TENANT_ID=1002 AND TABLET_ID=101106 ORDER BY START_TIME DESC LIMIT 2\GThe query result is as follows:
*************************** 1. row *************************** SVR_IP: 172.xx.xxx.xxx SVR_PORT: 2882 TENANT_ID: 1002 LS_ID: 1 TABLET_ID: 101106 TYPE: MEDIUM_MERGE COMPACTION_SCN: 1773044346622805000 START_TIME: 2026-03-09 16:21:06.800772 FINISH_TIME: 2026-03-09 16:21:06.815196 TASK_ID: YB42AC1E87E2-00064B8EBD3C7919-0-0 OCCUPY_SIZE: 23337 MACRO_BLOCK_COUNT: 1 MULTIPLEXED_MACRO_BLOCK_COUNT: 0 NEW_MICRO_COUNT_IN_NEW_MACRO: 3 MULTIPLEXED_MICRO_COUNT_IN_NEW_MACRO: 2 TOTAL_ROW_COUNT: 1786 INCREMENTAL_ROW_COUNT: 79 COMPRESSION_RATIO: 0.14 NEW_FLUSH_DATA_RATE: 2905 PROGRESSIVE_COMPACTION_ROUND: 1 PROGRESSIVE_COMPACTION_NUM: 0 PARALLEL_DEGREE: 1 PARALLEL_INFO: - PARTICIPANT_TABLE: table_cnt=4,[MAJOR]snapshot_version=1772992801519693000;[MINI]start_scn=1772992801522849001,end_scn=1773044230029441000; MACRO_ID_LIST: 15945 COMMENTS: cost_mb=2;win="{score:78, base_inc_row_cnt:78, dynamic_info:{query_cnt:0, read_amp:1.00, info:32768, ver:0}}"; START_CG_ID: 0 END_CG_ID: 0 KEPT_SNAPSHOT: {type:"LS_RESERVED", snapshot:1773042651332941000} MERGE_LEVEL: MICRO_BLOCK_LEVEL EXEC_MODE: EXEC_MODE_LOCAL IS_FULL_MERGE: FALSE IO_COST_TIME_PERCENTAGE: 0 MERGE_REASON: WINDOW_COMPACTION BASE_MAJOR_STATUS: CO_MERGE_TYPE: MDS_FILTER_INFO: EXECUTE_TIME: 6265 FILTER_ROW_COUNT: 0 *************************** 2. row *************************** SVR_IP: 172.xx.xxx.xxx SVR_PORT: 2882 TENANT_ID: 1002 LS_ID: 1 TABLET_ID: 101106 TYPE: MINI_MERGE COMPACTION_SCN: 1773044230029441000 START_TIME: 2026-03-09 16:19:46.413401 FINISH_TIME: 2026-03-09 16:19:46.420119 TASK_ID: YB42AC1E87E2-00064B8EBD3C7912-0-0 OCCUPY_SIZE: 393 MACRO_BLOCK_COUNT: 1 MULTIPLEXED_MACRO_BLOCK_COUNT: 0 NEW_MICRO_COUNT_IN_NEW_MACRO: 1 MULTIPLEXED_MICRO_COUNT_IN_NEW_MACRO: 0 TOTAL_ROW_COUNT: 1 INCREMENTAL_ROW_COUNT: 1 COMPRESSION_RATIO: 1 NEW_FLUSH_DATA_RATE: 59 PROGRESSIVE_COMPACTION_ROUND: 0 PROGRESSIVE_COMPACTION_NUM: 0 PARALLEL_DEGREE: 1 PARALLEL_INFO: - PARTICIPANT_TABLE: table_cnt=1,start_scn=1773044111197275001,end_scn=1773044230029441000; MACRO_ID_LIST: 15945 COMMENTS: cost_mb=2; START_CG_ID: 0 END_CG_ID: 0 KEPT_SNAPSHOT: {type:"LS_RESERVED", snapshot:1773042571305127000} MERGE_LEVEL: MACRO_BLOCK_LEVEL EXEC_MODE: EXEC_MODE_LOCAL IS_FULL_MERGE: FALSE IO_COST_TIME_PERCENTAGE: 0 MERGE_REASON: BASE_MAJOR_STATUS: CO_MERGE_TYPE: MDS_FILTER_INFO: EXECUTE_TIME: 1070 FILTER_ROW_COUNT: 0 2 rows in setIn the query result, the meanings of the values in the
TYPEcolumn are as follows:MDS_TABLE_MERGE: Persists system metadata to disk in SSTable format.MAJOR_MERGE: Tenant-level major compaction.MEDIUM_MERGE: Partition-level major compaction.MINI_MERGE: Mini compaction, converting the MemTable into a Mini SSTable.MINOR_MERGE: Minor compaction, combining multiple Mini SSTables into a new Mini SSTable, or combining multiple Mini SSTables with one Minor SSTable into a new Minor SSTable.META_MAJOR_MERGE: A special type of compaction that combines data from before a specified point in time into a Meta Major SSTable. Its data format is the same as that of a Major SSTable, excluding multi-version data and uncommitted transaction data.
For more information about the other fields in the
GV$OB_TABLET_COMPACTION_HISTORYview, see GV$OB_TABLET_COMPACTION_HISTORY.
View major compaction information of the current tenant
A user tenant can view the major compaction information of the current tenant by using a view.
Log in to the database as the tenant administrator of the user tenant.
Execute the following statements to view the major compaction information.
Query the
DBA_OB_ZONE_MAJOR_COMPACTIONorDBA_OB_MAJOR_COMPACTIONview for the tenant-level major compaction information of the current tenant.The
DBA_OB_ZONE_MAJOR_COMPACTIONview displays the major compaction information of each zone in the current tenant.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_ZONE_MAJOR_COMPACTION;An example of the query result is as follows:
+-------+---------------------+---------------------+----------------------------+----------------------------+--------+ | ZONE | BROADCAST_SCN | LAST_SCN | LAST_FINISH_TIME | START_TIME | STATUS | +-------+---------------------+---------------------+----------------------------+----------------------------+--------+ | zone1 | 1748455202538454000 | 1748455202538454000 | 2025-05-29 02:04:34.174642 | 2025-05-29 02:00:02.679713 | IDLE | +-------+---------------------+---------------------+----------------------------+----------------------------+--------+ 1 row in setIn the query result, observe the
STATUScolumn. The following table describes some of the columns.ColumnDescriptionZONE The name of the zone. BROADCAST_SCN The broadcasted major compaction version number. LAST_SCN The version number of the last major compaction. LAST_FINISH_TIME The end time of the last major compaction. START_TIME The start time of the major compaction. STATUS The status of the major compaction: IDLE: Not in a major compaction.COMPACTING: In a major compaction.VERIFYING: Verifying checksums.
The
DBA_OB_MAJOR_COMPACTIONview displays the global major compaction information of the current tenant.obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_MAJOR_COMPACTION;The query result is as follows:
+---------------------+----------------------------+----------------------+---------------------+----------------------------+----------------------------+------------+----------+--------------+------+--------+ | FROZEN_SCN | FROZEN_TIME | GLOBAL_BROADCAST_SCN | LAST_SCN | LAST_FINISH_TIME | START_TIME | STATUS | IS_ERROR | IS_SUSPENDED | INFO | MODE | +---------------------+----------------------------+----------------------+---------------------+----------------------------+----------------------------+------------+----------+--------------+------+--------+ | 1772992801519693000 | 2026-03-09 02:00:01.519693 | 1772992801519693000 | 1772992801519693000 | 2026-03-09 02:05:42.538179 | 2026-03-09 16:15:11.214954 | COMPACTING | NO | NO | | WINDOW | +---------------------+----------------------------+----------------------+---------------------+----------------------------+----------------------------+------------+----------+--------------+------+--------+ 1 row in setIn the query result, observe the
STATUScolumn. The following table describes some of the columns.ColumnDescriptionFROZEN_SCN The version number of the most recent major compaction. FROZEN_TIME The readable time corresponding to FROZEN_SCN.GLOBAL_BROADCAST_SCN The globally broadcasted major compaction version. LAST_SCN The version of the last completed major compaction. LAST_FINISH_TIME The end time of the last major compaction. START_TIME The start time of the major compaction. STATUS The status of the major compaction: IDLE: Not in a major compaction.COMPACTING: In a major compaction.VERIFYING: Verifying checksums.
IS_ERROR YES: An error occurred during the major compaction.NO: No error occurred during the major compaction.
IS_SUSPENDED YES: The major compaction is suspended.NO: The major compaction is not suspended.
INFO The information about the major compaction. Mode The type of the major compaction: TENANT: Tenant-level major compaction.WINDOW: Window major compaction.
Query the
GV$OB_TABLET_COMPACTION_HISTORYview for partition-level major compaction information of the current tenant.The
GV$OB_TABLET_COMPACTION_HISTORYview displays the detailed history of tablet-level compactions.obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_TABLET_COMPACTION_HISTORY WHERE TABLET_ID=101106 ORDER BY START_TIME DESC LIMIT 2\GThe query result is as follows:
*************************** 1. row *************************** SVR_IP: 172.xx.xxx.xxx SVR_PORT: 2882 TENANT_ID: 1002 LS_ID: 1 TABLET_ID: 101106 TYPE: MEDIUM_MERGE COMPACTION_SCN: 1773044346622805000 START_TIME: 2026-03-09 16:21:06.800772 FINISH_TIME: 2026-03-09 16:21:06.815196 TASK_ID: YB42AC1E87E2-00064B8EBD3C7919-0-0 OCCUPY_SIZE: 23337 MACRO_BLOCK_COUNT: 1 MULTIPLEXED_MACRO_BLOCK_COUNT: 0 NEW_MICRO_COUNT_IN_NEW_MACRO: 3 MULTIPLEXED_MICRO_COUNT_IN_NEW_MACRO: 2 TOTAL_ROW_COUNT: 1786 INCREMENTAL_ROW_COUNT: 79 COMPRESSION_RATIO: 0.14 NEW_FLUSH_DATA_RATE: 2905 PROGRESSIVE_COMPACTION_ROUND: 1 PROGRESSIVE_COMPACTION_NUM: 0 PARALLEL_DEGREE: 1 PARALLEL_INFO: - PARTICIPANT_TABLE: table_cnt=4,[MAJOR]snapshot_version=1772992801519693000;[MINI]start_scn=1772992801522849001,end_scn=1773044230029441000; MACRO_ID_LIST: 15945 COMMENTS: cost_mb=2;win="{score:78, base_inc_row_cnt:78, dynamic_info:{query_cnt:0, read_amp:1.00, info:32768, ver:0}}"; START_CG_ID: 0 END_CG_ID: 0 KEPT_SNAPSHOT: {type:"LS_RESERVED", snapshot:1773042651332941000} MERGE_LEVEL: MICRO_BLOCK_LEVEL EXEC_MODE: EXEC_MODE_LOCAL IS_FULL_MERGE: FALSE IO_COST_TIME_PERCENTAGE: 0 MERGE_REASON: WINDOW_COMPACTION BASE_MAJOR_STATUS: CO_MERGE_TYPE: MDS_FILTER_INFO: EXECUTE_TIME: 6265 FILTER_ROW_COUNT: 0 *************************** 2. row *************************** SVR_IP: 172.xx.xxx.xxx SVR_PORT: 2882 TENANT_ID: 1002 LS_ID: 1 TABLET_ID: 101106 TYPE: MINI_MERGE COMPACTION_SCN: 1773044230029441000 START_TIME: 2026-03-09 16:19:46.413401 FINISH_TIME: 2026-03-09 16:19:46.420119 TASK_ID: YB42AC1E87E2-00064B8EBD3C7912-0-0 OCCUPY_SIZE: 393 MACRO_BLOCK_COUNT: 1 MULTIPLEXED_MACRO_BLOCK_COUNT: 0 NEW_MICRO_COUNT_IN_NEW_MACRO: 1 MULTIPLEXED_MICRO_COUNT_IN_NEW_MACRO: 0 TOTAL_ROW_COUNT: 1 INCREMENTAL_ROW_COUNT: 1 COMPRESSION_RATIO: 1 NEW_FLUSH_DATA_RATE: 59 PROGRESSIVE_COMPACTION_ROUND: 0 PROGRESSIVE_COMPACTION_NUM: 0 PARALLEL_DEGREE: 1 PARALLEL_INFO: - PARTICIPANT_TABLE: table_cnt=1,start_scn=1773044111197275001,end_scn=1773044230029441000; MACRO_ID_LIST: 15945 COMMENTS: cost_mb=2; START_CG_ID: 0 END_CG_ID: 0 KEPT_SNAPSHOT: {type:"LS_RESERVED", snapshot:1773042571305127000} MERGE_LEVEL: MACRO_BLOCK_LEVEL EXEC_MODE: EXEC_MODE_LOCAL IS_FULL_MERGE: FALSE IO_COST_TIME_PERCENTAGE: 0 MERGE_REASON: BASE_MAJOR_STATUS: CO_MERGE_TYPE: MDS_FILTER_INFO: EXECUTE_TIME: 1070 FILTER_ROW_COUNT: 0 2 rows in setIn the query result, the meanings of the values in the
TYPEcolumn are as follows:MDS_TABLE_MERGE: Persists system metadata to disk in SSTable format.MAJOR_MERGE: Tenant-level major compaction.MEDIUM_MERGE: Partition-level major compaction.MINI_MERGE: Mini compaction, converting the MemTable into a Mini SSTable.MINOR_MERGE: Minor compaction, combining multiple Mini SSTables into a new Mini SSTable, or combining multiple Mini SSTables with one Minor SSTable into a new Minor SSTable.META_MAJOR_MERGE: A special type of compaction that combines data from before a specified point in time into a Meta Major SSTable. Its data format is the same as that of a Major SSTable, excluding multi-version data and uncommitted transaction data.
For more information about the other fields in the
GV$OB_TABLET_COMPACTION_HISTORYview, see GV$OB_TABLET_COMPACTION_HISTORY.