RECOMMEND_MVIEW_STRATEGY Procedure

This procedure generates a set of recommendations about which materialized views should be created, retained, or dropped, based on information in the workload (gathered by Oracle Trace, the user workload, or the SQL cache), and an analysis of table and column cardinality statistics gathered by the DBMS_STATS.GATHER_TABLE_STATS procedure.

RECOMMEND_MVIEW_STRATEGY requires that you have run the DBMS_STATS.GATHER_TABLE_STATS procedure to gather table and column cardinality statistics and have collected and formatted the workload statistics.

The workload is aggregated to determine the count of each request in the workload, and this count is used as a weighting factor during the optimization process. If the workload_id is not provided, then RECOMMEND_MVIEW_STRATEGY uses a hypothetical workload based on dimension definitions and other embedded statistics.

The space of all dimensional materialized views that include the specified fact tables identifies the set of materialized views that optimize performance across the workload. The recommendation results are stored in system tables, which can be accessed through the view SYSTEM.MVIEW_RECOMMENDATIONS.

Periodically, the unused results can be purged from the system by calling the DBMS_OLAP.PURGE_RESULTS procedure

See Also: "DBMS_OLAP Interface Views"

Syntax

DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY (
   run_id             IN  NUMBER,
   workload_id        IN  NUMBER, 
   filter_id          IN  NUMBER,
   storage_in_bytes   IN  NUMBER,
   retention_pct      IN  NUMBER,
   retention_list     IN  VARCHAR2,
   fact_table_filter  IN  VARCHAR2); 

Parameters

Table 34-15 RECOMMEND_MVIEW_STRATEGY Procedure Parameters

Parameter	Description
run_id	An ID generated by the DBMS_OLAP.CREATE_ID procedure to uniquely identify results of a run
workload_id	An optional workload ID that maps to a workload in the current repository. Use the parameter DBMS_OLAP.WORKLOAD_ALL to choose all workloads. If the workload_id is set to NULL, the call will use a hypothetical workload
filter_id	An optional filter ID that maps to a set of user-supplied filter items. Use the parameter DBMS_OLAP.FILTER_NONE to avoid filtering
storage_in_bytes	Maximum storage, in bytes, that can be used for storing materialized views. This number must be non-negative.
retention_pct	Number between 0 and 100 that specifies the percent of existing materialized view storage that must be retained, based on utilization on the actual or hypothetical workload. A materialized view is retained if the cumulative space, ranked by utilization, is within the retention threshold specified (or if it is explicitly listed in retention_list). Materialized views that have a NULL utilization (e.g. non-dimensional materialized views) are always retained.
retention_list	Comma-separated list of materialized view table names. A drop recommendation is not made for any materialized view that appears in this list.
fact_table_filter	Optional list of fact tables used to filter real or ideal workload