This topic describes how to use TRACE_ID in OceanBase AI Database to analyze CPU usage, I/O operations, wait events, and time-consuming SQL statements during a materialized view refresh when the refresh takes a long time. You can combine SQL Audit, ASH, refresh history, and runtime status views for analysis.
Prerequisites
Before you perform the operations described in this topic, confirm the following conditions:
- Permission settings: The user logging into the tenant must have permissions such as
CREATE TABLE,INSERT, andSELECT. For more information about permission settings, see Grant privileges directly. - You have identified the name of the materialized view to be checked, or you know the approximate time range for the corresponding refresh task.
Limitations
View |
Note |
|---|---|
| GV$OB_SQL_AUDIT | This view is a circular buffer with a limited retention period. Earlier refresh records may have been overwritten. |
| GV$OB_ACTIVE_SESSION_HISTORY | Applicable to the analysis of recently or currently running tasks, with a shorter retention period. |
| DBA_WR_ACTIVE_SESSION_HISTORY | Applicable to historical analysis, typically with a retention period longer than ASH. |
Procedure overview
We recommend that you troubleshoot the issue in the following order:
- Obtain the
TRACE_IDof the materialized view being refreshed. - Aggregate and analyze CPU and I/O metrics in SQL Audit based on the
TRACE_ID. - View SQL statements that consume high resources during the refresh.
- Identify the specific phase of the refresh.
- For long-running refreshes, use ASH or WR to analyze wait events.
- If the refresh task is still in progress, view its real-time status.
- To assess overall resource usage, view the aggregated I/O statistics for the materialized view refresh.
Preparation example
The following SQL in this topic uses the materialized view mv_order_daily as an example. When you actually execute it, replace the materialized view name, TRACE_ID, refresh_id, and mview_id in the text according to your environment.
Step 1: Create base tables
Create a partitioned order detail table with daily partitions. The table contains an order_date DATE column:
obclient> CREATE TABLE order_detail (
order_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
order_date DATE NOT NULL,
order_time DATETIME NOT NULL,
channel VARCHAR(32) DEFAULT 'web',
PRIMARY KEY (order_id, order_time)
)
PARTITION BY RANGE(TO_DAYS(order_time)) (
PARTITION p202601 VALUES LESS THAN(TO_DAYS('2026-02-01')),
PARTITION p202602 VALUES LESS THAN(TO_DAYS('2026-03-01')),
PARTITION p202603 VALUES LESS THAN(TO_DAYS('2026-04-01'))
);
Step 2: Insert initial data
obclient> INSERT INTO order_detail VALUES
(1001, 10001, 20001, 99.50, '2026-01-15', '2026-01-15 10:30:00', 'web'),
(1002, 10002, 20001, 129.00, '2026-01-15', '2026-01-15 11:00:00', 'app'),
(1003, 10001, 20003, 45.00, '2026-01-15', '2026-01-15 14:20:00', 'web'),
(1004, 10003, 20002, 310.00, '2026-01-16', '2026-01-16 09:15:00', 'app'),
(1005, 10002, 20001, 88.00, '2026-01-16', '2026-01-16 16:45:00', 'web'),
(1006, 10004, 20003, 56.50, '2026-01-17', '2026-01-17 10:00:00', 'web'),
(1007, 10001, 20002, 210.00, '2026-02-10', '2026-02-10 13:30:00', 'app'),
(1008, 10003, 20001, 67.00, '2026-02-10', '2026-02-10 15:00:00', 'web'),
(1009, 10002, 20003, 430.00, '2026-02-11', '2026-02-11 09:45:00', 'app'),
(1010, 10004, 20002, 125.00, '2026-02-20', '2026-02-20 14:00:00', 'web');
Step 3: Create a materialized view
Create an incremental (FAST) refresh materialized view summarized by day, channel, and product:
obclient> CREATE MATERIALIZED VIEW mv_order_daily (PRIMARY KEY(order_date, channel, product_id))
REFRESH FAST ON DEMAND
AS
SELECT
order_date,
channel,
product_id,
COUNT(*) AS order_cnt,
COUNT(amount) AS count_amount,
SUM(amount) AS total_amount
FROM order_detail
GROUP BY order_date, channel, product_id;
Step 4: Write changed data to the base table
After the materialized view is created, continue to insert new data into the base table to generate incremental changes:
obclient> INSERT INTO order_detail VALUES
(2001, 10005, 20001, 78.00, '2026-02-21', '2026-02-21 10:30:00', 'app'),
(2002, 10006, 20002, 199.00, '2026-02-21', '2026-02-21 11:00:00', 'web'),
(2003, 10001, 20003, 56.00, '2026-02-21', '2026-02-21 14:20:00', 'app'),
(2004, 10002, 20001, 340.00, '2026-02-22', '2026-02-22 09:15:00', 'web'),
(2005, 10003, 20002, 88.00, '2026-02-22', '2026-02-22 16:45:00', 'app');
Step 5: Execute materialized view refresh
obclient> CALL dbms_mview.refresh('mv_order_daily', 'F');
Note
The second parameter F indicates a FAST refresh, and C indicates a COMPLETE refresh. After the refresh is executed, OceanBase AI Database assigns a unique TRACE_ID for this refresh. Subsequent steps are based on this ID.
Step 6: Obtain internal IDs of the materialized view
The join query in Step 4 of the Procedure section requires mview_id and refresh_id. You can obtain them through the following queries:
Obtain
mview_id:obclient> SELECT MVIEW_ID, MVIEW_NAME FROM oceanbase.DBA_MVREF_RUN_STATS WHERE MVIEW_NAME = 'mv_order_daily';Obtain the most recent
refresh_id:obclient> SELECT refresh_id, trace_id, mviews, start_time, end_time, elapsed_time FROM oceanbase.DBA_MVREF_RUN_STATS ORDER BY start_time DESC LIMIT 5;
Procedure
Step 1: Obtain the TRACE_ID for the materialized view refresh
Each time a materialized view refresh is executed, OceanBase AI Database assigns a unique TRACE_ID to that refresh. Internal SQL statements executed during the refresh carry the same TRACE_ID.
Method 1: Obtain the TRACE_ID for the most recent refresh by materialized view name
To view the resource consumption of the most recent refresh for a specific materialized view, execute the following SQL:
obclient> SELECT TRACE_ID, START_TIME, END_TIME, MVIEW_NAME
FROM oceanbase.DBA_MVREF_RUN_STATS
WHERE MVIEW_NAME = 'mv_order_daily';
Explanation of the return result:
TRACE_ID: The trace identifier for the refresh task.START_TIME: The start time of the refresh operation.END_TIME: The end time of the refresh operation.MVIEW_NAME: The name of the materialized view.
Record the TRACE_ID from the query result for use in the subsequent step. In the following example steps, YXX-AB12CD34EF56-0001 is used as the TRACE_ID.
Method 2: Obtain the TRACE_ID from the refresh history
To troubleshoot a historical refresh, you can execute the following SQL:
obclient> SELECT
refresh_id,
trace_id,
mviews,
start_time,
end_time,
elapsed_time,
parallelism
FROM oceanbase.DBA_MVREF_RUN_STATS
ORDER BY start_time DESC
LIMIT 20;
Explanation of the return result:
refresh_id: The refresh task ID.trace_id: TheTRACE_IDfor this refresh.mviews: The materialized views involved.start_time,end_time: The start and end times of the refresh.elapsed_time: The time consumed by the refresh.parallelism: The parallelism of the refresh.
If you need to further associate refresh step information, also record the refresh_id. If you need to associate specific materialized views later, you will also need to obtain the corresponding mview_id.
Step 2: Aggregate and analyze the CPU and I/O consumption of this refresh
After obtaining the TRACE_ID, you can use GV$OB_SQL_AUDIT to aggregate the resource consumption of all SQL statements during this refresh.
obclient> SELECT
TRACE_ID,
COUNT(*) AS stmt_count,
SUM(ELAPSED_TIME) AS total_elapsed_us,
SUM(EXECUTE_TIME) AS total_execute_us,
SUM(USER_IO_WAIT_TIME) AS total_io_wait_us,
SUM(APPLICATION_WAIT_TIME) AS total_app_wait_us,
SUM(CONCURRENCY_WAIT_TIME) AS total_concurrency_wait_us,
SUM(SCHEDULE_TIME) AS total_schedule_us,
SUM(DISK_READS) AS total_disk_reads,
SUM(DATA_BLOCK_READ_CNT) AS total_data_block_reads,
SUM(DATA_BLOCK_CACHE_HIT) AS total_data_block_cache_hit,
SUM(INDEX_BLOCK_READ_CNT) AS total_index_block_reads,
SUM(INDEX_BLOCK_CACHE_HIT) AS total_index_block_cache_hit,
SUM(MEMSTORE_READ_ROW_COUNT) AS total_memstore_rows,
SUM(SSSTORE_READ_ROW_COUNT) AS total_ssstore_rows,
SUM(AFFECTED_ROWS) AS total_affected_rows,
SUM(RETURN_ROWS) AS total_return_rows,
SUM(REQUEST_MEMORY_USED) AS total_memory_used,
SUM(RPC_COUNT) AS total_rpc_count
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE TRACE_ID = 'YXX-AB12CD34EF56-0001'
GROUP BY TRACE_ID;
Parameter description:
Replace <trace_id> in the WHERE clause with the actual value obtained in Step 1.
Explanation of the return result:
Field |
Note |
Recommended focus areas |
|---|---|---|
| stmt_count | Number of SQL Statements | May be larger in parallel refresh scenarios. |
| total_elapsed_us | Total Time (μs) | This can serve as a reference for the overall duration. |
| total_execute_us | Execution Time Summary | It can serve as a reference metric for CPU consumption. |
| total_io_wait_us | Summary of User I/O Wait Time | A higher value typically indicates more I/O waits. |
| total_disk_reads | Physical Disk Reads | Reflects the pressure of physical reads. |
| total_data_block_reads | Data Block Reads | This can be analyzed in conjunction with cache hit rates. |
| total_memstore_rows | Rows Read from MemTable | It can be used to analyze hot data read patterns. |
| total_ssstore_rows | Rows Read from SSTable | It can be used to analyze the scan volume of baseline data. |
| total_rpc_count | Number of RPC Calls | This can serve as a reference for distributed overhead. |
| total_memory_used | Request Memory Usage | This is suitable for observing scenarios with high memory consumption. |
Usage suggestions:
- If
total_io_wait_usaccounts for a relatively high proportion of the total time, it usually indicates that the main time consumption is concentrated in I/O waits. - If
total_execute_usaccounts for a relatively high proportion, it usually indicates that the main time consumption is concentrated in the computation and execution phases. - If
total_disk_readsandtotal_data_block_readsare relatively high, it usually indicates that a large amount was scanned or read during the refresh.
Sample output:
+----------------------+------------+----------------+----------------+----------------+------------------+
| TRACE_ID | stmt_count | total_elapsed | total_execute | total_io_wait | total_disk_reads |
+----------------------+------------+----------------+----------------+----------------+------------------+
| YXX-AB12CD34EF56-0001| 12 | 325000000 | 180000000 | 120000000 | 15420 |
+----------------------+------------+----------------+----------------+----------------+------------------+
In actual troubleshooting, prioritize SQL statements with high ELAPSED_TIME rankings. Also, analyze the resource overhead of these SQL statements by considering the fields USER_IO_WAIT_TIME, DISK_READS, and TABLE_SCAN. A preliminary judgment is that this refresh involved both computation and I/O overhead.
Considerations:
If this query returns no results, it may be because the corresponding SQL audit record has been overwritten. In this case, it is recommended to refer to the WR analysis method described in Step 5, or analyze it in conjunction with the refresh step details described in Step 4.
Step 3: Locate SQL statements with high resource consumption
To further identify which specific SQL statement consumes a large amount of resources during the refresh, execute the following SQL:
obclient> SELECT
SQL_ID,
QUERY_SQL,
ELAPSED_TIME,
EXECUTE_TIME,
USER_IO_WAIT_TIME,
DISK_READS,
DATA_BLOCK_READ_CNT,
AFFECTED_ROWS,
RETURN_ROWS,
REQUEST_MEMORY_USED,
PLAN_TYPE,
TABLE_SCAN
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE TRACE_ID = 'YXX-AB12CD34EF56-0001'
ORDER BY ELAPSED_TIME DESC;
Explanation of return results:
SQL_ID: The SQL identifier.QUERY_SQL: The SQL text.ELAPSED_TIME: The SQL execution time.EXECUTE_TIME: The execution time.USER_IO_WAIT_TIME: The I/O wait time.DISK_READS: The number of physical reads.DATA_BLOCK_READ_CNT: The number of block reads.REQUEST_MEMORY_USED: The memory usage.PLAN_TYPE: The execution plan type.TABLE_SCAN: Whether a table scan is involved.
Usage suggestions:
- Prioritize SQL statements with high
ELAPSED_TIMErankings. - You can determine which phase of the refresh the corresponding SQL statement belongs to based on the
QUERY_SQL, for example,INSERT,MERGE, orSELECT. - When
TABLE_SCAN = 1, pay special attention to whether a large-scale scan exists. - When both
DISK_READSandDATA_BLOCK_READ_CNTare relatively high, further analyze the physical read and cache hit situations.
Sample output:
+-------------+------------------------------------------+--------------+--------------+----------------+------------+--------------------+------------+
| SQL_ID | QUERY_SQL | ELAPSED_TIME | EXECUTE_TIME | USER_IO_WAIT | DISK_READS | DATA_BLOCK_READ_CNT| TABLE_SCAN |
+-------------+------------------------------------------+--------------+--------------+----------------+------------+--------------------+------------+
| C2F8A1B20 | INSERT /*+ monitor */ INTO mv_order_daily| 210000000 | 120000000 | 80000000 | 10240 | 15360 | 1 |
| 7D3E9A1C50 | SELECT /*+ monitor */ COUNT(*) ... | 85000000 | 45000000 | 35000000 | 4096 | 6144 | 1 |
| A1B2C3D4E0 | MERGE INTO mv_order_daily USING ... | 30000000 | 15000000 | 5000000 | 1084 | 2048 | 0 |
+-------------+------------------------------------------+--------------+--------------+----------------+------------+--------------------+------------+
As shown in the sample output, the first-ranked INSERT statement takes 210 seconds, accounting for 65% of the total time. Moreover, TABLE_SCAN = 1 and DISK_READS = 10240, indicating that this INSERT operation involves a large-scale scan and is the resource consumption bottleneck for this refresh. Subsequently, you can associate it with the refresh step details in Step 4 to confirm the refresh step number corresponding to this SQL statement.
Step 4: Locate the specific phase by combining the refresh step details
If you need to view the step numbers, complete SQL text, and execution time during the refresh process, you can perform correlation analysis between the refresh step details table and SQL audit results.
obclient> SELECT
S.step,
S.sqlid,
S.execution_time,
S.stmt,
A.ELAPSED_TIME,
A.EXECUTE_TIME,
A.USER_IO_WAIT_TIME,
A.DISK_READS,
A.DATA_BLOCK_READ_CNT
FROM oceanbase.DBA_MVREF_STMT_STATS S
LEFT JOIN oceanbase.GV$OB_SQL_AUDIT A
ON S.sqlid = A.SQL_ID
WHERE S.refresh_id = 1025
AND S.mview_id = 1100611139
ORDER BY S.step;
Parameter description:
<refresh_id>: The refresh task ID (example value: 1025), which can be obtained from Method 1 or Method 2.<mview_id>: The internal ID of the materialized view (example value: 1100611139), which can be obtained by executingSELECT mview_id FROM oceanbase.DBA_MVREF_RUN_STATS.
Response parameters:
step: The step number of the refresh.sqlid: The SQL ID corresponding to the step.execution_time: The execution time of this step.stmt: The complete SQL text.- Fields such as
ELAPSED_TIMEandUSER_IO_WAIT_TIME: The resource statistics from the corresponding SQL audit.
Usage suggestions:
- Sort by
stepto restore the refresh execution order. stmtis usually more complete thanQUERY_SQLin SQL audit results, making it suitable for verifying specific execution logic.- If the associated
Auditfield is empty, it typically indicates that the corresponding SQL audit record has been overwritten.
Step 5: Analyze wait events for long-running refreshes
For refresh tasks that take a long time to run, we recommend analyzing the distribution of wait events using ASH or WR.
Method 1: Use ASH to analyze recent or ongoing refreshes
obclient> SELECT
SQL_ID,
EVENT,
WAIT_CLASS,
COUNT(*) AS sample_count,
SUM(CASE WHEN WAIT_CLASS IS NULL OR WAIT_CLASS = '' THEN 1 ELSE 0 END) AS on_cpu_samples,
SUM(CASE WHEN WAIT_CLASS = 'USER_IO' OR WAIT_CLASS = 'User I/O' THEN 1 ELSE 0 END) AS io_wait_samples
FROM oceanbase.GV$OB_ACTIVE_SESSION_HISTORY
WHERE TRACE_ID = 'YXX-AB12CD34EF56-0001'
GROUP BY SQL_ID, EVENT, WAIT_CLASS
ORDER BY sample_count DESC;
Response parameters:
sample_count: The number of samples.on_cpu_samples: Can be used as a reference for CPU active time.io_wait_samples: Can be used as a reference for I/O wait time.EVENT, WAIT_CLASS: The wait event and wait class.
Usage suggestions:
- Each sample point typically represents about 1 second of active time.
- You can determine the primary wait event based on
sample_count. - You can combine
SQL_IDto analyze the wait characteristics of different refresh steps.
Method 2: Use WR to analyze historical refreshes
If the target refresh exceeds the retention period of ASH, you can execute the following SQL:
obclient> SELECT
SQL_ID,
EVENT_ID,
TIME_WAITED,
COUNT(*) AS sample_count
FROM oceanbase.DBA_WR_ACTIVE_SESSION_HISTORY
WHERE TRACE_ID = 'YXX-AB12CD34EF56-0001'
GROUP BY SQL_ID, EVENT_ID, TIME_WAITED
ORDER BY sample_count DESC;
Note
WR is suitable for historical analysis, but the result granularity depends on the WR sampling and retention strategy.
Step 6: View the status of a running refresh task
If a refresh task has not finished, you can view its status and current session information.
1. View a running refresh task
obclient> SELECT
SVR_IP,
SVR_PORT,
TABLE_NAME,
JOB_TYPE,
SESSION_ID,
PARALLEL,
JOB_START_TIME
FROM oceanbase.DBA_MVIEW_RUNNING_JOBS;
Response parameters:
TABLE_NAME: The name of the materialized view.SESSION_ID: The session ID corresponding to the refresh task.PARALLEL: The degree of parallelism.JOB_START_TIME: The start time.
2. View the current wait event of the refresh session
obclient> SELECT *
FROM oceanbase.GV$OB_PROCESSLIST
WHERE ID = 3221225472;
Parameter description:
Replace <session_id> with the session ID obtained from the previous query (example value 3221225472).
Response parameters:
EVENT: The current wait event.STATE: The current state.WAIT_TIME_MICRO: The amount of time already spent waiting.TIME_REMAINING_MICRO: The estimated remaining waiting time.
3. View the resource consumption of the running refresh
If you have obtained the TRACE_ID of the current refresh, you can reuse the SQL Audit aggregate query from Step 2 to view the resource consumption up to the current point in time.
Step 7: View aggregated I/O statistics for all materialized view refreshes
To view the overall I/O usage for all materialized view refreshes, use the following views.
Note
Aggregated statistics are suitable for capacity assessment and are not suitable for analyzing individual materialized views.
1. View I/O statistics by feature type
obclient> SELECT
SVR_IP,
SVR_PORT,
TENANT_ID,
FUNCTION_NAME,
MODE,
SIZE,
REAL_IOPS,
REAL_MBPS,
SCHEDULE_US,
IO_DELAY_US,
TOTAL_US
FROM oceanbase.GV$OB_FUNCTION_IO_STAT
WHERE FUNCTION_NAME LIKE '%MVIEW%';
Note
Materialized view refreshes are typically counted as MVIEW-related functional types at the I/O layer.
2. View I/O statistics by consumer group
obclient> SELECT
TENANT_ID,
SVR_IP,
SVR_PORT,
GROUP_ID,
GROUP_NAME,
MODE,
MIN_IOPS,
MAX_IOPS,
REAL_IOPS,
NORM_IOPS,
MAX_NET_BANDWIDTH_DISPLAY,
REAL_NET_BANDWIDTH_DISPLAY
FROM oceanbase.GV$OB_GROUP_IO_STAT
WHERE GROUP_NAME LIKE '%MVIEW%';
3. View resource management statistics
obclient> SELECT
CON_ID,
GROUP_ID,
NAME,
VALUE,
VALUE_TYPE
FROM oceanbase.GV$OB_RES_MGR_SYSSTAT
WHERE GROUP_ID = 1001;
Parameter description:
Replace <group_id> with the actual materialized view-related resource group ID (example value: 1001).
Step 8: View refresh history and change scale
Refresh history information and base table change scale provide context for resource analysis, for example, explaining why a particular refresh took longer or consumed more I/O.
1. View the time spent on a single refresh and changes in row count
obclient> SELECT
MV_OWNER,
MV_NAME,
REFRESH_ID,
REFRESH_METHOD,
START_TIME,
END_TIME,
ELAPSED_TIME,
LOG_PURGE_TIME,
INITIAL_NUM_ROWS,
FINAL_NUM_ROWS
FROM oceanbase.DBA_MVREF_STATS
ORDER BY END_TIME DESC
LIMIT 20;
Response parameters:
ELAPSED_TIME: The time spent on the refresh.INITIAL_NUM_ROWS: The number of rows before the refresh.FINAL_NUM_ROWS: The number of rows after the refresh.
Considerations:
LOG_PURGE_TIME is deprecated. In newer versions, this column may always be NULL.
2. View historical records by refresh operation
obclient> SELECT
RUN_OWNER,
REFRESH_ID,
NUM_MVS,
MVIEWS,
BASE_TABLES,
METHOD,
PARALLELISM,
START_TIME,
END_TIME,
ELAPSED_TIME
FROM oceanbase.DBA_MVREF_RUN_STATS
ORDER BY START_TIME DESC
LIMIT 20;
Response parameters:
NUM_MVS: The number of materialized views involved.MVIEWS: The materialized views involved.BASE_TABLES: The base tables involved.METHOD: The refresh method.PARALLELISM: The degree of parallelism.
3. View the number of changed rows in base tables
obclient> SELECT
TBL_OWNER,
TBL_NAME,
MV_OWNER,
MV_NAME,
NUM_ROWS_INS,
NUM_ROWS_UPD,
NUM_ROWS_DEL,
NUM_ROWS
FROM oceanbase.DBA_MVREF_CHANGE_STATS
WHERE REFRESH_ID = 1025;
Parameter description:
Replace <refresh_id> with the target refresh task ID (for example, 1025).
Response parameters:
NUM_ROWS_INS: The number of rows inserted.NUM_ROWS_UPD: The number of rows updated.NUM_ROWS_DEL: The number of rows deleted.NUM_ROWS: The total number of changed rows.
Usage suggestion:
A significant increase in NUM_ROWS typically indicates that a larger volume of data needed to be processed during the refresh, which may lead to increased refresh time and I/O consumption.
Considerations
GV$OB_ACTIVE_SESSION_HISTORYapplies to recent or currently running tasks;DBA_WR_ACTIVE_SESSION_HISTORYis more suitable for historical analysis.- If parallel execution is enabled for a refresh task, SQL Audit may record multiple parallel subtasks, and
stmt_countmay be significantly higher than the actual number of refresh steps. - For a running refresh task, its
TRACE_IDcan be obtained from the refresh runtime statistics or related logs. - View names, field names, and return results may vary across different compatibility modes or versions. Please refer to the actual environment.