Note
This view is introduced since OceanBase Database V4.3.0.
Purpose
The USER_MVREF_RUN_STATS view displays information about each refresh of materialized views. Each refresh is identified by a refresh ID. The information includes the timing statistics and refresh parameters of each refresh.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| REFRESH_ID | NUMBER | NO | The ID of the refresh. |
| NUM_MVS | NUMBER | NO | The number of materialized views refreshed. |
| MVIEWS | VARCHAR2(4000) | NO | The names of the materialized views specified in the API. |
| BASE_TABLES | VARCHAR2(4000) | YES | For internal use only. |
| METHOD | VARCHAR2(4000) | YES | The METHOD parameter specified in the API. |
| ROLLBACK_SEG | VARCHAR2(4000) | YES | The ROLLBACK_SEG parameter specified in the API. |
| PUSH_DEFERRED_RPC | CHAR(1) | NO | The PUSH_DEFERRED_RPC parameter specified in the API. |
| REFRESH_AFTER_ERRORS | CHAR(1) | NO | The REFRESH_AFTER_ERRORS parameter specified in the API. |
| PURGE_OPTION | NUMBER | NO | The PURGE_OPTION parameter specified in the API. |
| PARALLELISM | NUMBER | NO | The PARALLELISM parameter specified in the API. |
| HEAP_SIZE | NUMBER | NO | The HEAP_SIZE parameter specified in the API. |
| ATOMIC_REFRESH | CHAR(1) | NO | The ATOMIC_REFRESH parameter specified in the API. |
| NESTED | CHAR(1) | NO | The NESTED parameter specified in the API. |
| OUT_OF_PLACE | CHAR(1) | NO | The OUT_OF_PLACE parameter specified in the API. |
| NUMBER_OF_FAILURES | NUMBER | NO | The number of failures occurred in processing the API. |
| START_TIME | TIMESTAMP(6) | NO | The start time of the refresh. |
| END_TIME | TIMESTAMP(6) | NO | The end time of the refresh. |
| ELAPSED_TIME | NUMBER | NO | The duration of the refresh.
|
| LOG_SETUP_TIME | NUMBER | NO | The log setup time for the materialized views, in seconds.
NoteThis column is used only for compatibility and is |
| LOG_PURGE_TIME | NUMBER | NO | The log purge time for the materialized views.
|
| COMPLETE_STATS_AVAILABLE | CHAR(1) | NO | Indicates whether all the complete refresh statistics can be used for this refresh. Valid values:
NoteThis column is used only for compatibility and is |
| MVIEW_ID | NUMBER | YES | The ID of the materialized view.
NoteFor V4.4.2, this column is available starting with V4.4.2 BP2. |
| MVIEW_NAME | VARCHAR2(128) | YES | The name of the materialized view.
NoteFor V4.4.2, this column is available starting with V4.4.2 BP2. |
| DATA_TARGET_SCN | NUMBER | YES | The SCN when the target data was synchronized.
NoteFor V4.4.2, this column is available starting with V4.4.2 BP2. |
| TRACE_ID | VARCHAR2(64) | YES | The trace ID of the refresh task.
NoteFor V4.4.2, this column is available starting with V4.4.2 BP2. |
Sample query
obclient [test_db]> SELECT * FROM SYS.USER_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)