Note
This view is available starting with V4.3.0.
Description
The DBA_MVREF_STATS view displays basic timing statistics for materialized view refreshes.
Columns
Field |
Type |
Nullable |
Description |
|---|---|---|---|
| MV_OWNER | varchar(128) | NO | Owner of the materialized view |
| MV_NAME | varchar(128) | NO | Materialized View Name |
| REFRESH_ID | bigint(20) | NO | Refresh ID |
| REFRESH_METHOD | varchar(30) | NO | The method used to refresh the materialized view. Valid values:
|
| REFRESH_OPTIMIZATIONS | text | NO | The primary key or foreign key of the application during an empty refresh or a materialized view refresh.
NoteThis field is only compatible with and its default value is NULL. |
| ADDITIONAL_EXECUTIONS | text | NO | Additional operations. For example, log operations involved during index rebuilding or materialized view refreshes.
NoteThis field is only compatible and its default value is NULL. |
| START_TIME | datetime | NO | Refresh Start Time |
| END_TIME | datetime | NO | Refresh End Time |
| ELAPSED_TIME | bigint(20) | NO | The length of time for which the refresh will run.
|
| LOG_SETUP_TIME | bigint(1) | NO | Log retention period for the materialized view, in seconds
NoteThis field is only compatible with and its default value is 0. |
| LOG_PURGE_TIME | bigint(20) | YES | The log cleanup time for the materialized view.
|
| INITIAL_NUM_ROWS | bigint(20) | NO | Initial Number of Rows in the Materialized View (at the Start of Refresh) |
| FINAL_NUM_ROWS | bigint(20) | NO | Final row count in the materialized view (at the end of the refresh) |
| NUM_STEPS | bigint(20) | NO | Indicates the total number of steps in this refresh.
Note
|
| RESULT | bigint(20) | NO | Indicates the execution result code of this refresh task. A value of 0 indicates success.
Note
|
| REFRESH_SCN | bigint(20) unsigned | YES | The SCN at which the refresh starts.
Note
|
| SVR_IP | varchar(64) | YES | The IP address of the OBServer node that performed the refresh.
Note
|
| SVR_PORT | bigint(20) | YES | The port number of the OBServer node that performs the refresh.
Note
|
Sample query
Query the basic timing statistics for 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)