Example 9-1 Setting Materialized View Refresh Statistics Collection Level for the Database

This example sets the default collection level for materialized view refresh statistics to ADVANCED indicating that detailed statistics about materialized view refresh operations will be collected and stored.

DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT ('COLLECTION_LEVEL','ADVANCED');

Example 9-2 Disabling Statistics Collection for Materialized View Refresh

This example sets the default collection level for materialized view refresh statistics to NONE thereby disabling statistics collection.

DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT ('COLLECTION_LEVEL','NONE');

Example 9-3 Setting the Materialized View Statistics Collection Level for the Entire Database

The following example modifies the collection level for materialized view refresh statistics at the database level to TYPICAL. Specifying NULL instead of one or more materialized view names indicates that this setting is for the entire database.

DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS (NULL, 'TYPICAL');

Example 9-4 Setting the Materialized View Statistics Collection Level for Multiple Materialized Views

This example sets the collection level for the materialized views SALES_2013_MV and SALES_2014_MV in the SH schema to ADVANCED. The retention period is set to 60 days. This setting overrides any default settings that may have been specified at the database level.

DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS ('SH.SALES_2013_MV, SH.SALES_2014_MV','ADVANCED',60);

Example 9-5 Setting the Retention Period for Materialized View Refresh Statistics

This example sets the default retention period for materialized view refresh statistics for the entire database to 60 days.

DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT ('RETENTION_PERIOD',60);

Example 9-6 Preventing the Purging of Materialized View Refresh Statistics

This example sets the retention period for materialized view refresh statistics to -1 thereby ensuring that refresh statistics are not automatically purged when the default retention period is reached. When you use this setting, refresh statistics will need to be explicitly purged from the data dictionary using the DBMS_MVIEW_STATS.PURGE_REFRESH_STATS procedure.

DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT ('RETENTION_PERIOD',–1);

Example 9-7 Using Default Materialized View Refresh Statistics Settings for Retention Period

This example sets the collection level for the materialized view SALES_MV in the SH schema to TYPICAL. Since NULL is used for the retention period, the system-wide default setting for retention period is used for this materialized view.

DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS ('SH.SALES_MV','TYPICAL',NULL);

Example 9-8 Setting the Retention Period for a Materialized View

This example sets the collection level for the SH.SALES_MV to ADVANCED and the retention period to 45 days. This overrides the existing retention period set for this materialized view.

DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS ('SH.SALES_MV','ADVANCED',45);

Example 9-13 Displaying Basic Statistics for a Materialized View Refresh Operation

The following query displays some refresh statistics for refresh operations on the SH.NEW_SALES_RTMV materialized view. Information includes the refresh method, refresh time, number of rows in the materialized view at the start of the refresh operation, and number of rows at the end of the refresh operation.

SELECT refresh_id, refresh_method, elapsed_time, initial_num_rows, final_num_rows
FROM dba_mvref_stats
WHERE mv_name = 'NEW_SALES_RTMV' and mv_owner = 'SH';

REFRESH_ID    REFRESH_METHOD    ELAPSED_TIME    INITIAL_NUM_ROWS    FINAL_NUM_ROWS
----------    --------------    -------------   ----------------    ----------------
49            FAST              0               766                 788
61            FAST              1               788                 788
81            FAST              1               788                 798

3 rows selected.

Example 9-14 Displaying Materialized Views Based on their Refresh Times

The following example displays the names of materialized views whose refresh operations took more than 10 minutes. Since elapsed_time is specified in seconds, we use 600 in the query.

SELECT mv_owner, mv_name, refresh_method
FROM dba_mvref_stats
WHERE elapsed_time > 600;

Example 9-15 Listing All Materialized Views Refreshed in a Single Refresh Operation

The following example displays the materialized views and refresh times for materialized views that were refreshed as part of the specified refresh ID.

SELECT mviews, elapsed_time, complete_stats_available
FROM dba_mvref_run_stats
WHERE refresh_id = 100;

MVIEWS              ELAPSED_TIME      COMPLETE_STATS_AVAIALBE
--------            ------------      -------------------------
"SH"."SALES_RTMV"   1                 Y

Example 9-16 Viewing the Parameters Specified During a Materialized View Refresh Operation

The following example displays the list of refreshed materialized views and some of the parameters specified during the refresh operation for refresh ID 81.

SELECT mviews, refresh_after_errors, purge_option, parallelism, nested
FROM dba_mvref_run_stats
WHERE run_owner = 'SH' and refresh_id=81;

MVIEWS              	R     PURGE_OPTION    PARALLELISM   NESTED
------              	-     ------------    ------------  -------
"SH"."SALES_RTMV"	N	1		0	N

Example 9-17 Displaying Detailed Statistics for a Materialized View Refresh Operation

The following example displays detailed statistics for the refresh operation with refresh ID 156. The details include the number of materialized views refreshed, the owner and names of materialized views, and the time taken for the refresh.

SELECT num_mvs, mv_owner, mv_name, r.elapsed_time
FROM dba_mvref_stats s, dba_mvref_run_stats r
WHERE s.refresh_id = r.refresh_id and refresh_id = 156;

NUM_MVS     MV_OWNER    MV_NAME			ELAPSED_TIME
--------    --------    --------		-----------
1           SH          SALES_RTMV		5

Example 9-18 Determining if a Refresh Operation Resulted in PMOPs

The following example displays the base table names and PMOP details for the refresh operation with refresh ID 1876. The query output contains one record for each base table of the materialized view.

SELECT tbl_name, mv_name, pmops_occurred, pmop_details
FROM dba_mvref_change_stats
WHERE refresh_id =1876;

TBL_NAME      MV_NAME     PMOPS_OCCURRED    PMOP_DETAILS
---------     --------    --------------    ------------
MY_SALES      SALES_RTMV    N	

Example 9-19 Displaying the Number of Rows Modified During a Refresh Operation

This example displays the following details about each base table in a refresh operation on the SH.MY_SALES materialized view: number of rows in the tables, number of rows inserted, number of rows updates, number of rows deleted, number of direct load inserts, and details of PMOP operations.

SELECT tbl_name, num_rows, num_rows_ins, num_rows_upd, num_rows_del, num_rows_dl_ins, pmops_occurred, pmop_details
FROM dba_mvref_change_stats
WHERE mv_name = 'MY_SALES' and mv_owner = 'SH';

Example 9-20 Displaying SQL Statements for Each Step in a Refresh Operation

The following example displays the materialized view names, SQL statements used to refresh the materialized view, and execution time for the materialized view refresh operation with refresh ID is 1278.

SELECT mv_name, step, stmt, execution_time
FROM dba_mvref_stmt_stats
WHERE refresh_id = 1278;

Example 9-21 Displaying Refresh Statements Used in the Current Refresh of an Materialized View

This example displays the individual SQL statements that are used to the refresh the MY_SALES materialized view. A single refresh operation may consist of multiple steps, each of which executes a SQL statement. The details displayed in this example include the step number, SQL ID of the SQL statement, the SQL statement that is executed, and the execution time for the SQL statement.

SELECT step, sqlid, stmt, execution_time
FROM DBA_MVREF_STATS M, DBA_MVREF_STMT_STATS S
WHERE M.refresh_id = S.refresh_id and M.mv_name = 'MY_SALES'
ORDER BY step;