This topic describes how to monitor and maintain vector indexes in OceanBase AI Database.
Monitoring
This section describes how to view vector index status through system views and how to use profile analysis to examine the time consumption and execution details of each phase of a single vector search.
Index status monitoring
You can view the basic information and real-time status of vector indexes through system views.
View |
Description |
|---|---|
| [G]V$OB_HNSW_INDEX_INFO | View the basic information and real-time status of HNSW series indexes. |
| [G]V$OB_HNSW_INDEX_SEGMENT_INFO | View the basic information and real-time status of each data segment in a single partition of an HNSW series index table. |
| [G]V$OB_IVF_INDEX_INFO | View the basic information and real-time status of IVF series indexes. |
| [G]V$OB_SINDI_INDEX_INFO | View the basic information and real-time status of a sparse index in memory. |
Single vector search performance monitoring
When a vector search SQL query takes a long time to execute or occasionally experiences a significant increase in latency, you can use DBMS_XPROFILE.DISPLAY_PROFILE() to view the execution details of each phase in that vector search and identify the main source of overhead. This feature is supported for all current dense and sparse vector indexes.
Execute a vector search SQL query and view the profile
The following example omits the table creation steps. Assume the sample table test_table already contains an id column, a four-dimensional vector column vector_col, and a vector index idx_vector that uses cosine distance. In practical applications, adjust the query based on your actual table structure, indexes, and vector dimensions.
Execute the vector search SQL query you want to analyze using the
MONITORhint.-- The VECTOR_INDEX hint is used to specify a vector index. SELECT /*+ MONITOR VECTOR_INDEX(test_table idx_vector) */ id FROM test_table WHERE id >= 3 ORDER BY COSINE_DISTANCE(vector_col, '[0.1, 0.2, 0.3, 0.4]') APPROXIMATE LIMIT 5;Immediately afterwards, view the profile of this query within the same session:
SELECT DBMS_XPROFILE.DISPLAY_PROFILE();To view a specific query, you can explicitly pass its
trace_id:SELECT DBMS_XPROFILE.DISPLAY_PROFILE('<trace_id>');
The returned result displays the Vector Search subtree under the physical operator that actually executed the vector search SQL. The following is a schematic illustration; the numbers are for explanation only and not the actual measurement results of the preceding SQL:
0.PHY_TABLE_SCAN
...
Vector Search
Partition
tablet id:200017
Vector Iter
try path:1
current path:2
switch reason:1
pre filter rows scanned:10000
pre filter time:49.879ms
vec index search time:9.199ms
iter filter loop cnt:2
iter filter row:98
iter filter matched row count:10
iter filter time:317.727ms
output row count:10
total search time:376.949ms
Vector Index HNSW Scan
filter mode:0
vsag query segment cnt:1
vsag query time:5.081ms
Identify the main time-consuming phases
You can analyze and identify the main time-consuming phases in the following order:
- View the overall time consumption and execution instances. Navigate from the statistics of the SQL physical operator to
Vector Searchto view thetablet idandtotal search timefor eachPartition. If the vector search involves multiple data tablets, you can compare the time consumption of each execution instance by tablet id to determine if the overhead is concentrated in a particular data tablet. - Distinguish between filtering and index search overhead. Compare the pre-filter time (
pre filter time), candidate filter time (iter filter time), and vector index search time (vec index search time). If there are many candidate rows, few matching rows after filtering, and many iteration rounds, it indicates multiple rounds of candidate expansion were performed to obtain sufficient results. - Check if the filter path switched.
try pathandcurrent pathrepresent the initial and final paths, respectively. A non-zeroswitch reasonindicates an actual switch occurred. In the preceding illustration,switch reason:1indicates the pre-filter timed out, and iterative filtering was ultimately used; if the candidate filter time is high, further check the matching of filter conditions. - Enter algorithm nodes to view specific overhead. For HNSW and SINDI series, pay attention to the time spent on index search, incremental data search, and replenishing the index data required for the query; for IVF series, pay attention to candidate scanning, distance calculation, and the time spent on cache construction or rollback when the cache is unavailable. For example, a high
complete data total timeindicates significant overhead for replenishing index data, while high cache construction time for IVF indicates the current query is undertaking cache construction work. - Check if query retry occurred. If multiple instances appear for the same Tablet, check the path switch reason (
vec retry reason) in the failed instances. The final successful instance does not display this field, so you cannot judge the retry reason by looking at only the last instance.
For a complete list of output fields and numerical value descriptions, see DISPLAY_PROFILE.
Notice
- The time consumed in different phases may be inclusive and cannot be directly added. During parallel execution, the cumulative time of each execution instance may also be greater than the actual time taken by this vector search.
- If a field is not displayed, it usually means that the corresponding phase was not executed or is not applicable, not that the field's value is 0.
- A profile shows only one execution of a specified trace and cannot reflect the average duration or historical trend of multiple executions of the same SQL.
- Profiles can help identify execution phases with high time consumption, but they cannot directly determine resource-level causes such as CPU, I/O, memory, or lock waits. After locating time-consuming phases, further investigation should be conducted in conjunction with execution plans, ASH, resource monitoring, and logs.
Basic maintenance
When there is a large amount of incremental data, search performance is affected. To reduce the amount of data in the incremental data table, OceanBase AI Database introduces DBMS_VECTOR to maintain vector indexes.
Online modification of index parameters (HNSW series only)
HNSW series indexes support online modification of target index parameters through ALTER_INDEX_PARAMS. Asynchronous partition-level rebuilding is triggered when parameter changes occur; modifying only query-related parameters does not trigger rebuilding. After modification, you can query the SEGMENT_PARAMS column in (G)V$OB_HNSW_INDEX_SEGMENT_INFO to view the actual parameters used by each data segment.
Full refresh (rebuilding)
Manual full-table rebuilding
If a large amount of data is updated or deleted after an index is created, we recommend that you use the REBUILD_INDEX procedure to perform a full refresh.
A full refresh is checked every 24 hours. If new data exceeds 20% of the original data, a full refresh is automatically executed. The full refresh runs asynchronously in the background, first creating a new index and then replacing the old one. During the rebuilding process, the old index remains available, but the overall process is relatively slow.
We also provide the vector_index_memory_saving_mode parameter to control memory usage during index rebuilding. Enabling this mode reduces memory consumption during the rebuild of vector indexes on partitioned tables. Typically, vector index rebuilding requires twice the memory of the index. After the memory-saving mode is enabled, the system temporarily deletes the memory index of a partition after it is built to release memory, effectively reducing the total memory required for the rebuilding operation. For syntax and examples, see vector_index_memory_saving_mode.
Considerations are as follows:
Performing Online DDL and Offline DDL Operations operations (such as modifying the table structure or primary key with
ALTER TABLE) will cause the index table to be rebuilt. Since the degree of parallelism cannot be specified for index rebuilding, the system uses a single thread by default. Therefore, when the data volume is large, the rebuilding process can be slow, affecting the execution efficiency of the entire offline DDL.When modifying index parameters during index rebuilding, you must specify both
typeanddistancein the parameter list, and thetypeanddistancemust match the original index type. For example, if the original index type washnswand the distance algorithm wasl2, then rebuilding must specifytype=hnswanddistance=l2.Index rebuilding supports:
- Modifying the values of
m,ef_search, andef_construction. - Enabling or disabling identical-vector grouping for HNSW series indexes by using
duplicate_group. For more information, see Switch the vector grouping mode. - Online rebuilding of
ef_searchparameters. - Rebuilding between
hnswandhnsw_sqindex types. - Rebuilding between
ivf_flatandivf_flat, and betweenivf_pqandivf_pqindex types. - Setting the degree of parallelism during rebuilding. For examples, see REBUILD_INDEX.
- Modifying the values of
Index rebuilding does not support:
- Modifying the
typeanddistanceparameters. - Rebuilding between
hnswandivfindex types. - Rebuilding between
hnswandhnsw_bqindex types. - Cross-rebuilding between
ivf_flatandivf_pq.
- Modifying the
Switch the vector grouping mode
Note
This feature is supported starting from V4.6.3 Hotfix1.
For an existing HNSW series index, you can enable or disable the identical-vector grouping feature through the IDX_PARAMETERS parameter of DBMS_VECTOR.REBUILD_INDEX to adjust the recall optimization settings for scenarios with many duplicate vectors. When this feature is enabled, recall improves in scenarios with many duplicate vectors, but the memory overhead for recording grouping relationships increases. Currently, only exactly identical vectors are supported; merging different vectors by a similarity threshold is not supported.
- Specify
duplicate_group=TRUE: convert the index to the grouped format through a full rebuild. - Specify
duplicate_group=FALSE: convert the index to the non-grouped format through a full rebuild. - Do not specify
duplicate_group: keep the settings of the currently effective index and do not change the grouping mode.
Switching the mode requires a full rebuild of the index rather than merely changing a parameter value. During the rebuild, the original index continues to serve requests, and the new index takes effect after it is built successfully. If the rebuild fails, the original index is still used.
For the complete parameter description, see REBUILD_INDEX.
Automatic partition rebuilding (recommended)
Note
- To enable or resume an automatic partition rebuilding task, manually perform a full-table rebuild for all vector indexes first. Otherwise, the partition rebuilding task may not execute automatically.
- This feature supports HNSW series indexes, semantic indexes, and memory-sparse indexes; it does not support IVF series indexes.
Note
Semantic indexes handle both incremental data and snapshot tasks during automatic partition rebuilding, ensuring the consistency and integrity of index data.
An automatic partition rebuilding task is triggered in the following two scenarios:
- When executing a vector index search statement.
- Scheduled check, with the execution cycle configurable manually.
Configure the execution cycle
Under the
oceanbasedatabase, configure the execution cycle using the parameter vector_index_optimize_duty_time. Example:ALTER SYSTEM SET vector_index_optimize_duty_time='[23:00:00, 24:00:00]';After the preceding configuration is completed, the partition rebuilding task is executed only between 23:00:00 and 24:00:00. The task is not initiated during other periods. For more information about the parameters, see the corresponding configuration item documentation.
View task progress and history
You can view task progress and historical tasks through the oceanbase.DBA_OB_VECTOR_INDEX_TASKS or oceanbase.DBA_OB_VECTOR_INDEX_TASK_HISTORY view.
Note
The above views cover background tasks of vector indexes, such as partition rebuilding, segment minor compaction and major compaction, IVF cluster center loading and cleaning, semantic index refreshing, and memory synchronization. You can distinguish task types by the
TASK_TYPEfield in the view.You can view the status of a task by using the
STATUSfield:PREPARE: The task is waiting to execute.RUNNING: The task is in progress.PENDING: The task is paused.FINISHED: The task is completed.STANDBY: A manually triggered task is waiting to be claimed and executed by a node.QUEUE: The task is queued in a priority queue waiting to execute.CANCEL: The task has been canceled.EXCHANGE: Indexes are being exchanged (this state is specific to automatic partition rebuilding tasks).CLEAN: Resources are being cleaned up (this state is specific to automatic partition rebuilding tasks).
Tasks with the status
FINISHED, whether successful or not, are moved to the history table. For detailed usage examples, see the corresponding view documentation.Cancel a task
To cancel a task, obtain the trace_id from the DBA_OB_VECTOR_INDEX_TASKS or oceanbase.DBA_OB_VECTOR_INDEX_TASKS view, and then execute the following command:
-- Example: fill in the trace_id based on the actual situation. ALTER SYSTEM CANCEL TASK "Y61480BA2D976-00063084E80435E2-0-1";
Special maintenance
Manual forced minor compaction or major compaction (optional)
The minor compaction and major compaction processes for incremental segments are typically automatically triggered by the database system's background modules when specific conditions are met. OceanBase AI Database supports the following manual triggering methods:
- Table-level triggering: Use the
FLUSH_INDEXandCOMPACT_INDEXprocedures in theDBMS_VECTORsystem package to manually trigger minor compaction and major compaction tasks at the table/index granularity. For syntax and examples, see FLUSH_INDEX and COMPACT_INDEX. - Tablet-level triggering: Use the
TRIGGER_ASYNC_TASKstored procedure in theDBMS_VECTORsystem package to manually create a background task for a vector index by specifying the task type andtablet_id. This supports operations such as incremental segment freezing, major compaction, and follower memory loading. For syntax and examples, see TRIGGER_ASYNC_TASK.
After triggering, you can query the DBA_OB_VECTOR_INDEX_TASKS view. A task with TASK_TYPE='INCREMENTAL_INDEX_FREEZE' is a triggered minor compaction task, and a task with TASK_TYPE='INCREMENTAL_INDEX_MERGE' is a triggered major compaction task. If the current incremental data volume is small, the task may complete quickly. In this case, you can check the historical tasks view DBA_OB_VECTOR_INDEX_TASK_HISTORY. If the data volume is large, the major compaction process may take a longer time. You can view the execution progress in real time through the GV$SESSION_LONGOPS view. For query examples and detailed descriptions, see the corresponding view documentation.
Note
The feature to view the progress of segment major compaction tasks is supported starting from V4.6.2.1.
A segment is the basic unit used to organize partitioned memory data in a vector index and is responsible for storing and compacting incremental data. For detailed concept introduction and threshold control methods, see Vector index memory management.
Background task priority scheduling
Note
This feature is supported starting from V4.6.2.1.
All vector background tasks share a single thread pool, and the system schedules them at six priority levels (P0–P5): tasks that affect foreground services have the highest priority, followed by lightweight resource cleanup, then heavy operations at medium priority, and heavy background tasks at the lowest priority. The default priorities and water level thresholds are as follows:
Priority |
Task type |
Default watermark threshold |
|---|---|---|
| P0 | Manually triggered tasks | 100% |
| P1 | IVF cluster center cache loading, in-memory data synchronization | 90% |
| P2 | IVF cache purging, incremental segment freezing | 70% |
| P3 | Embedding computation, incremental segment merging | 50% |
| P4 | Partition-level index rebuilding | 35% |
| P5 | Reserved | 20% |
The water level threshold is the percentage of the total number of threads in the thread pool that is occupied by currently running tasks. Tasks of this priority or lower are scheduled only when the running task water level falls below this threshold. This ensures that high-priority tasks always have available threads and low-priority tasks do not exhaust all threads.
If a certain type of task requires a more aggressive or conservative execution strategy for a specific tenant or period, you can override the default scheduling water level by task type using the parameter vector_task_thread_limit_percent. For example, during business peak hours, you can reduce the scheduling ratio of segment minor compaction and major compaction tasks to alleviate resource contention:
ALTER SYSTEM SET vector_task_thread_limit_percent = 'INCREMENTAL_INDEX_FREEZE:30,INCREMENTAL_INDEX_MERGE:30';
For detailed value formats and task type descriptions, see the corresponding parameter documentation.
Notice
When setting a task type's water level to 0, it is still protected by the minimum slot guarantee and may still be scheduled.
Controlling background task resources via cgroup (optional)
Note
This feature is supported starting from V4.6.2.1.
Vector background tasks have been integrated with function-level resource isolation, allowing CPU and I/O usage to be limited by task type. The corresponding resource request types are as follows:
Resource request type |
Related tasks |
|---|---|
| VECTOR_HIGH | In-memory data loading, IVF cache loading/clearing |
| VECTOR_MID | Incremental segment freezing and major compaction |
| VECTOR_LOW | Partition-level index rebuilding |
For specific configuration steps, see Resource isolation configuration examples for background tasks and Resource isolation overview.
View memory throttling information
Note
This feature is supported starting from V4.6.2.1.
For memory-intensive tasks such as partition-level index rebuilding and incremental segment merging, the scheduler checks whether the tenant's vector memory is sufficient before triggering execution. If the current memory is insufficient, the task is postponed and retained in the queue. It will be executed once other tasks release memory; the task will not be discarded.
After a task completes, the memory throttling information is recorded in the TASK_INFO field of the historical task view DBA_OB_VECTOR_INDEX_TASK_HISTORY (or CDB_OB_VECTOR_INDEX_TASK_HISTORY) in JSON format, including a mem_limit_info section.
Adaptive thread pool mechanism for small tenants
Note
This feature is supported starting from V4.6.2.1.
To prevent small-specification tenants from wasting resources due to excessive background threads, the system adaptively calculates the maximum number of threads in the vector background task thread pool based on the tenant's CPU specification. This section describes its mechanism and calculation formula. The specific formula is as follows:
max_threads = clamp(floor(cpu × 0.5), 2, 64)
The meaning of the calculation parameters is as follows:
Parameters |
Meaning |
|---|---|
| cpu | Number of CPU cores allocated to the tenant |
| cpu × 0.5 | Estimate the number of threads based on half of the CPU count. |
| floor(...) | Round down to get the integer. |
| clamp(..., 2, 64) | Limit the result to between 2 and 64. |
The maximum number of threads corresponding to common tenant specifications is as follows:
Tenant CPU |
Maximum threads |
|---|---|
| 1~5 | 2 |
| 6~7 | 3 |
| 8~9 | 4 |
| 10~11 | 5 |
| 12~13 | 6 |
| 16~17 | 8 |
| 32~33 | 16 |
| 64~65 | 32 |
| 128+ | 64 |
When the tenant's CPU quota changes (such as during online scaling), the thread pool size is automatically refreshed to ensure the number of threads matches the current CPU specification, without requiring manual intervention. Even if the thread pool is small, the system ensures that tasks of all priorities receive basic scheduling opportunities through the minimum slot guarantee mechanism.