Release 8.1.5
A68001-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Supplied Packages Reference Release 8.1.5 A68001-01 |
|
The DBMS_OLAP package provides a collection of materialized view analysis and advisory functions that are callable from any PL/SQL program.
DBMS_OLAP utilizes structural statistics (cardinalities of fact tables, dimension tables, and distinct cardinalities of dimension level columns, join key columns, and fact table key columns) and optionally workload statistics on materialized view management events collected by Oracle Trace. The workload tables generated by Oracle Trace, if any, must be stored in the current default schema.
This estimates the size of a materialized view that you might create, in bytes and rows.
DBMS_OLAP.ESTIMATE_SUMMARY_SIZE ( stmt_id IN VARCHAR2, select_clause IN VARCHAR2, num_rows OUT NUMBER, num_bytes OUT NUMBER);
This procedure measures the utilization of each existing materialized view based on the materialized view usage statistics from a hypothetical workload. It requires parameters that are not explicit.
The output is contained in the MVIEW$_EVALUATIONS table, which is initially truncated if it already contains rows.
DBMS_OLAP.EVALUATE_UTILIZATION;
| Parameter | Description |
|---|---|
MVIEW$_EVALUATIONS |
Describes an evaluation of the utilization of each existing materialized view. It is initially truncated if it already contains rows. This is implicit, because it is supplied to the procedure when the procedure is called. |
This procedure measures the utilization of each existing materialized view based on the materialized view usage statistics collected from the workload. The workload must be contained in tables located in the default schema, as described below.
This procedure also creates the WORK$_IDEAL_MVIEW and WORK$_MVIEW_USAGE views.
DBMS_OLAP.EVALUATE_UTILIZATION_W;
| Parameter | I/O | Description |
|---|---|---|
MVIEW$_EVALUATIONS |
OUT |
Returns an evaluation of the utilization of each existing materialized view. It is initially truncated if it already contains rows. This is implicit, because it is supplied to the procedure when the procedure is called. |
V_192216243_F_5_E_14_8_1 |
IN |
Table of workload requests logged by Oracle Trace. This is implicit, because it is supplied to the procedure when the procedure is called. |
V_192216243_F_5_E_15_8_1 |
IN |
Table of materialized view usages logged by Oracle Trace. This is implicit, because it is supplied to the procedure when the procedure is called. |
This procedure generates a set of recommendations about which materialized views should be created, retained, or dropped, based on an analysis of table and column cardinality statistics gathered by ANALYZE.
The recommendations are based on a hypothetical workload in which all possible queries in the data warehouse are weighted equally. This procedure does not require or use the workload statistics tables collected by Oracle Trace, but it works even if those tables are present.
Dimensions must have been created, and there must be foreign key constraints that link the dimensions to fact tables.
Recommending materialized views with a hypothetical workload is appropriate in a DBA-less environment where ease of use is the primary consideration; however, if a workload is available in the default schema, it should be used.
DBMS_OLAP.RECOMMEND_MV ( fact_table_filter IN VARCHAR2, storage_in_bytes IN NUMBER, retention_list IN VARCHAR2, retention_pct IN NUMBER := 50);
| Parameter | Description |
|---|---|
MVIEW$_RECOMMENDATION |
Returns the recommendations made, including a size estimate and the SQL required to build the materialized view. This is implicit, because it is supplied to the procedure when the procedure is called. |
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), and an analysis of table and column cardinality statistics gathered by ANALYZE.
RECOMMEND_MV_W requires that you have run ANALYZE to gather table and column cardinality statistics, you have collected and formatted the workload statistics, and you have created dimensions.
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.
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.
This procedure also creates the WORK$_IDEAL_MVIEW and WORK$_MVIEW_USAGE views.
DBMS_OLAP.RECOMMEND_MV_W ( fact_table_filter IN VARCHAR2, storage_in_bytes IN NUMBER, retention_list IN VARCHAR2, retention_pct IN NUMBER := 80);
| Parameter | I/O | Description |
|---|---|---|
MVIEW$_RECOMMENDATION |
|
Returns the recommendations made, including a size estimate and the SQL required to build the materialized view. This is implicit, because it is supplied to the procedure when the procedure is called. |
V_192216243_F_5_E_14_8_1 (required) |
|
Table of workload requests logged by Oracle Trace. This is implicit, because it is supplied to the procedure when the procedure is called. |
V_192216243_F_5_E_15_8_1 (required) |
|
Table of materialized view usages logged by Oracle Trace. This is implicit, because it is supplied to the procedure when the procedure is called. |
This procedure verifies that the hierarchical and attribute relationships, and join relationships, specified in an existing dimension object are correct. This provides a fast way to ensure that referential integrity is maintained.
DBMS_OLAP.VALIDATE_DIMENSION ( dimension_name VARCHAR2, incremental BOOLEAN := TRUE, check_nulls BOOLEAN := FALSE);
This table represents the recommendations made by the RECOMMEND_MV procedure or the RECOMMEND_MV_W procedure.
Each row contains a recommended action (CREATE, RETAIN, or DROP), and a description of the materialized view in one of two forms:
RETAIN or DROP), or
SELECT expression for creating the materialized view (when the recommended action is CREATE). The SQL SELECT expression is supplied in four parts:
GROUP BY clause of the materialized view. These columns also appear in the SELECT list.
WHERE clause, which is an AND-separated list of inner equijoins
FROM clause, which is a comma-separated list of relation names
SELECT list of the materialized view.
This is an example of how you can assemble the SQL SELECT expression:
SELECT <group by columns>, <measures list> FROM <from clause> WHERE <where clause> GROUP BY <group by columns>;
Each row also contains the storage_in_bytes field, which is the storage in bytes that an existing materialized view occupies; in the case of newly recommended materialized views, this is an estimate of the size that the materialized view would occupy.
Two performance metrics are provided for each materialized view:
The recommendations are ordered from most beneficial to least beneficial by recommendation_number, and the incremental benefit is calculated under the assumption that all previous recommendations in the list have been accepted.
This table represents the evaluations made by the EVALUATE_UTILIZATION procedure. The number of rows in this table is equal to the number of materialized views in the current database.
|
Note:
The |
This view is based on the table V_192216243_F_5_E_14_8_1 which corresponds to an actual or potential query rewrite.
This view is based on the table V_1992216243_F_5_E_15_8_1 which is generated by the Oracle Trace format operation. Each row of this table corresponds to an actual query rewrite.
| Column Name | Type | Constraints | Description |
|---|---|---|---|
sql_text_hash |
NUMBER |
Not |
SQL statement signature. |
lib_cache_addr |
VARCHAR2(16) |
Not |
Associates this materialized view usage with a specific cursor. |
