Note
This view is introduced since OceanBase Database V4.3.0.
Purpose
The DBA_MVREF_STATS view displays the basic timing statistics on each refresh of each materialized view.
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. Valid values:
|
| REFRESH_OPTIMIZATIONS | VARCHAR2(4000) | NO | The refresh optimization, such as a null refresh, or a primary key or foreign key used during the refresh.
NoteThis column is used only for compatibility and is |
| ADDITIONAL_EXECUTIONS | VARCHAR2(4000) | NO | Additional executions, such as an index rebuild or log operations performed during the refresh.
NoteThis column is used only for compatibility and is |
| 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 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 view, in seconds. |
| INITIAL_NUM_ROWS | NUMBER | NO | The initial number of rows in the materialized view when the refresh starts. |
| FINAL_NUM_ROWS | NUMBER | NO | The final number of rows in the materialized view when the refresh ends. |
Sample query
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)