Performance Tuning   «Prev  Next»

Lesson 8Managing materialized views
ObjectiveDescribe the function of the DBMS_OLAP and DBMS_MVIEW packages.

Managing Materialized Views


Oracle includes two PL/SQL packages that help you to manage materialized views. The two packages are DBMS_OLAP and DBMS_MVIEW.

The DBMS_OLAP package

The DBMS_OLAP package is primarily used for gathering statistics that are used for data warehouses. Two specific procedures in the DBMS_OLAP procedure relate to materialized views:
  1. RECOMMEND_MV: The RECOMMEND_MV package analyzes the statistics of the uniqueness of your tables and columns and recommends which materialized views should be created. These recommendations are based on a set of all possible queries against the tables.
  2. RECOMMEND_MV_W: The RECOMMEND_MV_W procedure performs the same sort of analysis as the RECOMMEND_MV procedure, except that this procedure uses the actual workload of your database in its calculations.
With Oracle Database 10g, the DBMS_OLAP package has been replaced with improved technology. While Oracle recommends you not begin development using DBMS_OLAP, Oracle continues to support DBMS_OLAP, and your existing applications using DBMS_OLAP will continue to work.
If you are developing new or substantially modified applications and had previously used the Summary Advisor in DBMS_OLAP, you should now use the SQL Access Advisor.


The DBMS_OLAP package, presented here for reasons of backward compatibility, provides a collection of materialized view analysis and advisory functions that are callable from any PL/SQL program. Some of the functions generate output tables.
DBMS_OLAP performs seven major functions, which include materialized view strategy recommendation, materialized view strategy evaluation, reporting and script generation, repository management, workload management, filter management, and dimension validation.
To perform materialized view strategy recommendation and evaluation functions, the workload information can either be provided by the user or synthesized by the Advisor engine. In the former case, cardinality information of all tables and materialized views referenced in the workload are required. In the latter case, dimension objects must be present and cardinality information for all dimension tables, fact tables, and materialized views are required. Cardinality information should be gathered with the DBMS_STATS.GATHER_TABLE_STATS procedure. Once these functions are completed, the analysis results can be presented with the reporting and script generation function.
The workload management function handles three types of workload, which are user-specified workload, SQL cache workload, and Oracle Trace workload. To process the user-specified workload, a user-defined workload table must be present in the user's schema. To process Oracle Trace workload, the Oracle Trace formatter must be run to preprocess collected workload statistics into default V-tables in the user's schema.


Several views are created when using DBMS_OLAP. All are in the SYSTEM schema. To access these views, you must have a DBA role.



The DBMS_MVIEW package

DBMS_MVIEW is a synonym for the DBMS_SNAPSHOT package. The package contains a number of procedures that mainly apply to the use of snapshots of a database and two that can be useful with materialized views:
  1. REFRESH_ALL_MVIEWS: The REFRESH_ALL_MVIEWS procedure refreshes all the materialized views that are registered. This procedure refreshes materialized views only where the data in the base tables has been changed because the materialized view was refreshed.
  2. REFRESH_DEPENDENT: The REFRESH_DEPENDENT view is used to refresh all materialized views that are dependent on a particular table. The same conditions for refreshing materialized views apply to this procedure as do to the REFRESH_ALL_MVIEWS procedure.

The following SlideShow demonstrates the use of some of these functions:

1) Mview Recommendation 1 2) Mview Recommendation 2 3) Mview Recommendation 3 4) Mview Recommendation 4

Program 1 Program 2 Program 2 Program 2
Managing Materialized Views
The next lesson is the module conclusion.