Note
This view is available starting with V4.3.0.
Overview
The DBA_MVREF_RUN_STATS view displays information about each refresh operation of a materialized view, with each operation identified by a REFRESH_ID. This information includes timing statistics related to the operation and the parameters specified in that operation.
Columns
Field |
Type |
Nullable |
Description |
|---|---|---|---|
| RUN_OWNER | varchar(128) | NO | Owner of the refresh operation (the user who initiated the operation). |
| REFRESH_ID | bigint(20) | NO | Refresh ID |
| NUM_MVS | bigint(20) | NO | Number of Materialized Views Refreshed |
| MVIEWS | text | NO | The name of the materialized view specified by the API |
| BASE_TABLES | text | NO | For Internal Use Only |
| METHOD | text | YES | METHOD parameter specified in the API |
| ROLLBACK_SEG | text | YES | ROLLBACK_SEG parameter specified by API |
| PUSH_DEFERRED_RPC | varchar(1) | NO | PUSH_DEFERRED_RPC parameter specified in the API |
| REFRESH_AFTER_ERRORS | varchar(1) | NO | The REFRESH_AFTER_ERRORS parameter specified in the API |
| PURGE_OPTION | bigint(20) | YES | PURGE_OPTION parameter specified by the API |
| PARALLELISM | bigint(20) | NO | PARALLELISM parameter specified by the API |
| HEAP_SIZE | bigint(20) | YES | The HEAP_SIZE parameter specified by the API |
| ATOMIC_REFRESH | varchar(1) | NO | The ATOMIC_REFRESH parameter specified in the API |
| NESTED | varchar(1) | NO | NESTED parameter specified by the API |
| OUT_OF_PLACE | varchar(1) | NO | OUT_OF_PLACE parameter specified by the API |
| NUMBER_OF_FAILURES | bigint(20) | NO | Number of failures in processing APIs |
| RESULT | bigint(20) | YES | Refresh result (error code).
Note
|
| START_TIME | datetime | NO | Start time of the refresh run |
| END_TIME | datetime | NO | End time of the refresh operation |
| 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.
|
| COMPLETE_STATS_AVAILABLE | varchar(1) | NO | Indicates whether all complete statistics refreshes are available for this run.
NoteThis field is only compatible with and its default value is Y. |
| MVIEW_ID | bigint(20) | YES | The materialized view ID.
Note
|
| MVIEW_NAME | varchar(128) | NO | The name of the materialized view.
Note
|
| DATA_TARGET_SCN | bigint(20) unsigned | YES | The target data synchronization SCN.
Note
|
| TRACE_ID | varchar(64) | YES | The trace ID of the refresh task.
Note
|
Sample query
Queries the information about each refresh of a materialized view, with each refresh identified by a REFRESH_ID.
obclient [test_db]> SELECT * FROM oceanbase.DBA_MVREF_RUN_STATS;
The query result is as follows:
+-----------+------------+---------+-----------------------+-------------+--------+--------------+-------------------+----------------------+--------------+-------------+-----------+----------------+--------+--------------+--------------------+---------------------+---------------------+--------------+----------------+----------------+--------------------------+
| RUN_OWNER | REFRESH_ID | NUM_MVS | MVIEWS | BASE_TABLES | METHOD | ROLLBACK_SEG | PUSH_DEFERRED_RPC | REFRESH_AFTER_ERRORS | PURGE_OPTION | PARALLELISM | HEAP_SIZE | ATOMIC_REFRESH | NESTED | OUT_OF_PLACE | NUMBER_OF_FAILURES | START_TIME | END_TIME | ELAPSED_TIME | LOG_SETUP_TIME | LOG_PURGE_TIME | COMPLETE_STATS_AVAILABLE |
+-----------+------------+---------+-----------------------+-------------+--------+--------------+-------------------+----------------------+--------------+-------------+-----------+----------------+--------+--------------+--------------------+---------------------+---------------------+--------------+----------------+----------------+--------------------------+
| root | 64670 | 1 | test_db.mv1_tbl1_tbl2 | NULL | NULL | NULL | N | N | 0 | 0 | 0 | N | N | N | 0 | 2024-01-17 16:26:28 | 2024-01-17 16:26:28 | 0 | 0 | 0 | Y |
| root | 64672 | 1 | test_db.mv2_tbl1_tbl2 | NULL | NULL | NULL | N | N | 0 | 0 | 0 | N | N | N | 0 | 2024-01-17 16:26:28 | 2024-01-17 16:26:28 | 0 | 0 | 0 | Y |
+-----------+------------+---------+-----------------------+-------------+--------+--------------+-------------------+----------------------+--------------+-------------+-----------+----------------+--------+--------------+--------------------+---------------------+---------------------+--------------+----------------+----------------+--------------------------+
2 rows in set (0.015 sec)