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.
|
| REFRESH_OPTIMIZATIONS | text | NO | The refresh optimizations. For example, empty refresh or the primary key or foreign key applied during the materialized view refresh.
NoteThis column is only compatible with the preceding syntax and the value is NULL by default. |
| ADDITIONAL_EXECUTIONS | text | NO | The additional executions. For example, index rebuild or log operations involved in the materialized view refresh.
NoteThis column is only compatible with the preceding syntax and 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 materialized view logs, in seconds
NoteThis column is only compatible with the preceding syntax and the value is 0 by default. |
| LOG_PURGE_TIME | bigint(20) | NO | The time taken to purge the materialized view logs, 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 materialized view refreshes.
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)