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, empty refresh or the application of primary keys or foreign keys during materialized view refreshes.
NoteThis column is reserved and its value is NULL by default. |
| ADDITIONAL_EXECUTIONS | text | NO | The additional executions. For example, index rebuild or log operations involved in materialized view refreshes.
NoteThis column is reserved and its 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 reserved and its 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 initial number of rows in the materialized view (at the start of the refresh). |
| FINAL_NUM_ROWS | bigint(20) | NO | The final 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)