23
DBMS_OLAP

The DBMS_OLAP package provides a collection of materialized view analysis and advisory functions that are callable from any PL/SQL program.

See Also: For guidelines on using these functions, please see the Oracle8i Tuning manual.

Requirements

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.

Error Messages

Table 23-1 DBMS_OLAP Error Messages

Error Code	Description
ORA-30476	PLAN_TABLE does not exist in the user's schema
ORA-30477	The input select_clause is incorrectly specified
ORA-30478	Specified dimension does not exist
ORA-30479	Summary Advisor error\n <QSM message with more details
QSM-00501	Unable to initialize Summary Advisor environment
QSM-00502	OCI error
QSM-00503	Out of memory
QSM-00504	Internal error
QSM-00505	Syntax error in <parse_entity_name> - <error_description>
QSM-00506	No fact-tables could be found
QSM-00507	No dimensions could be found
QSM-00508	Statistics missing on tables/columns
QSM-00509	Invalid parameter - <parameter_name>
QSM-00510	Statistics missing on summaries
QSM-00511	Invalid fact-tables specified in fact-filter
QSM-00512	Invalid summaries specified in the retention-list
QSM-00513	One or more of the workload tables is missing

Summary of Subprograms

Table 23-2 DBMS_OLAP Package Subprograms

Subprogram	Description
ESTIMATE_SUMMARY_SIZE procedure	Estimates the size of a materialized view that you might create, in bytes and rows.
EVALUATE_UTILIZATION procedure	Measures the utilization of each existing materialized view.
EVALUATE_UTILIZATION_W procedure	Measures the utilization of each existing materialized view.
RECOMMEND_MV procedure	Generates a set of recommendations about which materialized views should be created, retained, or dropped.
RECOMMEND_MV_W procedure	Generates a set of recommendations about which materialized views should be created, retained, or dropped.
VALIDATE_DIMENSION procedure	Verifies that the relationships specified in a dimension are correct.

ESTIMATE_SUMMARY_SIZE procedure

This estimates the size of a materialized view that you might create, in bytes and rows.

Syntax

DBMS_OLAP.ESTIMATE_SUMMARY_SIZE (
   stmt_id       IN  VARCHAR2, 
   select_clause IN  VARCHAR2, 
   num_rows      OUT NUMBER, 
   num_bytes     OUT NUMBER); 

Parameters

Table 23-3 ESTIMATE_SIZE Procedure Parameters

Parameter	Description
stmt_id	Arbitrary string used to identify the statement in an EXPLAIN PLAN.
select_clause	The SELECT statement to be analyzed.
num_rows	Estimated cardinality.
num_bytes	Estimated number of bytes.

EVALUATE_UTILIZATION procedure

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.

Syntax

DBMS_OLAP.EVALUATE_UTILIZATION; 

Parameters

Table 23-4 EVALUATE_UTILIZATION Procedure Parameters

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.

EVALUATE_UTILIZATION_W procedure

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.

See Also: "DBMS_OLAP Interface Tables"

Syntax

DBMS_OLAP.EVALUATE_UTILIZATION_W; 

Parameters

Table 23-5 EVALUATE_UTILIZATION_W Procedure Parameters

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.

RECOMMEND_MV procedure

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.

See Also: For workload-driven analysis, see "RECOMMEND_MV_W procedure"

Syntax

DBMS_OLAP.RECOMMEND_MV (
   fact_table_filter IN VARCHAR2, 
   storage_in_bytes  IN NUMBER, 
   retention_list    IN VARCHAR2, 
   retention_pct     IN NUMBER := 50); 

Parameters

Table 23-6 RECOMMEND_MV Procedure Parameters

Parameter	Description
fact_table_filter	Comma-separated list of fact table names to analyze, or NULL to analyze all fact tables.
storage_in_bytes	Maximum storage, in bytes, that can be used for storing materialized views. This number must be non-negative.
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.
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.

Parameters

Table 23-7 RECOMMEND_MV Procedure Parameters

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.

RECOMMEND_MV_W 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), 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.

See Also: "DBMS_OLAP Interface Tables"

Syntax

DBMS_OLAP.RECOMMEND_MV_W (
   fact_table_filter IN  VARCHAR2,
   storage_in_bytes  IN  NUMBER, 
   retention_list    IN  VARCHAR2, 
   retention_pct     IN  NUMBER := 80); 

Parameters

Table 23-8 RECOMMEND_MV_W Procedure Parameters

Parameter	Description
fact_table_filter	Comma-separated list fact table names to analyze, or NULL to analyze all fact tables.
storage_in_bytes	Maximum storage, in bytes, that can be used for storing materialized views. This number must be non-negative.
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.
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.

Parameters

Table 23-9 RECOMMEND_MV_W Procedure Parameters

Parameter	I/O	Description
MVIEW$_RECOMMENDATION	OUT	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)	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 (required)	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.

VALIDATE_DIMENSION procedure

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.

Syntax

DBMS_OLAP.VALIDATE_DIMENSION (
   dimension_name  VARCHAR2, 
   incremental     BOOLEAN := TRUE, 
   check_nulls     BOOLEAN := FALSE); 

Parameters

Table 23-10 VALIDATE_DIMENSION Procedure Parameters

Parameter	Description
dimension_name	Name of the dimension to analyze.
incremental	If TRUE, then tests are performed only for the rows specified in the sumdelta$ table for tables of this dimension; otherwise, check all rows.
check_nulls	If TRUE, then all level columns are verified to be non-NULL; otherwise, this check is omitted. Specify FALSE when non-nullness is guaranteed by other means, such as NOT NULL constraints.

Parameters

Table 23-11 VALIDATE_DIMENSION Procedure Parameters

Parameter	I/O	Description
MVIEW$_EXCEPTIONS	OUT	Returns the rows of the tables referenced in the named dimension that violate dimensional integrity. Each row identifies a table and a ROWID for an exception. This is implicit, because it is supplied to the procedure when the procedure is called.

DBMS_OLAP Interface Tables

MVIEW$_RECOMMENDATION

This table represents the recommendations made by the RECOMMEND_MV procedure or the RECOMMEND_MV_W procedure.

Table 23-12 MVIEW$_RECOMMENDATION

Column Name	Type	Constraints	Description
recommendation_ number	INTEGER	Primary key > 0	Unique identifier for this recommendation.
recommended_ action	VARCHAR2(6)	CREATE, RETAIN, or DROP	What to do with the materialized view described by this row.
summary_owner	VARCHAR(30)	Undefined when action is CREATE	Owner of an existing materialized view to DROP or RETAIN.
summary_name	VARCHAR2(30)	Undefined when action is CREATE	Name of an existing materialized view to DROP or RETAIN.
group_by_columns	VARCHAR2(2000)	NULL unless action is CREATE	Comma-separated list of column references in the GROUP BY clause of the materialized view. These columns references also appear in the SELECT list
where_clause	VARCHAR2(2000)	NULL unless action is CREATE	AND-separated list of inner equijoins, as they would appear in the WHERE clause of this materialized view.
from_clause	VARCHAR2(2000)	NULL unless action is CREATE	Comma-separated list of relation names.
measures_list	VARCHAR2(2000)	NULL if action is DROP, non-NULL if action is CREATE	Comma separated list of <groupingFunction> (<expression>), as it would appear in the SELECT list of the materialized view. If recommended_action is CREATE, this field is the list of measures which must be present in the created materialized view. If recommended_action is RETAIN and this field is non-NULL, this field is the list of measures which must be added to the materialized view.
storage_in_bytes	NUMBER	>= 0	Actual or estimated storage in bytes.
pct_performance_ gain	NUMBER		The expected incremental improvement in performance obtained by accepting this recommendation relative to the initial condition, assuming that all previous recommendations have been accepted, or NULL if unknown.
benefit_to_cost_ ratio	NUMBER		Ratio of the incremental improvement in performance to the size of the materialized view in bytes, or NULL if unknown.

Each row contains a recommended action (CREATE, RETAIN, or DROP), and a description of the materialized view in one of two forms:

• The materialized view name, if the materialized view already exists (when the recommended action is RETAIN or DROP), or

• The SQL SELECT expression for creating the materialized view (when the recommended action is CREATE). The SQL SELECT expression is supplied in four parts:
  1. A comma-separated list of column names in the GROUP BY clause of the materialized view. These columns also appear in the SELECT list.

  2. The contents of the WHERE clause, which is an AND-separated list of inner equijoins

  3. The contents of the FROM clause, which is a comma-separated list of relation names

  4. The list of aggregated measures, which is a comma separated list of <grouping function>(<expression>), as it appears in the 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:

pct_performance_gain	The expected incremental improvement in performance obtained by accepting this recommendation, assuming that all previous recommendations have been accepted.
benefit_to_cost_ratio	The ratio of the incremental improvement in performance to the size of the 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.

MVIEW$_EVALUATIONS

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.

Table 23-13 MVIEW$_EVALUATIONS

Column Name	Type	Constraints	Description
summary_owner	VARCHAR2(30)	Primary key	Owner of an existing materialized view in this database.
summary_name	VARCHAR2(30)	Primary key	Name of an exiting materialized view in this database.
rank	INTEGER	>= 1	Rank of this materialized view in descending order of benefit_to_cost_ratio.
storage_in_bytes	NUMBER	>= 0	Size of the materialized view in bytes.
frequency	INTEGER	>= 0, or NULL	Number of times this materialized view appears in the workload.
cumulative_benefit	NUMBER	>= 0, or NULL	Each time a materialized view is used by query rewrite, the materialized view and its benefit is logged to the workload. This field sums up the benefit (or net reduction factor) for each materialized view
benefit_to_cost_ ratio	NUMBER		If storage_in_bytes > 0, then the ratio of cumulative_benefit to storage_in_bytes, else NULL.

Note: The benefit_to_cost_ratio in this table uses a similar, but not identical, method as what is used to compute the benefit_to_cost_ratio of the MVIEW$_RECOMMENDATION table.

WORK$_IDEAL_MVIEW

This view is based on the table V_192216243_F_5_E_14_8_1 which corresponds to an actual or potential query rewrite.

Table 23-14 WORK$_IDEAL_MVIEW

Column Name	Type	Constraints	Description
sql_text_hash	NUMBER	Not NULL	SQL statement signature.
lib_cache_addr	VARCHAR2(16)	Not NULL	Associates this materialized view usage with a specific cursor.
group_by_columns	VARCHAR2(2000)	Not NULL	Comma-separated list of qualified column references in the GROUP BY clause of the ideal materialized view. These columns references also appear in the SELECT list.
where_clause	VARCHAR2(2000)		'AND'-separated list of inner equijoins, as they would appear in the WHERE clause of the ideal materialized view.
from_clause	VARCHAR2(2000)	Not NULL	Comma-separated list of owner-qualified relation names and aliases.
measure	VARCHAR2(2000)		Comma-separated list of <groupingFunction> (<expression>), as it would appear in the SELECT list of the ideal materialized view.
idl_sum_flags	NUMBER		Flag vector (4 bytes). Currently all bits are undefined.
summary_owner	VARCHAR2(30)		Owner of an existing materialized view used by query rewrite, or NULL if none.
summary_name	VARCHAR2(30)		Name of an existing materialized view used by query rewrite, or NULL if none.
actual_benefit	NUMBER	>0	Actual performance benefit of using this materialized view.

WORK$_MVIEW_USAGE

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 NULL	SQL statement signature.
lib_cache_addr	VARCHAR2(16)	Not NULL	Associates this materialized view usage with a specific cursor.

---