Note
This view is available starting with V4.3.0.
Purpose
The DBA_MVREF_STATS view displays the basic timing statistics of materialized view refreshes.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| MV_OWNER | varchar(128) | NO | The owner of the materialized view. |
| MV_NAME | varchar(128) | NO | The name of the materialized view. |
| REFRESH_ID | bigint(20) | NO | The ID of the refresh. |
| REFRESH_METHOD | varchar(30) | NO | The method used to refresh the materialized view. Valid values:
|
| REFRESH_OPTIMIZATIONS | text | NO | The refresh optimizations. For example, the primary key or foreign key constraints applied during a refresh or empty refresh.
NoteThis column is only compatible with the OceanBase Database V4.3.0 and later. The value is NULL by default. |
| ADDITIONAL_EXECUTIONS | text | NO | The additional executions. For example, the log operations involved in index rebuilding or a refresh of the materialized view.
NoteThis column is only compatible with the OceanBase Database V4.3.0 and later. The value is NULL by default. |
| START_TIME | datetime | NO | The start time of the refresh. |
| END_TIME | datetime | NO | The end time of the refresh. |
| ELAPSED_TIME | bigint(20) | NO | The duration of the refresh, in seconds. |
| LOG_SETUP_TIME | bigint(1) | NO | The time taken to set up the logs for the materialized view, in seconds
NoteThis column is only compatible with the OceanBase Database V4.3.0 and later. The value is 0 by default. |
| LOG_PURGE_TIME | bigint(20) | NO | The time taken to purge the logs for the materialized view, in seconds. |
| INITIAL_NUM_ROWS | bigint(20) | NO | The number of rows in the materialized view at the start of the refresh. |
| FINAL_NUM_ROWS | bigint(20) | NO | The number of rows in the materialized view at the end of the refresh. |
Sample query
Query the basic timing statistics of a materialized view refresh.
obclient [test_db]> SELECT * FROM oceanbase.DBA_MVREF_STATS;
The query result is as follows:
+----------+---------------+------------+----------------+-----------------------+-----------------------+---------------------+---------------------+--------------+----------------+----------------+------------------+----------------+
| MV_OWNER | MV_NAME | REFRESH_ID | REFRESH_METHOD | REFRESH_OPTIMIZATIONS | ADDITIONAL_EXECUTIONS | START_TIME | END_TIME | ELAPSED_TIME | LOG_SETUP_TIME | LOG_PURGE_TIME | INITIAL_NUM_ROWS | FINAL_NUM_ROWS |
+----------+---------------+------------+----------------+-----------------------+-----------------------+---------------------+---------------------+--------------+----------------+----------------+------------------+----------------+
| test_db | mv1_tbl1_tbl2 | 64670 | COMPLETE | NULL | NULL | 2024-01-17 16:26:28 | 2024-01-17 16:26:28 | 0 | 0 | 0 | 0 | 0 |
| test_db | mv2_tbl1_tbl2 | 64672 | COMPLETE | NULL | NULL | 2024-01-17 16:26:28 | 2024-01-17 16:26:28 | 0 | 0 | 0 | 0 | 0 |
+----------+---------------+------------+----------------+-----------------------+-----------------------+---------------------+---------------------+--------------+----------------+----------------+------------------+----------------+
2 rows in set (0.009 sec)