|Oracle8i Data Warehousing Guide
Release 2 (8.1.6)
Part Number A76994-01
This chapter can help you create and manage a data warehouse, and includes:
To help you select from among the many materialized views that are possible in your schema, Oracle provides a collection of materialized view analysis and advisory functions in the DBMS_OLAP package. These functions are callable from any PL/SQL program.
From within the DBMS_OLAP package, several facilities are available to:
Whenever the summary advisor is run, with the exception of reporting the size of a materialized view, the results are placed in a table in the database which means that they can be queried, thereby saving the need to keep running the advisor process.
The advisory functions of the DBMS_OLAP package require you to gather structural statistics about fact table cardinalities, dimension table cardinalities, and the distinct cardinalities of every dimension LEVEL column, JOIN KEY column, and fact table key column. This can be accomplished by loading your data warehouse, then gathering either exact or estimated statistics with the DBMS_STATS package or the ANALYZE TABLE statement. Because gathering statistics is time-consuming and extreme statistical accuracy is not required, it is generally preferable to estimate statistics. The advisor cannot be used if no dimensions have been defined, which is a good reason for ensuring that some time is spent creating them.
Optionally, if you have purchased the Oracle Enterprise Manager Performance Pack, then you may also run Oracle Trace to gather dynamic information about your query work load, which can then be used by an advisory function. If Oracle Trace is available, serious consideration should be given to collecting materialized view usage. Not only does it enable the DBA to see which materialized views are in use, but it also means that the advisor may detect some unusual query requests from the users which would result in recommending some different materialized views.
Oracle Trace gathers the following work load statistics for the analysis of materialized views:
Oracle Trace includes two new point events for collecting runtime statistics about materialized views: one event that records the selected materialized view names at request execution time, and another event that records the estimated benefit and ideal materialized view at compile time. You can log just these two events for materialized view analysis if desired, or you can join this information with other information collected by Oracle Trace, such as the SQL text or the execution time of the request, if other Trace events are also collected. A collection option in the Oracle Trace Manager GUI provides a way to collect materialized view management statistics.
To collect and analyze the summary event set, you must do the following:
For further information on these parameters, refer to the Oracle Trace Users Guide.
V_192216243_F_5_E_14_8_1 and V_192216243_F_5_E_15_8_1. The workload tables should be located in the same schema that the subsequent workload analysis will be performed in. Alternatively, the collection file, which usually has an extension of .CDF, can be formatted manually using otrcfmt. A manual collection command is illustrated below:
Once these four steps have been completed, you will be ready to make recommendations about your materialized views.
The analysis and advisory functions for materialized views are RECOMMEND_MV and RECOMMEND_MV_W in the DBMS_OLAP package. These functions automatically recommend which materialized views to create, retain, or drop.
You can call these functions to obtain a list of materialized view recommendations that you can select, modify, or reject. Alternatively, you can use the DBMS_OLAP package directly in your PL/SQL programs for the same purpose.
The summary advisor will not be able to recommend summaries if the following conditions are not met:
Four parameters are required to use these functions:
A typical call to the package, where the main fact table is called FACT, would be:
No workload statistics are used in this example.
The results from calling this package are put in the table MVIEWS$_RECOMMENDATIONS. The contents of this table can be queried or they can be displayed using the SQL file sadvdemo.sql. The output from calling this procedure is the same irrespective of whether the workload statistics are used.
The recommendations can be viewed by calling the procedure DEMO_SUMADV.PRETTYPRINT_RECOMMENDATIONS, but first you need to run sadvdemo.sql. It is suggested that SET SERVEROUTPUT ON SIZE 900000 be used to ensure that all the information can be displayed. A sample recommendation that resulted from calling this package is shown below.
Recommended Action is DROP existing summary GROCERY.QTR_STORE_PROMO_SUM Storage in bytes is 196020 Percent performance gain is null Benefit-to-cost ratio is null
Recommended Action is RETAIN existing summary GROCERY.STORE_SUM Storage in bytes is 21 Percent performance gain is null Benefit-to-cost ratio is null
To call the package and use the workload statistics, the only difference is the procedure name that is called. For example, instead of recommend_mv, it's recommend_mv_w.
Recommendation Number = 3 Recommended Action is CREATE new summary: SELECT PROMOTION.PROMOTION_KEY, STORE.STORE_KEY, STORE.STORE_NAME, STORE.DISTRICT, STORE.REGION , COUNT(*), SUM(FACT.CUSTOMER_COUNT), COUNT(FACT.CUSTOMER_COUNT), SUM(FACT.DOLLAR_COST), COUNT(FACT.DOLLAR_COST), SUM(FACT.DOLLAR_SALES), COUNT(FACT.DOLLAR_SALES), MIN(FACT.DOLLAR_SALES), MAX(FACT.DOLLAR_SALES), SUM(FACT.RANDOM1), COUNT(FACT.RANDOM1), SUM(FACT.RANDOM2), COUNT(FACT.RANDOM2), SUM(FACT.RANDOM3), COUNT(FACT.RANDOM3), SUM(FACT.UNIT_SALES), COUNT(FACT.UNIT_SALES) FROM GROCERY.FACT, GROCERY.PROMOTION, GROCERY.STORE WHERE FACT.PROMOTION_KEY = PROMOTION.PROMOTION_KEY AND FACT.STORE_KEY = STORE.STORE_KEY GROUP BY PROMOTION.PROMOTION_KEY, STORE.STORE_KEY, STORE.STORE_NAME, STORE.DISTRICT, STORE.REGION Storage in bytes is 257999.999999976 Percent performance gain is .533948057298649 Benefit-to-cost ratio is .00000206956611356085
Recommended Action is CREATE new summary: SELECT STORE.REGION, TIME.QUARTER, TIME.YEAR , COUNT(*), SUM(FACT.CUSTOMER_COUNT), COUNT(FACT.CUSTOMER_COUNT), SUM(FACT.DOLLAR_COST), COUNT(FACT.DOLLAR_COST), SUM(FACT.DOLLAR_SALES), COUNT(FACT.DOLLAR_SALES), MIN(FACT.DOLLAR_SALES), MAX(FACT.DOLLAR_SALES), SUM(FACT.RANDOM1), COUNT(FACT.RANDOM1), SUM(FACT.RANDOM2), COUNT(FACT.RANDOM2), SUM(FACT.RANDOM3), COUNT(FACT.RANDOM3), SUM(FACT.UNIT_SALES), COUNT(FACT.UNIT_SALES) FROM GROCERY.FACT, GROCERY.STORE, GROCERY.TIME WHERE FACT.STORE_KEY = STORE.STORE_KEY AND FACT.TIME_KEY = TIME.TIME_KEY GROUP BY STORE.REGION, TIME.QUARTER, TIME.YEAR Storage in bytes is 86 Percent performance gain is .523360688578368 Benefit-to-cost ratio is .00608558940207405
Since a materialized view occupies storage space in the database, it is helpful to know how much space will be required before it is created. Rather than guess or wait until it has been created and then discoverer that insufficient space is available in the tablespace, use the package DBMS_ESTIMATE_SIZE. Calling this procedure instantly returns an estimate of the size in bytes that the materialized view is likely to occupy.
The parameters to this procedure are:
and the package returns:
In the example shown below, the query that will be specified in the materialized view is passed into the ESTIMATE_SUMMARY_SIZE package. Note that the SQL statement is passed in without a ";".
DBMS_OLAP.estimate_summary_size ('simple_store', 'SELECT product_key1, product_key2, SUM(dollar_sales) AS sum_dollar_sales, SUM(unit_sales) AS sum_unit_sales, SUM(dollar_cost) AS sum_dollar_cost, SUM(customer_count) AS no_of_customers FROM fact GROUP BY product_key1, product_key2' , no_of_rows, mv_size );
The procedure returns two values, an estimate for the number of rows and the size of the materialized view in bytes, as shown below.
The Summary Advisor Wizard, which can be found in Oracle Enterprise Manager, provides an interactive environment to recommend and build materialized views. Using this approach, you will be asked where the materialized views are to be placed, which fact tables to use, and which of the existing materialized views are to be retained. If a workload exists, it will be automatically selected, otherwise, it will display the recommendations that are generated from the advisor functions RECOMMEND_MV or RECOMMEND_MV_W.
By using the wizard, all of the steps required to maintain your materialized views can be completed by answering the wizard's questions and no subsequent DML operations are required. See the Oracle Enterprise Manager documentation set for further details.
One of the major administrative problems with materialized views is knowing whether they are being used. Materialized views could be in regular use or they could have been created for a one-time problem that has now been resolved. However, the usergroup who requested this level of analysis might never have told the DBA that it was no longer required, so the materialized view remains in the database occupying storage space and possibly being regularly refreshed.
If the Oracle Trace option is available, then it can advise the DBA which materialized views are in use, using exactly the same procedure as for collecting workload statistics. Trace collection is enabled and in this case the collection period is likely to be longer that for query collection because Trace will only report on materialized views that were used while it was collecting statistics. Therefore, if too small a window is chosen, not all the materialized views that are in use will be reported.
Once you are satisfied that you have collected sufficient data, the data is formatted by Oracle Trace, just as if it were workload information, and then the package EVALUATE_UTILIZATION_W is called. It analyzes the data and then the results are placed in the table MVIEWS$_EVALUATIONS.
In the example below, the utilization of materialized views is analyzed and the results are displayed.
Note that no parameters are passed into the package.
Shown below is a sample output obtained by querying the table MVIEW$EVALUATIONS which is providing the following information:
MVIEW_OWNER MVIEW_NAME RANK SIZE FREQ CUMULATIVE BENEFIT ----------- ------------------- ----- ------ ---- ---------- ---------- GROCERY STORE_MIN_SUM 1 340 1 9001 26.4735294 GROCERY STORE_MAX_SUM 2 380 1 9001 23.6868421 GROCERY STORE_STDCNT_SUM 3 3120 1 3000.38333 .961661325 GROCERY QTR_STORE_PROMO_SUM 4 196020 2 0 0 GROCERY STORE_SALES_SUM 5 340 1 0 0 GROCERY STORE_SUM 6 21 10 0 0