Analyzing Materialized Views for Fast Refresh

This article shows how materialized views can be analyzed and optimized to ensure they can be FAST REFRESHed. As tools, the DBMS_MVIEW.explain_mview procedure and the MV_CAPABILITIES_TABLE are used. In this particular case, refresh time was reduced from more than 14 hours to less than 2!

Business Problem:

A recent assignment tasked our team with analyzing 12 existing materialized views comprising the reporting engine of an in-house Oracle 9i budgeting application. Upon the first release of the application largest tables had no more than several hundred thousand rows. Thus, all of the materialized views had been created with REFRESH FORCE ON DEMAND and a COMPLETE refresh of all 12 materialized views was taking less than an hour. However, after more than 12 months of larger and more frequent data loads, the same Oracle master tables contained more than several hundred million rows and the total time to refresh all 12 materialized views was more than 14 hours. Based on the scheduling of the data loads the refresh ran daily at 3am leaving the associated Discoverer Reports, built directly off the materialized views, unavailable to the business analysts for up to an entire business day.

Our task was to analyze and enable the materialized view for FAST REFRESH ON DEMAND benchmarking both the individual and total refresh times for the materialized views with a goal of having the materialized views and associated Discoverer reports available to the business analysts by 8am every morning. Further, as the historical data was continuing to being loaded on a daily basis the materialized view refresh times were only going to degrade: the master or underlying tables were now growing at a rate of approximately 3 million rows a week.

Technical Analysis:

Since the original materialized views were defined with the FORCE ON DEMAND the first step was to confirm the refresh methods for each materialized view by querying USER_MVIEWS, in particular:

SQL> SELECT mview_name, refresh_mode, refresh_method,
            last_refresh_type, last_refresh_date
       FROM user_mviews;

We confirmed a COMPLETE REFRESH of all 12 materialized views was occurring. In short, none of the materialized views were being FAST REFRESHED.

Next, we checked for any existing materialized view logs:

SQL> SELECT log_owner, master, log_table
       FROM dba_mview_logs;

LOG_OWNER               MASTER          LOG_TABLE
-------------------	--------------	------------------
SCOTT                   EMP             MLOG$_EMP

In this case, 2 of the 5 required materialized view logs existed. However, neither of the existing materialized view logs met the basic FAST REFRESH requirements defined with ROWID (or PRIMARY KEY). And since 6 of the materialized views included some type of aggregation, namely a SUM, the required INCLUDING NEW VALUES and SEQUENCE clauses were missing along with all columns referenced in the materialized view aggregation definition.

Methodology and Tools

The primary tools used to perform the FAST REFRESH analysis were:

• Oracle provided DBMS_MVIEW.explain_mview procedure
• MV_CAPABILITIES_TABLE table

The Oracle provided DBMS_MVIEW.explain_mview procedure was used to analyze each of the existing materialized views FAST REFRESH capabilities and write the results to the MV_CAPABILITIES_TABLE:

• Lists the general capabilities of materialized view or materialized view query
• Specifies whether each capability is possible (Y or N)
• If a capability is not possible the reason (why) is written to the msgtxt column of the MV_CAPABILITIES_TABLE

The MV_CAPABILITIES TABLE is created in the owner’s schema by running the following Oracle 9i script:

SQL> @ $ORACLE_HOME/rdbms/admin/utlxmv.sql

Steps:

• Created the necessary materialized view logs for each of the master tables making sure to include all columns referenced in any of the materialized view aggregations along with the ROWID, SEQUENCE, and INCLUDING NEW VALUES clauses based on the Oracle 9i documentation FAST REFRESH requirements.

  Note: See Oracle9i Data Warehousing Guide Release 2 9.2 or Oracle Metalink Local Materialized View Fast Refresh Restrictions Note:222843.1.

• Dropped and recreated each of the 12 materialized views using the original DDL including the REFRESH FORCE ON DEMAND option, in order to prepare for the detailed analysis of the respective FAST REFRESH capabilities.

  Please note, the ORA-12034: materialized view log on "SCOTT". younger than last refresh error occurs when a FAST REFRESH is attempted without rebuilding existing materialized views following the creation of the materialized view logs. Additionally, when querying the MV_CAPABILITIES_TABLE after executing the DBMS_MVIEW.explain_mview procedure the MSGTXT for any FAST REFRESH capability name will return “mv log is newer than last full refresh” if, again, the existing materialized view are not rebuilt after creating or rebuilding the respective materialized view logs.

• Iterated through each existing materialized view analyzing and refactoring as necessary to meet the specific FAST REFRESH requirements using the contents of the MV_CAPABILITIES_TABLE MSGTXT column as the guide. For each materialized view the following steps were repeated:
  • Truncated the MV_CAPABILITIES_TABLE to remove any prior analysis written to the table by the DBMS_MVIEW.explain_mview procedure.

    SQL> TRUNCATE TABLE scott.mv_capabilities_table;

  • Executed the dbms_mview.explain_mview() procedure to explain the materialized view:

    SQL> EXEC dbms_mview.explain_mview('scott.EMP_MV');

  • Queried the MV_CAPABILITIES_TABLE for each FAST REFRESH capability until the basic requirements were met as signified by a ‘Y’ in the POSSIBLE column using the contents of the MSGTXT column to provide any specific requirements for FAST REFRESH which were not being met. This was our guide in refactoring each materialized view for FAST REFRESH including any necessary modifications to the materialized view logs:

    SQL> SELECT capability_name,
                possible,
                substr(msgtxt,1,60) AS msgtxt
           FROM scott.mv_capabilities_table
          WHERE capability_name like '%FAST%';

    
    

The above 3 steps were repeated for each materialized view until all 12 were eligible for FAST REFRESH. Afterwards, we benchmarked both the total FAST REFRESH time (as compared to the original 14 hours) and the FAST REFRESH times for the individual materialized views. These benchmarks were compared to the time required to perform a COMPLETE REFRESH: there are some cases where a COMPLETE REFRESH is faster than a FAST REFRESH. In this case the FAST REFRESH for each materialized view was as fast as or dramatically faster than the respective COMPLETE REFRESH.

Conclusion:

Ultimately, none of the materialized views were immediately eligible for FAST REFRESH and all were undergoing a COMPLETE REFRESH due in part to missing or improperly defined materialized view logs. After creating the required materialized view logs (based on the Oracle 9i documentation FAST REFRESH requirements) the DBMS_MVIEW.explain_mview procedure and the MV_CAPABILITIES_TABLE proved to be invaluable and less than 120 hours of analysis and unit testing resulted in refactoring the 12 materialized views for FAST REFRESH dramatically reducing the refresh time from more than 14 hours to less than 2 making the Discoverer Plus reports available to the business analysts and management by 8am.

Example 1: Materialized View Built on View Rewritten for FAST REFRESH

SQL> DROP MATERIALIZED VIEW scott.emp_v_MV;

SQL> CREATE MATERIALIZED VIEW scott.emp_v_MV
NOLOGGING
PARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
select * from emp_v
/
SQL> truncate table mv_capabilities_table;
SQL> exec dbms_mview.explain_mview('scott.emp_v_mv');

SQL> set linesize 100
SQL> SELECT capability_name,  possible, SUBSTR(msgtxt,1,60) AS msgtxt
           FROM mv_capabilities_table
           WHERE capability_name like '%FAST%';

CAPABILITY_NAME                P MSGTXT
------------------------------ - -------------
REFRESH_FAST                   N
REFRESH_FAST_AFTER_INSERT      N named view in FROM list not supported   for this type MV
REFRESH_FAST_AFTER_INSERT      N named view in FROM list not supported for this type MV
REFRESH_FAST_AFTER_INSERT      N view or subquery in from list
REFRESH_FAST_AFTER_INSERT      N the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML  N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML     N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT               N PCT is not possible on any of the detail tables in the mater

SQL> DROP MATERIALIZED VIEW scott.emp_v_MV;

SQL> CREATE MATERIALIZED VIEW scott.emp_v_MV
NOLOGGING
PARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
select * from emp;

SQL> TRUNCATE TABLE mv_capabilities_table;
SQL> EXEC dbms_mview.explain_mview('scott.emp_v_mv');

SQL> SELECT capability_name,  possible, SUBSTR(msgtxt,1,60) AS msgtxt
           FROM mv_capabilities_table
           WHERE capability_name like '%FAST%';

CAPABILITY_NAME                P MSGTXT
------------------------------ - ------------------------------------
REFRESH_FAST                   Y
REFRESH_FAST_AFTER_INSERT      Y
REFRESH_FAST_AFTER_ONETAB_DML  Y
REFRESH_FAST_AFTER_ANY_DML     Y
REFRESH_FAST_PCT               N PCT is not possible on any of the detail tables in the mater

Example 2: Materialized View Aggregation with Required Materialized View Logs:

SQL> CREATE MATERIALIZED VIEW LOG ON scott.emp
WITH SEQUENCE, ROWID (JOB, DEPTNO, SAL)
INCLUDING NEW VALUES;

SQL> CREATE MATERIALIZED VIEW LOG ON scott.dept
WITH SEQUENCE, ROWID (DEPTNO)
INCLUDING NEW VALUES;

SQL> DROP MATERIALIZED VIEW scott.sal_dept_mv;

SQL> CREATE MATERIALIZED VIEW scott.sal_dept_mv
           NOLOGGING
           PARALLEL
           BUILD IMMEDIATE
           REFRESH FORCE ON DEMAND
           ENABLE QUERY REWRITE
           AS
          SELECT e.job, e.deptno, sum(e.sal)
          FROM emp e,
               dept d
          WHERE e.deptno=d.deptno
          GROUP BY e.job, e.deptno;