Oracle9i Supplied PL/SQL Packages and Types Reference Release 1 (9.0.1) Part Number A89852-02 |
|
DBMS_OLAP , 14 of 26
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
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);
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|