Notice
- This method applies to OceanBase Database V4.1. To query the index progress, you must use the sys tenant account.
- For more information about how to query the index progress in OceanBase Database of a version from V1.4.x to V3.2.x, see Query the index status in OceanBase Database V1.4.x to V3.2.x.
GV$SESSION_LONGOPS view
If you use OceanBase Database V4.1.0 or later, you can view the time-consuming tasks of OceanBase Database in the GV$SESSION_LONGOPS view. The GV$SESSION_LONGOPS view provides information about the stage and progress of index creation. The following table describes the fields in the view.
| Field | Type | Description |
|---|---|---|
| SID | bigint(20) | The ID of the session. |
| OPNAME | varchar(128) | The name of the DDL operation. |
| TARGET | varchar(128) | The DDL operation object. |
| SVR_IP | varchar(46) | The IP address of the server. |
| SVR_PORT | bigint(20) | The port on the server. |
| START_TIME | bigint(20) | The start time. |
| ELAPSED_SECONDS | DECIMAL(24,4) | The time consumed. |
| TIME_REMAINING | bigint(20) | The estimated remaining time. |
| LAST_UPDATE_TIME | bigint(20) | The record update time. |
| MESSAGE | varchar(500) | The supplementary information. |
| TRACE_ID | varchar(64) | The trace ID. |
View the index creation process
Index data completion is the most time-consuming stage in index creation. In some environments, the most time in index creation may be spent in waiting for the concurrent transactions to complete or end.
Waiting for transaction to end
The index creation task is waiting for the transaction to end. Here is the sample command:
obclient> select * from oceanbase.gv$session_longops \G
*************************** 1. row ***************************
SID: -1
TRACE_ID: YE5186458A15C-0005EF63AE10FBBB-0-0
OPNAME: create index
TARGET: __idx_500005_i1
SVR_IP:
SVR_PORT: 58648
START_TIME: 2023-06-09
ELAPSED_SECONDS: 7
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2023-06-09
MESSAGE: TENANT_ID: 1004, TASK_ID: 2, STATUS: WAIT TRANS END, PENDING_TX_ID: 76
When the transaction is waiting for completion, the MESSAGE field prints WAIT TRANS END and the transaction ID of the first uncompleted transaction, such as PENDING_TX_ID: 76. You can then query the transaction information in the __all_virtual_trans_stat table by using the trans_id field.
Data completion
You can use the row count metric to determine the overall progress of an index creation task.
Here is the sample code:
obclient> select * from oceanbase.gv$session_longops \G
*************************** 1. row ***************************
SID: -1
TRACE_ID: YFDE80BA2DA8D-0005FDA8116C1F4E-0-0
OPNAME: create index
TARGET: 500097
SVR_IP: 127.1
SVR_PORT: 65000
START_TIME: 2023-06-09 17:10:42
ELAPSED_SECONDS: 5
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2023-06-09 17:10:48
MESSAGE: TENANT_ID: 1004, TASK_ID: 541, STATUS: REPLICA BUILD, ROW_SCANNED: 2000000, ROW_SORTED: 4000000, ROW_INSERTED: 503316
1 row in set (0.03 sec)
Fields in GV$SESSION_LONGOPS
The following table describes the fields for an index creation task.
| Field | Description |
|---|---|
| TRACE_ID | The ID of the OBServer program log, which is used to search for related log files. |
| OPNAME | The operation name. The value is create index for an index creation task. |
| TARGET | The operation object. The value is the name of the index being created for an index creation task. |
| SVR_IP | The IP address of the OBServer node on which the index creation task is executed. |
| SVR_PORT | The port number of the OBServer node on which the index creation task is executed. |
| START_TIME | The start date of index creation. This field is compatible with Oracle databases. |
| ELAPSED_SECONDS | The time consumed for creating the index in seconds. |
| TIME_REMAINING | The estimated remaining time. The field is compatible with Oracle databases and is currently not supported. |
| LAST_UPDATE_TIME | The date when statistics are collected. This field is compatible with Oracle databases. |
| MESSAGE | The specific information about the index task, which includes the following fields:
|
Row count metric in the index data completion phase
Data completion can be divided into three main stages. The following table describes the row count metrics in these stages.
| Stage | Row count metric | Description |
|---|---|---|
| Primary table scanning | ROW_SCANNED | - |
| Sorting | ROW_SORTED | The sorting stage may involve multiple rounds of compaction. Therefore, the value of ROW_SORTED is usually larger than those of ROW_SCANNED and ROW_INSERTED. The stage also takes a longer time. |
| Data writing to the index table | ROW_INSERTED | - |
Based on the following fields in the preceding sample code: ROW_SCANNED: 2000000, ROW_SORTED: 4000000, and ROW_INSERTED: 503316, the following conclusions can be made:
The primary table scanning and sorting stages in data completion have been completed, and the task is now in the last stage, namely, data writing to the index table.
The total number of data rows is 2,000,000, and the number of rows written to the index table is 503,316, reaching a progress of 503316/2000000, which is about 25%.
