analyzing materialized views for fast refresh _ oracle faq.pdf

8/11/2019 Analyzing Materialized Views for Fast Refresh _ Oracle FAQ.pdf

1/4

7/2/2014 Analyzing Materialized Views for Fast Refresh | Oracle FAQ

http://www.orafaq.com/node/831 1/4

User login

Login: *

Password: *

Logi n

[ Regis ter ] [ Forgotpassw ord ]

Site navigation

About

Blogs

Feed aggregator

Books

Directories

Events

FAQ's

Forums

Mailing Lists

Papers

Scripts

Tools

USENET News

Wiki

XML Feeds

Home Blogs Ellis Miller's blog

Analyzing Materialized Views for Fast Refresh

Submitted by Ellis Miller on Sat, 2006-01-07 06:39 RDBMS ServerThis article shows how materialized views can be analyzedand optimized to ensure they can be FAST REFRESHed. Astools, the DBMS_M VIEW.explain_mview procedure and theMV_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 exis tingmaterialized views comprising the reporting engine of an in-houseOracle 9i budgeting application. Upon the first release of the applicationlargest tables had no more than several hundred thousand rows. Thus,all of the materialized views had been created with REFRES H FORCE ON

DEMAND and a COMPLETE refresh of all 12 materialized views wastaking less than an hour. Howeve r, after more than 12 months of largerand more frequent data loads, th e same Oracle master tables c ontained more than several hundred million rows and the totaltime to refresh all 12 materialize d views was more than 14 hou rs. Based on the scheduling of the data loads the refresh randaily at 3am leaving the associate d Discoverer Reports, built di rectly off the materialized views, unavailable to the businessanalysts for up to an entire busin ess day.

Our task was to analyze and enab le the materialized view for F AST REFRESH ON DEMAND benchmarking both the individual andtotal refresh times for the materialized views with a goal of having the materialized views and associated Discoverer reportsavailable to the business analysts by 8am every morning. Further, as the his torical data was continuing to being loaded on adaily basis the materialized view refresh times were only going to degrade: the master or underlying tables were now growingat a rate of approximately 3 million rows a week.

Technical Analysis:

Since the original materialized views were defined with the FORCE ON DEMAND the firs t step was to confirm the refreshmethods for each materialized view by querying USER_M VIEWS, in particular:

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

We confirmed a COMPLETE REFRES H of all 12 materialized views was occurring. In s hort, none of the materialized views werebeing FAS T 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 metthe basic FAST REFRESH requirements defined with ROWID (or PRIM ARY KEY). And since 6 of the materialized views includedsome type of aggregation, namely a SUM, the required INCLUDING NEW VALUES and SEQUENCE clauses were miss ing along withall 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
http://www.orafaq.com/wiki/XML/RSS_Newsfeedshttp://www.orafaq.com/wiki/XML/RSS_Newsfeedshttp://www.orafaq.com/wiki/Usenethttp://www.orafaq.com/wiki/Usenethttp://www.orafaq.com/wiki/Usenethttp://www.orafaq.com/wiki/Scriptshttp://www.orafaq.com/wiki/Scriptshttp://www.orafaq.com/wiki/Eventshttp://www.orafaq.com/aggregatorhttp://www.orafaq.com/abouthttp://www.orafaq.com/forum/rst/0/http://www.orafaq.com/http://www.orafaq.com/http://www.orafaq.com/http://www.orafaq.com/http://googleads.g.doubleclick.net/aclk?sa=l&ai=C8X7bZJmzU_vmL4qfuATtsoGoDom_rMAEydKlp6YBiOTezTQQASCcx_QBULHfkoUDYOmCgIDkDaABh5DQ3QPIAQKpAqf0wu_m7Ko-qAMByAPBBKoEek_Q-jocLA_55NwYsIT0NYsqVa6jLFG3w7o2zIyT7ihOQgCrjVb93tO-_-QKEVNnueaQHB365HXvK3CEkHgE62s1rlp-cw-b-vS0RL1JXK5xxeqOwUFfrfAbssP9sdRDl0zpTa63jH4BAvj1lq0--CTWp5ATHmwi1ynvoAYCgAfh768i&num=1&sig=AOD64_0saigTolO6sWpzTpEFORYlq7Iz_A&client=ca-pub-7499460687650857&adurl=http://berniaga.com/indonesia/Sepeda%2Bmotor-12000/%3Fxtor%3DSEC-8285-GOO-%5Bgcn_business%5D-%5Bwhite_red_300x250_may%5D-C-%5B%5D&nm=1http://googleads.g.doubleclick.net/aclk?sa=l&ai=C8X7bZJmzU_vmL4qfuATtsoGoDom_rMAEydKlp6YBiOTezTQQASCcx_QBULHfkoUDYOmCgIDkDaABh5DQ3QPIAQKpAqf0wu_m7Ko-qAMByAPBBKoEek_Q-jocLA_55NwYsIT0NYsqVa6jLFG3w7o2zIyT7ihOQgCrjVb93tO-_-QKEVNnueaQHB365HXvK3CEkHgE62s1rlp-cw-b-vS0RL1JXK5xxeqOwUFfrfAbssP9sdRDl0zpTa63jH4BAvj1lq0--CTWp5ATHmwi1ynvoAYCgAfh768i&num=1&sig=AOD64_0saigTolO6sWpzTpEFORYlq7Iz_A&client=ca-pub-7499460687650857&adurl=http://berniaga.com/indonesia/Sepeda%2Bmotor-12000/%3Fxtor%3DSEC-8285-GOO-%5Bgcn_business%5D-%5Bwhite_red_300x250_may%5D-C-%5B%5D&nm=1http://googleads.g.doubleclick.net/aclk?sa=l&ai=C8X7bZJmzU_vmL4qfuATtsoGoDom_rMAEydKlp6YBiOTezTQQASCcx_QBULHfkoUDYOmCgIDkDaABh5DQ3QPIAQKpAqf0wu_m7Ko-qAMByAPBBKoEek_Q-jocLA_55NwYsIT0NYsqVa6jLFG3w7o2zIyT7ihOQgCrjVb93tO-_-QKEVNnueaQHB365HXvK3CEkHgE62s1rlp-cw-b-vS0RL1JXK5xxeqOwUFfrfAbssP9sdRDl0zpTa63jH4BAvj1lq0--CTWp5ATHmwi1ynvoAYCgAfh768i&num=1&sig=AOD64_0saigTolO6sWpzTpEFORYlq7Iz_A&client=ca-pub-7499460687650857&adurl=http://berniaga.com/indonesia/Sepeda%2Bmotor-12000/%3Fxtor%3DSEC-8285-GOO-%5Bgcn_business%5D-%5Bwhite_red_300x250_may%5D-C-%5B%5D&nm=1http://googleads.g.doubleclick.net/aclk?sa=l&ai=C8X7bZJmzU_vmL4qfuATtsoGoDom_rMAEydKlp6YBiOTezTQQASCcx_QBULHfkoUDYOmCgIDkDaABh5DQ3QPIAQKpAqf0wu_m7Ko-qAMByAPBBKoEek_Q-jocLA_55NwYsIT0NYsqVa6jLFG3w7o2zIyT7ihOQgCrjVb93tO-_-QKEVNnueaQHB365HXvK3CEkHgE62s1rlp-cw-b-vS0RL1JXK5xxeqOwUFfrfAbssP9sdRDl0zpTa63jH4BAvj1lq0--CTWp5ATHmwi1ynvoAYCgAfh768i&num=1&sig=AOD64_0saigTolO6sWpzTpEFORYlq7Iz_A&client=ca-pub-7499460687650857&adurl=http://berniaga.com/indonesia/Sepeda%2Bmotor-12000/%3Fxtor%3DSEC-8285-GOO-%5Bgcn_business%5D-%5Bwhite_red_300x250_may%5D-C-%5B%5D&nm=1http://googleads.g.doubleclick.net/aclk?sa=l&ai=C8X7bZJmzU_vmL4qfuATtsoGoDom_rMAEydKlp6YBiOTezTQQASCcx_QBULHfkoUDYOmCgIDkDaABh5DQ3QPIAQKpAqf0wu_m7Ko-qAMByAPBBKoEek_Q-jocLA_55NwYsIT0NYsqVa6jLFG3w7o2zIyT7ihOQgCrjVb93tO-_-QKEVNnueaQHB365HXvK3CEkHgE62s1rlp-cw-b-vS0RL1JXK5xxeqOwUFfrfAbssP9sdRDl0zpTa63jH4BAvj1lq0--CTWp5ATHmwi1ynvoAYCgAfh768i&num=1&sig=AOD64_0saigTolO6sWpzTpEFORYlq7Iz_A&client=ca-pub-7499460687650857&adurl=http://berniaga.com/indonesia/Sepeda%2Bmotor-12000/%3Fxtor%3DSEC-8285-GOO-%5Bgcn_business%5D-%5Bwhite_red_300x250_may%5D-C-%5B%5D&nm=1http://googleads.g.doubleclick.net/aclk?sa=l&ai=C8X7bZJmzU_vmL4qfuATtsoGoDom_rMAEydKlp6YBiOTezTQQASCcx_QBULHfkoUDYOmCgIDkDaABh5DQ3QPIAQKpAqf0wu_m7Ko-qAMByAPBBKoEek_Q-jocLA_55NwYsIT0NYsqVa6jLFG3w7o2zIyT7ihOQgCrjVb93tO-_-QKEVNnueaQHB365HXvK3CEkHgE62s1rlp-cw-b-vS0RL1JXK5xxeqOwUFfrfAbssP9sdRDl0zpTa63jH4BAvj1lq0--CTWp5ATHmwi1ynvoAYCgAfh768i&num=1&sig=AOD64_0saigTolO6sWpzTpEFORYlq7Iz_A&client=ca-pub-7499460687650857&adurl=http://berniaga.com/indonesia/Sepeda%2Bmotor-12000/%3Fxtor%3DSEC-8285-GOO-%5Bgcn_business%5D-%5Bwhite_red_300x250_may%5D-C-%5B%5D&nm=1http://googleads.g.doubleclick.net/aclk?sa=l&ai=C8X7bZJmzU_vmL4qfuATtsoGoDom_rMAEydKlp6YBiOTezTQQASCcx_QBULHfkoUDYOmCgIDkDaABh5DQ3QPIAQKpAqf0wu_m7Ko-qAMByAPBBKoEek_Q-jocLA_55NwYsIT0NYsqVa6jLFG3w7o2zIyT7ihOQgCrjVb93tO-_-QKEVNnueaQHB365HXvK3CEkHgE62s1rlp-cw-b-vS0RL1JXK5xxeqOwUFfrfAbssP9sdRDl0zpTa63jH4BAvj1lq0--CTWp5ATHmwi1ynvoAYCgAfh768i&num=1&sig=AOD64_0saigTolO6sWpzTpEFORYlq7Iz_A&client=ca-pub-7499460687650857&adurl=http://berniaga.com/indonesia/Sepeda%2Bmotor-12000/%3Fxtor%3DSEC-8285-GOO-%5Bgcn_business%5D-%5Bwhite_red_300x250_may%5D-C-%5B%5D&nm=1http://googleads.g.doubleclick.net/aclk?sa=l&ai=C0XaJZJmzU8ztFpSouASQxIHQDvyEqawFzOSauWPAjbcBEAEgnMf0AVC6x4rA-P____8BYOmCgIDkDaABzuSl_gPIAQKoAwHIA8EEqgRzT9BjUD7GqF1ix0wmm4q_9YBOKIgnft-dvX9K3c-slOS1TnKO5IkR6myeFtOIZRZrlcr6NGHJZF00wnwuIdX1qzMcNcBuTE9pTX5mCkx6KxO4ndWH0vTk3h8B8HFTxbLLOCKkCLzJvm4eOfed1IcGTJ9L36AGAoAHmpvaAQ&num=1&sig=AOD64_1787WOPw6sVd3uJ2D3_Ak6uURmGw&client=ca-pub-7499460687650857&adurl=https://www.ibm.com/services/forms/signup.do%3Fsource%3Dswg-BA_WebOrganic%26S_PKG%3Dov7816%26form%3D336%26S_TACT%3D101KR81W%26cmp%3D101KR%26ct%3D101KR81W%26cr%3Dgoogle%26cm%3Dk%26csr%3DBI-Cognos_Enterprise_Demo-Branded-Display%26ccy%3Dus%26ck%3Dibm%2520data%2520mining%26cs%3D%26mkwid%3DclqIX0qff-dc_26650557668_43246d30503&nm=3http://googleads.g.doubleclick.net/aclk?sa=l&ai=C8X7bZJmzU_vmL4qfuATtsoGoDom_rMAEydKlp6YBiOTezTQQASCcx_QBULHfkoUDYOmCgIDkDaABh5DQ3QPIAQKpAqf0wu_m7Ko-qAMByAPBBKoEek_Q-jocLA_55NwYsIT0NYsqVa6jLFG3w7o2zIyT7ihOQgCrjVb93tO-_-QKEVNnueaQHB365HXvK3CEkHgE62s1rlp-cw-b-vS0RL1JXK5xxeqOwUFfrfAbssP9sdRDl0zpTa63jH4BAvj1lq0--CTWp5ATHmwi1ynvoAYCgAfh768i&num=1&sig=AOD64_0saigTolO6sWpzTpEFORYlq7Iz_A&client=ca-pub-7499460687650857&adurl=http://berniaga.com/indonesia/Sepeda%2Bmotor-12000/%3Fxtor%3DSEC-8285-GOO-%5Bgcn_business%5D-%5Bwhite_red_300x250_may%5D-C-%5B%5D&nm=1http://www.orafaq.com/articles/serverhttp://www.orafaq.com/blog/ellis_millerhttp://www.orafaq.com/articleshttp://www.orafaq.com/http://googleads.g.doubleclick.net/aclk?sa=l&ai=C0XaJZJmzU8ztFpSouASQxIHQDvyEqawFzOSauWPAjbcBEAEgnMf0AVC6x4rA-P____8BYOmCgIDkDaABzuSl_gPIAQKoAwHIA8EEqgRzT9BjUD7GqF1ix0wmm4q_9YBOKIgnft-dvX9K3c-slOS1TnKO5IkR6myeFtOIZRZrlcr6NGHJZF00wnwuIdX1qzMcNcBuTE9pTX5mCkx6KxO4ndWH0vTk3h8B8HFTxbLLOCKkCLzJvm4eOfed1IcGTJ9L36AGAoAHmpvaAQ&num=1&sig=AOD64_1787WOPw6sVd3uJ2D3_Ak6uURmGw&client=ca-pub-7499460687650857&adurl=https://www.ibm.com/services/forms/signup.do%3Fsource%3Dswg-BA_WebOrganic%26S_PKG%3Dov7816%26form%3D336%26S_TACT%3D101KR81W%26cmp%3D101KR%26ct%3D101KR81W%26cr%3Dgoogle%26cm%3Dk%26csr%3DBI-Cognos_Enterprise_Demo-Branded-Display%26ccy%3Dus%26ck%3Dibm%2520data%2520mining%26cs%3D%26mkwid%3DclqIX0qff-dc_26650557668_43246d30503&nm=3http://www.orafaq.com/wiki/XML/RSS_Newsfeedshttp://www.orafaq.com/wiki/Main_Pagehttp://www.orafaq.com/wiki/Usenethttp://www.orafaq.com/toolshttp://www.orafaq.com/wiki/Scriptshttp://www.orafaq.com/wiki/Papershttp://www.orafaq.com/wiki/Mailing_listshttp://www.orafaq.com/forumhttp://www.orafaq.com/wiki/Category:Frequently_Asked_Questionshttp://www.orafaq.com/wiki/Eventshttp://www.orafaq.com/linkshttp://www.orafaq.com/bookshttp://www.orafaq.com/aggregatorhttp://www.orafaq.com/articleshttp://www.orafaq.com/abouthttp://www.orafaq.com/forum/rst/0/http://www.orafaq.com/forum/re/0/http://www.orafaq.com/


2/4



MV_CAPABILITIES_TABLE table

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

Lists the general capabilities of materialized view or materialized view querySpecifies whether each capability is possible (Y or N)If a capability is not poss ible the reason (why) is written to the msgtx t column of the MV_CAPABILITIES _TABLE

The MV_CAPABILITIES TABLE is created in the owners 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 columnsreferenced in any of the materialized view aggregations along with the ROWID, S EQUENCE, and INCLUDING NEW VALUESclauses 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 RefreshRestrictions Note:222843.1.

Dropped and recreated each of the 12 materialized views using the original DDL including the REFRESH FORCE ONDEMAND option, in order to prepare for the detailed analysis of the respective FAS T REFRES H capabilities.

Please note, the ORA-12034: materialized view log on " SCOTT" . younger than last refresh error occurs when a FASTREFRESH is attempted without rebuilding exis ting materialized views following the creation of the materialized view

logs. Additionally, when querying the M V_CAPABILITIES_TABLE after executing the DBMS_MVIEW.explain_mviewprocedure the MSGTXT for any FAS T 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 exis ting materialized view analyzing and refactoring as necessary to meet the specific FASTREFRESH requirements using the contents of the MV_CAPABILITIES_TABLE MSGTXT column as the guide. For eachmaterialized view the following steps were repeated:

Truncated the MV_CAPABILITIES_TABLE to remove any prior analysis written to the table by theDBMS_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 assignified by a Y in the POSSIBLE column using the contents of the MSGTXT column to provide any specificrequirements for FAST REFRESH which were not being met. This was our guide in refactoring each materializedview for FAST REFRES H including any necessary modifications to the materialized view logs:

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

The above 3 steps were repeated for each materialized view until all 12 were eligible for FAST REFRESH. Afterwards, webenchmarked both the total FAST REFRESH time (as compared to the original 14 hours) and the FAST REFRESH times for theindividual materialized views. These benchmarks were compared to the time required to perform a COMPLETE REFRESH: thereare some cases where a COMPLETE REFRESH is faster than a FAST REFRESH. In this case the FAST REFRESH for eachmaterialized 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 COMPLETEREFRESH due in part to missing or improperly defined materialized view logs. After creating the required materialized viewlogs (based on the Oracle 9i documentation FAST REFRESH requirements) the DBMS_MVIEW.explain_mview procedure and theMV_CAPABILITIES _TABLE proved to be invaluable and less than 120 hours of analysis and unit testing resulted in refactoringthe 12 materialized views for FAST REFRESH dramatically reducing the refresh time from more than 14 hours to less than 2making the Discoverer Plus reports ava ilable to the business analysts and management by 8am.


3/4



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_MVNOLOGGINGPARALLELBUILD IMMEDIATEREFRESH FORCE ON DEMANDENABLE QUERY REWRITEASselect * from emp_v/SQL> truncate table mv_capabilities_table;SQL> exec dbms_mview.explain_mview('scott.emp_v_mv');

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

CAPABILITY_NAME P MSGTXT------------------------------ - -------------REFRESH_FAST NREFRESH_FAST_AFTER_INSERT N named view in FROM list not supported for this type MVREFRESH_FAST_AFTER_INSERT N named view in FROM list not supported for this type MVREFRESH_FAST_AFTER_INSERT N view or subquery in from listREFRESH_FAST_AFTER_INSERT N the detail table does not have a materialized view logREFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabledREFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabledREFRESH_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_MVNOLOGGINGPARALLELBUILD IMMEDIATEREFRESH FORCE ON DEMANDENABLE QUERY REWRITEASselect * 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_capabiliti es_table WHERE capability_name like '%FAST%';

CAPABILITY_NAME P MSGTXT------------------------------ - ------------------------------------REFRESH_FAST YREFRESH_FAST_AFTER_INSERT YREFRESH_FAST_AFTER_ONETAB_DML YREFRESH_FAST_AFTER_ANY_DML YREFRESH_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.empWITH SEQUENCE, ROWID (JOB, DEPTNO, SAL)INCLUDING NEW VALUES;

SQL> CREATE MATERIALIZED VIEW LOG ON scott.deptWITH 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;

Ellis Miller's blog Login to post comments
http://www.orafaq.com/user/login?destination=node%2F831%23comment-formhttp://www.orafaq.com/blog/ellis_miller


4/4



.:: Blogger Home :: Wiki Home :: Forum Home :: Privacy :: Contact ::.
http://www.orafaq.com/feedbackhttp://www.orafaq.com/wiki/Oracle_FAQ:Privacy_policyhttp://www.orafaq.com/forumhttp://www.orafaq.com/wikihttp://www.orafaq.com/

analyzing materialized views for fast refresh _ oracle faq.pdf

Documents