15
Summary Advisor

This chapter can help you create and manage a data warehouse, and includes:

Summary Advisor

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.

Figure 15-1 Materialized Views and the Summary Advisor

From within the DBMS_OLAP package, several facilities are available to:

Estimate the size of a materialized view
Recommend a materialized view
Recommend materialized views based on collected workload information
Report actual utilization of materialized views based on collected workload

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.

Collecting Structural Statistics

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.

Collection of Dynamic Workload Statistics

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:

The name of each materialized view selected by query rewrite
The estimated benefit obtained by using the materialized view, which is roughly the ratio of the fact table cardinality to the materialized view cardinality, adjusted for the need to further aggregate over the materialized view or join it back to other relations
The ideal materialized view that could have been used by the request

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:

Set six initialization parameters to collect data via Oracle Trace. Enabling these parameters incurs some additional overhead at database connection, but is otherwise transparent.
- ORACLE_TRACE_COLLECTION_NAME = oraclesm
- ORACLE_TRACE_COLLECTION_PATH = location of collection files
- ORACLE_TRACE_COLLECTION_SIZE = 0
- ORACLE_TRACE_ENABLE = TRUE turns on Trace collecting
- ORACLE_TRACE_FACILITY_NAME = oraclesm
- ORACLE_TRACE_FACILITY_PATH = location of trace facility files
For further information on these parameters, refer to the Oracle Trace Users Guide.
Run the Oracle Trace Manager GUI, specify a collection name, and select the SUMMARY_EVENT set. Oracle Trace Manager reads information from the associated configuration file and registers events to be logged with Oracle. While collection is enabled, the workload information defined in the event set gets written to a flat log file.
When collection is complete, Oracle Trace automatically formats the Oracle Trace log file into a set of relations, which have the predefined synonyms 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:
```
otrcfmt   collection_name.cdf   user/password@database
```
Run the GATHER_TABLE_STATS procedure of the DBMS_STATS package or ANALYZE...ESTIMATE STATISTICS to collect cardinality statistics on all fact tables, dimension tables, and key columns (any column that appears in a dimension LEVEL clause or JOIN KEY clause of a CREATE DIMENSION statement).

Once these four steps have been completed, you will be ready to make recommendations about your materialized views.

Recommending 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.

RECOMMEND_MV uses structural statistics, but not workload statistics, to generate recommendations.
RECOMMEND_MV_W uses both workload statistics and structural statistics.

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:

All tables including existing materialized views must have been analyzed as described in step 4 above.
Dimensions must exist.
The advisor should be able to identify the fact table because it will contain foreign key references to other tables.

See Also:
See Oracle8i Supplied PL/SQL Packages Reference for detailed information about the DBMS_OLAP package.

Four parameters are required to use these functions:

Fact table names or null to analyze all fact tables
The maximum storage that can be used for storing materialized views
A list or materialized views that you want to retain
A number between 0 to 100 that specifies the percentage of materialized views that must be retained

A typical call to the package, where the main fact table is called FACT, would be:

DBMS_OLAP.RECOMMEND_MV('fact', 100000, '', 10);

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.

Recommendation Number 1

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

Recommendation Number 2

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.

DBMS_OLAP.RECOMMEND_MV_W('fact', 100000, '', 10);

Recommendation Number 3

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

Recommendation Number 4

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

Estimating Materialized View Size

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:

the name for sizing
the SELECT statement

and the package returns:

the number of rows it expects in the materialized view
the size of the materialized view in bytes

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.

No of Rows: 17284
Size of Materialized view (bytes): 2281488

Summary Advisor Wizard

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.

Is a Materialized View Being Used?

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.

DBMS_OLAP.EVALUATE_UTILIZATION_W();

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:

Materialized view owner and name
Rank of this materialized view in descending benefit-to-cost ratio
Size of the materialized view in bytes
The number of times the materialized view appears in the workload
The cumulative benefit is calculated each time the materialized view is used as

The benefit-to-cost ratio is calculated as the incremental improvement in performance to the size of the materialized view

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

15 Summary Advisor