Note
This view is introduced since OceanBase Database V4.3.0.
Purpose
The DBA_MVREF_STATS view displays the basic timing statistics on each refresh of each materialized view.
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 optimization, such as a null refresh, or a primary key or foreign key used during the refresh.
NoteThis column is used only for compatibility and is |
| ADDITIONAL_EXECUTIONS | text | NO | Additional executions, such as an index rebuild or log operations performed during the refresh.
NoteThis column is used only for compatibility and is |
| 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 log setup time for the materialized view, in seconds.
NoteThis column is used only for compatibility and is |
| LOG_PURGE_TIME | bigint(20) | NO | The log purge time for the materialized view, in seconds. |
| INITIAL_NUM_ROWS | bigint(20) | NO | The initial number of rows in the materialized view when the refresh starts. |
| FINAL_NUM_ROWS | bigint(20) | NO | The final number of rows in the materialized view when the refresh ends. |
Sample query
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)