Note
This view is available starting with V4.3.0.
Purpose
The USER_MVREF_STATS view displays the basic timing statistics of a materialized view refresh.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| MV_NAME | VARCHAR2(128) | YES | The name of the materialized view. |
| REFRESH_ID | NUMBER | NO | The ID of the refresh. |
| REFRESH_METHOD | VARCHAR2(30) | NO | The method used to refresh the materialized view.
|
| REFRESH_OPTIMIZATIONS | VARCHAR2(4000) | NO | The refresh optimizations. For example, empty refresh or primary key and foreign key constraints applied during the refresh of the materialized view.
NoteThis column is reserved and its value is NULL by default. |
| ADDITIONAL_EXECUTIONS | VARCHAR2(4000) | NO | Additional executions. For example, index rebuild or log operations involved in the refresh of the materialized view.
NoteThis column is reserved and its value is NULL by default. |
| 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, in seconds. |
| LOG_SETUP_TIME | NUMBER | NO | The time taken to set up the materialized view log, in seconds
NoteThis column is reserved and its value is 0 by default. |
| LOG_PURGE_TIME | NUMBER | NO | The time taken to purge the materialized view log, in seconds. |
| INITIAL_NUM_ROWS | NUMBER | NO | The number of rows in the materialized view at the start of the refresh. |
| FINAL_NUM_ROWS | NUMBER | NO | The number of rows in the materialized view at the end of the refresh. |
Sample query
obclient [SYS]> SELECT * FROM SYS.USER_MVREF_STATS;
The query result is as follows:
+---------------+------------+----------------+-----------------------+-----------------------+------------------------------+------------------------------+--------------+----------------+----------------+------------------+----------------+
| 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 |
+---------------+------------+----------------+-----------------------+-----------------------+------------------------------+------------------------------+--------------+----------------+----------------+------------------+----------------+
| MV1_TEST_TBL1 | 825195 | FAST | NULL | NULL | 20-JAN-24 07.16.27.668694 PM | 20-JAN-24 07.16.27.726144 PM | 0 | 0 | 0 | 0 | 0 |
+---------------+------------+----------------+-----------------------+-----------------------+------------------------------+------------------------------+--------------+----------------+----------------+------------------+----------------+
1 row in set (0.029 sec)