Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Configuring for Materialized Views

Oracle Tips by Burleson Consulting


Oracle materialized views are one of the single most important SQL tuning tools and they are a true silver bullet, allowing you to pre-join complex views and pre-compute summaries for super-fast response time. 

I've devoted over a hundred pages to SQL tuning with Oracle materialized views in my book "Oracle Tuning: The Definitive Reference", and also see "Oracle Replication" a deeply-technical book on creating and managing materialized views.

Also see my notes now how to identify opportunities for Oracle Materialized Views.


Introduction to Oracle materialized views

Oracle materialized views perform miracles in our goal to reduce repetitive I/O.  You want tips on tuning materialized views internal performance, see:

Oracle materialized views were first introduced in Oracle8, and in Oracle materialized views were enhanced to allow very fast dynamic creation of complex objects. Oracle materialized views allow sub-second response times by pre-computing aggregate information, and Oracle dynamically rewrites SQL queries to reference existing Oracle materialized views. In this article, we continue our discussion of Oracle materialized views and discuss how to set up and configure your Oracle database to use this powerful new feature. We begin with a look at the initialization parameters and continue with details of the effective management and use of Oracle materialized views.

Without Oracle materialized views you may see unnecessary repeating large-table full-table scans, as summaries are computed, over and over:


Prerequisites for using Oracle materialized views

In order to use Oracle  materialized views, the Oracle DBA must set special initialization parameters and grant special authority to the users of Oracle materialized views. You start by setting these initialization parameters within Oracle to enable the mechanisms for Oracle materialized views and query rewrite, as shown here:

  • trustedAssumes that the Oracle materialized view is current.
  • enforced (default):  Always goes to Oracle materialized view with fresh data.
     
  • stale_tolerated:  Uses Oracle materialized view with both stale and fresh data

Next, you must grant several system privileges to all users who will be using the Oracle materialized views. In many cases, the Oracle DBA will encapsulate these grant statements into a single role and grant the role to the end users:

grant query rewrite to scott;
grant create materialized view to scott;
alter session set query_rewrite_enabled = true;


Invoking SQL query rewrite

Once Oracle materialized views have been enabled, Oracle provides several methods for invoking query rewrite. Query rewrite is generally automatic, but you can explicitly enable it by using Isession, alter system, or SQL hints: 

  • ALTER {SESSION|SYSTEM} DISABLE QUERY REWRITE
  • Select /*+REWRITE(mv1)*/...

Refreshing materialized views

In Oracle, if you specify REFRESH FAST for a single-table aggregate Oracle materialized view, you must have created a materialized view log for the underlying table, or the refresh command will fail. When creating an Oracle materialized view, you have the option of specifying whether the refresh occurs manually (ON DEMAND) or automatically (ON COMMIT, DBMS_JOB). To use the fast warehouse refresh facility, you must specify the ON DEMAND mode. To refresh the Oracle materialized view, call one of the procedures in DBMS_MVIEW.

The DBMS_MVIEW package provides three types of refresh operations: 

  • DBMS_MVIEW.REFRESH:  Refreshes one or more Oracle materialized views
  • DBMS_MVIEW.REFRESH_ALL_MVIEWS:  Refreshes all Oracle materialized views
  • DBMS_MVIEW.REFRESH_DEPENDENT:  Refreshes all table-based Oracle materialized views

Manual complete refresh

A complete refresh occurs when the Oracle materialized view is initially defined, unless it references a prebuilt table, and a complete refresh may be requested at any time during the life of the Oracle materialized view. Because the refresh involves reading the detail table to compute the results for the Oracle materialized view, this can be a very time-consuming process, especially if huge amounts of data need to be read and processed.

Manual fast (incremental) refresh

If you specify REFRESH FAST (which means that only deltas performed by UPDATE, INSERT, DELETE on the base tables will be refreshed), Oracle performs further verification of the query definition to ensure that fast refresh can always be performed if any of the detail tables change. These additional checks include the following: 

  • An Oracle materialized view log must be present for each detail table.
  • The RowIDs of all the detail tables must appear in the SELECT list of the MVIEW query definition.
  • If there are outer joins, unique constraints must be placed on the join columns of the inner table.

You can use the DBMS_MVIEW package to manually invoke either a fast refresh or a complete refresh, where F equals Fast Refresh and C equals Complete Refresh:

EXECUTE DBMS_MVIEW.REFRESH('emp_dept_sum','F');

Automatic fast refresh of materialized views

The automatic fast refresh feature is completely new in Oracle, so you can refresh a snapshot with DBMS_JOB in a short interval according to the snapshot log. With Oracle, it's possible to refresh automatically on the next COMMIT performed at the master table. This ON COMMIT refreshing can be used with materialized views on single-table aggregates and materialized views containing joins only. ON COMMIT MVIEW logs must be built as ROWID logs, not as primary-key logs. For performance reasons, it's best to create indexes on the ROWIDs of the MVIEW. Note that the underlying table for the MVIEW can be prebuilt.

Below is an example of an Oracle materialized view with an ON COMMIT refresh.

CREATE MATERIALIZED VIEW
   empdep
ON PREBUILT TABLE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
  AS SELECT empno, ename, dname, loc,
         e.rowid emp_rowid,
         d.rowid dep_rowid
    FROM emp e, dept d
   WHERE e.deptno = d.deptno;

Creating an Oracle materialized view

To see all the steps in the creation of a materialized view, let?s take it one step at a time. The code for each step is shown here:

Step 1

optimizer_mode = choose, first_rows, or all_rows
job_queue_interval = 3600
job_queue_processes = 1
query_rewrite_enabled = true
query_rewrite_integrity = enforced
compatible = 8.1.5.0.0 (or greater)

Step 2

CREATE MATERIALIZED VIEW emp_sum
ENABLE QUERY REWRITE
AS SELECT deptno,job,SUM(sal)
      FROM emp
GROUP BY deptno,job
  PCTFREE 5
  PCTUSED 60
  NOLOGGING PARALLEL 5
  TABLESPACE users
    STORAGE (INITIAL 50K NEXT 50K)
    USING INDEX STORAGE (INITIAL 25K NEXT 25K)
  REFRESH FAST
  START WITH SYSDATE
NEXT SYSDATE + 1/12;

Step 3

execute dbms_stats_gather_schemna_stats('SCOTT','ESTIMATE');
execute dbms_mview.refresh('emp_sum');

Step 4

set autotrace on explain
SELECT deptno, job, SUM(sal)
  FROM emp
GROUP BY deptno, job;
Execution Plan
-----------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMP_SUM'

Step 5

CREATE MATERIALIZED VIEW LOG ON
   emp_sum
WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON
   dept
WITH ROWID;

Step 6

EXECUTE DBMS_MVIEW.REFRESH('emp_sum');

  1. Set the initialization parameters and bounce the database.
  2. Create the materialized view table. Here, we specify that the materialized view will be refreshed every two hours with the refresh fast option. Instead of using DBMS_MVIEW, you can automatically refresh the MVIEW (Snapshot) using Oracle DBMS_JOB Management.
  3. Create the optimizer statistics and refresh the materialized view.
  4. Test the materialized view.
  5. Create the MVIEW log(s) MATERIALIZED VIEW.
  6. Execute a manual complete refresh.


Monitoring materialized views

Oracle provides information in the data dictionary to monitor the behavior of Oracle materialized views. When you?re monitoring Oracle materialized views, it?s critical that you check the refresh interval in the dba_jobs view. Here is a SQL statement to check the generated job status for Oracle materialized views:



Conclusion

Oracle materialized views are quite complex in nature and require a significant understanding to be used effectively. In this article, I covered the required set-up methods and the steps for creating Oracle materialized views and appropriate refresh intervals.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.