Note
This view is available starting with V4.3.0.
Purpose
The DBA_MVREF_STATS view displays basic timing statistics for materialized view refreshes.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| MV_OWNER | VARCHAR2(128) | YES | The owner of the materialized view. |
| 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 | Refresh optimizations. For example, empty refreshes or primary/foreign key operations applied during materialized view refreshes.
NoteThis column is compatible only and the value is NULL by default. |
| ADDITIONAL_EXECUTIONS | VARCHAR2(4000) | NO | Additional executions. For example, index rebuilds or log operations involved during materialized view refreshes.
NoteThis column is compatible only and the 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 compatible only and the 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 initial number of rows in the materialized view (at the start of the refresh). |
| FINAL_NUM_ROWS | NUMBER | NO | The final number of rows in the materialized view (at the end of the refresh). |
Sample query
Query the basic timing statistics for materialized view refreshes.
obclient [SYS]> SELECT * FROM SYS.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 |
+----------+---------------+------------+----------------+-----------------------+-----------------------+------------------------------+------------------------------+--------------+----------------+----------------+------------------+----------------+
| SYS | 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.010 sec)