58
DBMS_OLAP

Note:

With Oracle 10g Release 1 (10.1), the DBMS_OLAP package has been replaced with improved technology. While Oracle recommends you not begin development using DBMS_OLAP, Oracle continues to support DBMS_OLAP, and your existing applications using DBMS_OLAP will continue to work.

If you are developing new or substantially modified applications and had previously used the Summary Advisor in DBMS_OLAP, you should now use the SQLAccess Advisor described in Chapter 12, "DBMS_ADVISOR".
If you had previously used DBMS_OLAP.VALIDATE_DIMENSION, you should now use DBMS_DIMENSION.VALIDATE_DIMENSION described in Chapter 32, "DBMS_DIMENSION".
If you had previously used DBMS_OLAP.ESTIMATE_MVIEW_SIZE, you should now use DBMS_MVIEW.ESTIMATE_MVIEW_SIZE described in Chapter 54, "DBMS_MVIEW"

The DBMS_OLAP package, presented here for reasons of backward compatibility, provides a collection of materialized view analysis and advisory functions that are callable from any PL/SQL program. Some of the functions generate output tables.

See Also:

Oracle Data Warehousing Guide for more information.

This chapter contains the following topics:

Using DBMS_OLAP

Overview

DBMS_OLAP performs seven major functions, which include materialized view strategy recommendation, materialized view strategy evaluation, reporting and script generation, repository management, workload management, filter management, and dimension validation.

To perform materialized view strategy recommendation and evaluation functions, the workload information can either be provided by the user or synthesized by the Advisor engine. In the former case, cardinality information of all tables and materialized views referenced in the workload are required. In the latter case, dimension objects must be present and cardinality information for all dimension tables, fact tables, and materialized views are required. Cardinality information should be gathered with the DBMS_STATS.GATHER_TABLE_STATS procedure. Once these functions are completed, the analysis results can be presented with the reporting and script generation function.

The workload management function handles three types of workload, which are user-specified workload, SQL cache workload, and Oracle Trace workload. To process the user-specified workload, a user-defined workload table must be present in the user's schema. To process Oracle Trace workload, the Oracle Trace formatter must be run to preprocess collected workload statistics into default V-tables in the user's schema.

Views

Several views are created when using DBMS_OLAP. All are in the SYSTEM schema. To access these views, you must have a DBA role.

SYSTEM.MVIEW_EVALUATIONS

Table 58-1 SYSTEM.MVIEW_EVALUATIONS

Column	NULL?	Datatype	Description
`RUNID`	`NOT NULL`	`NUMBER`	Run id identifying a unique Advisor call.
`MVIEW_OWNER`	`-`	`VARCHAR2(30)`	Owner of materialized view.
`MVIEW_NAME`	`-`	`VARCHAR2(30)`	Name of an exiting materialized view in this database.
`RANK`	`NOT NULL`	`NUMBER`	Rank of this materialized view in descending order of `benefit_to_cost_ratio`.
`STORAGE_IN_BYTES`	`-`	`NUMBER`	Size of the materialized view in bytes.
`FREQUENCY`	`-`	`NUMBER`	Number of times this materialized view appears in the workload.
`CUMULATIVE_BENEFIT`	`-`	`NUMBER`	The cumulative benefit of the materialized view.
`BENEFIT_TO_COST_RATIO`	`NOT NULL`	`NUMBER`	The ratio of `cumulative_benefit` to `storage_in_bytes`.

SYSTEM.MVIEW_EXCEPTIONS

Table 58-2 SYSTEM.MVIEW_EXCEPTIONS

Column	NULL?	Datatype	Description
`RUNID`	`-`	`NUMBER`	Run id identifying a unique advisor call.
`OWNER`	`-`	`VARCHAR2(30)`	Owner name.
`TABLE_NAME`	`-`	`VARCHAR2(30)`	Table name.
`DIMENSION_NAME`	`-`	`VARCHAR2(30)`	Dimension name.
`RELATIONSHIP`	`-`	`VARCHAR2(11)`	Violated relation name.
`BAD_ROWID`	`-`	`ROWID`	Location of offending entry.

SYSTEM.MVIEW_FILTER

Table 58-3 SYSTEM.MVIEW_FILTER

Column	NULL?	Datatype	Description
`FILTERID`	`NOT NULL`	`NUMBER`	Unique number used to identify the operation that used this filter.
`SUBFILTERNUM`	`NOT NULL`	`NUMBER`	A unique id number that groups all filter items together. A corresponding filter header record can be found in the `MVIEW_LOG` table.
`SUBFILTERTYPE`	`-`	`VARCHAR2(12)`	Filter item number.
`STR_VALUE`	`-`	`VARCHAR2(1028)`	String attribute for items that require strings.
`NUM_VALUE1`	`-`	`NUMBER`	Numeric low for items that require numbers.
`NUM_VALUE2`	`-`	`NUMBER`	Numeric high for items that require numbers.
`DATE_VALUE1`	`-`	`DATE`	Date low for items that require dates.
`DATE_VALUE2`	`-`	`DATE`	Date high for items that require dates.

SYSTEM.MVIEW_FILTERINSTANCE

Table 58-4 SYSTEM.MVIEW_FILTERINSTANCE

Column	NULL?	Datatype	Description
`RUNID`	`NOT NULL`	`NUMBER`	Unique number used to identify the operation that used this filter.
`FILTERID`	`-`	`NUMBER`	A unique id number that groups all filter items together. A corresponding filter header record can be found in the `MVIEW_LOG` table.
`SUBFILTERNUM`	`-`	`NUMBER`	Filter item number.
`SUBFILTERTYPE`	`-`	`VARCHAR2(12)`	Filter item type.
`STR_VALUE`	`-`	`VARCHAR2(1028)`	String attribute for items that require strings.
`NUM_VALUE1`	`-`	`NUMBER`	Numeric low for items that require numbers.
`NUM_VALUE2`	`-`	`NUMBER`	Numeric high for items that require numbers.
`DATE_VALUE1`	`-`	`DATE`	Date low for items that require dates.
`DATE_VALUE2`	`-`	`DATE`	Date high for items that require dates.

SYSTEM.MVIEW_LOG

Table 58-5 SYSTEM.MVIEW_LOG

Column	NULL?	Datatype	Description
`ID`	`NOT NULL`	`NUMBER`	Unique number used to identify the table entry. The number must be created using the `CREATE_ID` routine.
`FILTERID`	`-`	`NUMBER`	Optional filter id. Zero indicates no user-supplied filter has been applied to the operation.
`RUN_BEGIN`	`-`	`DATE`	Date at which the operation began.
`RUN_END`	`-`	`DATE`	Date at which the operation ended.
`TYPE`	`-`	`VARCHAR2(11)`	A name that identifies the type of operation.
`STATUS`	`-`	`VARCHAR2(11)`	The current operational status.
`MESSAGE`	`-`	`VARCHAR2(2000)`	Informational message indicating current operation or condition.
`COMPLETED`	`-`	`NUMBER`	Number of steps completed by operation.
`TOTAL`	`-`	`NUMBER`	Total number steps to be performed.
`ERROR_CODE`	`-`	`VARCHAR2(20)`	Oracle error code in the event of an error.

SYSTEM.MVIEW_RECOMMENDATIONS

Table 58-6 SYSTEM.MVIEW_RECOMMENDATIONS

Column	NULL?	Datatype	Description
`RUNID`	`-`	`NUMBER`	Run id identifying a unique advisor call.
`ALL_TABLES`	`-`	`VARCHAR2(2000)`	A comma-delimited list of fully qualified table names for structured recommendations.
`FACT_TABLES`	`-`	`VARCHAR2(1000)`	A comma-delimited list of grouping levels, if any, for structured recommendation.
`GROUPING_LEVELS`	`-`	`VARCHAR2(2000)`	-
`QUERY_TEXT`	`-`	`LONG`	Query text of materialized view if `RECOMMENDED_ACTION` is `CREATE`; null otherwise.
`RECOMMENDATION_NUMBER`	`NOT NULL`	`NUMBER`	Unique identifier for this recommendation.
`RECOMMENDED_ACTION`	`-`	`VARCHAR2(6)`	`CREATE`, `RETAIN`, or `DROP`.
`MVIEW_OWNER`	`-`	`VARCHAR2(30)`	Owner of the materialized view if `RECOMMENDED_ACTION` is `RETAIN` or `DROP`; null otherwise.
`MVIEW_NAME`	`-`	`VARCHAR2(30)`	Name of the materialized view if `RECOMMENDED_ACTION` is `RETAIN` or `DROP`; null otherwise.
`STORAGE_IN_BYTES`	`-`	`NUMBER`	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`	`NOT NULL`	`NUMBER`	Ratio of the incremental improvement in performance to the size of the materialized view in bytes, or `NULL` if unknown.

SYSTEM.MVIEW_WORKLOAD

Table 58-7 SYSTEM.MVIEW_WORKLOAD

Column	NULL?	Datatype	Description
`APPLICATION`	`-`	`VARCHAR2(30)`	Optional application name for the query.
`CARDINALITY`	`-`	`NUMBER`	Total cardinality of all of tables in query.
`WORKLOADID`	`-`	`NUMBER`	Workload id identifying a unique sampling.
`FREQUENCY`	`-`	`NUMBER`	Number of times query executed.
`IMPORT_TIME`	`-`	`DATE`	Date at which item was collected.
`LASTUSE`	`-`	`DATE`	Last date of execution.
`OWNER`	`-`	`VARCHAR2(30)`	User who last executed query.
`PRIORITY`	`-`	`NUMBER`	User-supplied ranking of query.
`QUERY`	`-`	`LONG`	Query text.
`QUERYID`	`-`	`NUMBER`	Id number identifying a unique query.
`RESPONSETIME`	`-`	`NUMBER`	Execution time in seconds.
`RESULTSIZE`	`-`	`NUMBER`	Total bytes selected by the query.

Deprecated Subprograms

The DBMS_OLAP subprograms have been replaced with improved technology: see Chapter 12, "DBMS_ADVISOR", Chapter 32, "DBMS_DIMENSION" and Chapter 54, "DBMS_MVIEW". All DBMS_OLAP subprograms are obsolete with Oracle 10g Release 1 (10.1), and while Oracle will continue to support them, they are documented only for reasons of backward compatibility.

Summary of DBMS_OLAP Subprograms

Note:

The DBMS_OLAP subprograms have been replaced with improved technology:

If you are developing new or substantially modified applications and had previously used the Summary Advisor in DBMS_OLAP, you should now use the SQLAccess Advisor described in Chapter 12, "DBMS_ADVISOR".
If you had previously used DBMS_OLAP.VALIDATE_DIMENSION, you should now use DBMS_DIMENSION.VALIDATE_DIMENSION described in Chapter 32, "DBMS_DIMENSION".
If you had previously used DBMS_OLAP.ESTIMATE_MVIEW_SIZE, you should now use DBMS_MVIEW.ESTIMATE_MVIEW_SIZE described in Chapter 54, "DBMS_MVIEW"

Table 58-8 DBMS_OLAP Package Subprograms

Subprogram	Description
ADD_FILTER_ITEM Procedure	Filters the contents being used during the recommendation process [seeDeprecated Subprograms ]
CREATE_ID Procedure	Generates an internal ID used by a new workload collection, a new filter, or a new advisor run [seeDeprecated Subprograms ]
ESTIMATE_MVIEW_SIZE Procedure	Estimates the size of a materialized view that you might create, in bytes and rows [seeDeprecated Subprograms ]
EVALUATE_MVIEW_STRATEGY Procedure	Measures the utilization of each existing materialized view [seeDeprecated Subprograms ]
GENERATE_MVIEW_REPORT Procedure	Generates an HTML-based report on the given Advisor run [seeDeprecated Subprograms ]
GENERATE_MVIEW_SCRIPT Procedure	Generates a simple script containing the SQL commands to implement Summary Advisor recommendations [seeDeprecated Subprograms ]
LOAD_WORKLOAD_CACHE Procedure	Obtains a SQL cache workload [seeDeprecated Subprograms ]
LOAD_WORKLOAD_TRACE Procedure	Loads a workload collected by Oracle Trace [seeDeprecated Subprograms ]
LOAD_WORKLOAD_USER Procedure	Loads a user-defined workload [seeDeprecated Subprograms ]
PURGE_FILTER Procedure	Deletes a specific filter or all filters [seeDeprecated Subprograms ]
PURGE_RESULTS Procedure	Removes all results or those for a specific run [seeDeprecated Subprograms ]
PURGE_WORKLOAD Procedure	Deletes all workloads or a specific collection [seeDeprecated Subprograms ]
RECOMMEND_MVIEW_STRATEGY Procedure	Generates a set of recommendations about which materialized views should be created, retained, or dropped [seeDeprecated Subprograms ]
SET_CANCELLED Procedure	Stops the Advisor if it takes too long returning results [seeDeprecated Subprograms ]
VALIDATE_DIMENSION Procedure	Verifies that the relationships specified in a dimension are correct [seeDeprecated Subprograms ]
VALIDATE_WORKLOAD_CACHE Procedure	Validates the SQL Cache workload before performing load operations [seeDeprecated Subprograms ]
VALIDATE_WORKLOAD_TRACE Procedure	Validates the Oracle Trace workload before performing load operations [seeDeprecated Subprograms ]
VALIDATE_WORKLOAD_USER Procedure	Validates the user-supplied workload before performing load operations [seeDeprecated Subprograms ]

ADD_FILTER_ITEM Procedure

Note:

Parameter	Description
`filter_id`	An ID that uniquely describes the filter. It is generated by the `DBMS_OLAP.CREATE_ID` procedure.
`filter_name`	APPLICATION: String-workload's application column. An example of how to load a SQL Cache workload follows. BASETABLE: String-based tables referenced by workload queries. Name must be fully qualified including owner and table name (for example, `SH.SALES`). CARDINALITY: Numerical-sum of cardinality of the referenced base tables. FREQUENCY: Numerical-workload's frequency column. LASTUSE: Date-workload's lastuse column. Not used by SQL Cache workload. OWNER: String-workload's owner column. Expected in uppercase unless owner defined explicitly to be not all in uppercase. PRIORITY: Numerical-workload's priority column. Not used by SQL Cache workload. RESPONSETIME: Numerical-workload's response time column. Not used by SQL Cache workload. SCHEMA: String-based schema referenced by workload filter. TRACENAME: String-list of oracle trace collection names. Only used by a Trace Workload.
`string_list`	A comma-delimited list of strings. This parameter is only used by the filter items of the string type.
`number_min`	The lower bound of a numerical range. NULL represents the lowest possible value. This parameter is only used by the parameters of the numerical type.
`number_max`	The upper bound of a numerical range, NULL for no upper bound. NULL represents the highest possible value. This parameter is only used by the parameters of the numerical type.
`date_min`	The lower bound of a date range. `NULL` represents the lowest possible date value. This parameter is only used by the parameters of the date type.
`date_max`	The upper bound of a date range. `NULL` represents the highest possible date value. This parameter is only used by the parameters of the date type.

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.

Parameter	Description
`run_id`	An ID generated by the `DBMS_OLAP.CREATE_ID` procedure to 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.
`filter_id`	Specify filter for the workload to be used. The value `DBMS_OLAP.FILTER_NONE` indicates no filtering.

Parameter	Description
`filename`	Fully qualified output file name to receive HTML data. Note that the Oracle server restricts file access within Oracle stored procedures. See the "Security and Performance" section of the Java Developer's Guide for more information on file permissions.
`id`	An ID that identifies an advisor run. The parameter `DBMS_OLAP.RUNID_AL`L indicates all advisor runs should be reported.
`tspace`	Optional tablespace name to use when creating materialized views.

Parameter	Description
`workload_id`	Fully qualified output file name to receive HTML data. Note that the Oracle server restricts file access within Oracle stored procedures. See the "Security and Performance" section of the Java Developer's Guide for more information on file permission.
`flags`	`DBMS_OLAP.WORKLOAD_OVERWRITE:` The load routine will explicitly remove any existing queries from the workload that are owned by the specified collection ID. `DBMS_OLAP.WORKLOAD_APPEND:` The load routine preserves any existing queries in the workload. Any queries collected by the load operation will be appended to the end of the specified workload. `DBMS_OLAP.WORKLOAD_NEW:` The load routine assumes there are no existing queries in the workload. If it finds an existing workload element, the call will fail with an error. Note: the flags have the same behavior irrespective of the `LOAD_WORKLOAD` operation.
`filter_id`	Specify filter for the workload to be loaded.
`application`	The default business application name. This value will be used for a query if one is not found in the target workload.
`priority`	The default business priority to be assigned to every query in the target workload.

Parameter	Description
`collectionid`	Fully qualified output file name to receive HTML data. Note that the Oracle server restricts file access within Oracle stored procedures. See the "Security and Performance" section of the Java Developer's Guide for more information on file permission.
`flags`	`DBMS_OLAP.WORKLOAD_OVERWRITE`: The load routine will explicitly remove any existing queries from the workload that are owned by the specified collection ID. `DBMS_OLAP.WORKLOAD_APPEND`: The load routine preserves any existing queries in the workload. Any queries collected by the load operation will be appended to the end of the specified workload. `DBMS_OLAP.WORKLOAD_NEW`: The load routine assumes there are no existing queries in the workload. If it finds an existing workload element, the call will fail with an error. Note: the flags have the same behavior irrespective of the `LOAD_WORKLOAD` operation.
`filter_id`	Specify filter for the workload to be loaded.
`application`	The default business application name. This value will be used for a query if one is not found in the target workload.
`priority`	The default business priority to be assigned to every query in the target workload.
`owner_name`	The schema that contains the Oracle Trace data. If omitted, the current user will be used.

Parameter	Description
`workload_id`	The required id that was returned by the `DBMS_OLAP.CREATE_ID` call.
`flags`	`DBMS_OLAP.WORKLOAD_OVERWRITE:` The load routine will explicitly remove any existing queries from the workload that are owned by the specified collection ID `DBMS_OLAP.WORKLOAD_APPEND:` The load routine preserves any existing queries in the workload. Any queries collected by the load operation will be appended to the end of the specified workload `DBMS_OLAP.WORKLOAD_NEW:` The load routine assumes there are no existing queries in the workload. If it finds an existing workload element, the call will fail with an error Note: the flags have the same behavior irrespective of the `LOAD_WORKLOAD` operation.
`filter_id`	Specify filter for the workload to be loaded.
`owner_name`	The schema that contains the user supplied table or view.
`table_name`	The table or view name containing valid workload data.

Parameter	Description
dimension_name	Name of the dimension to analyze.
dimension_owner	Name of the dimension owner.
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 nonnull; otherwise, this check is omitted. Specify `FALSE` when nonnullness is guaranteed by other means, such as `NOT` `NULL` constraints.
run_id	An ID generated by the `DBMS_OLAP.CREATE_ID` procedure to identify a run.

Parameter	Description
`valid`	Return `DBMS_OLAP.VALID` or `DBMS_OLAP.INVALID` Indicate whether a workload is valid.
`error`	`VARCHAR2`, return error set.

Parameter	Description
`owner_name`	Owner of the trace workload table.
`valid`	Return `DBMS_OLAP.VALID` or `DBMS_OLAP.INVALID` Indicate whether a workload is valid.
`error`	`VARCHAR2`, return error text.

Parameter	Description
`owner_name`	Owner of the user workload table.
`table_name`	User workload table name.
`valid`	Return `DBMS_OLAP.VALID` or `DBMS_OLAP.INVALID` Indicate whether a workload is valid.
`error`	`VARCHAR2`, return error set.

58 DBMS_OLAP

Using DBMS_OLAP

Overview

Views

SYSTEM.MVIEW_EVALUATIONS

Table 58-1 SYSTEM.MVIEW_EVALUATIONS

SYSTEM.MVIEW_EXCEPTIONS

Table 58-2 SYSTEM.MVIEW_EXCEPTIONS

SYSTEM.MVIEW_FILTER

Table 58-3 SYSTEM.MVIEW_FILTER

SYSTEM.MVIEW_FILTERINSTANCE

Table 58-4 SYSTEM.MVIEW_FILTERINSTANCE

SYSTEM.MVIEW_LOG

Table 58-5 SYSTEM.MVIEW_LOG

SYSTEM.MVIEW_RECOMMENDATIONS

Table 58-6 SYSTEM.MVIEW_RECOMMENDATIONS

SYSTEM.MVIEW_WORKLOAD

Table 58-7 SYSTEM.MVIEW_WORKLOAD

Deprecated Subprograms

Summary of DBMS_OLAP Subprograms

Table 58-8 DBMS_OLAP Package Subprograms

ADD_FILTER_ITEM Procedure

Syntax

Parameters

Table 58-9 ADD_FILTER_ITEM Procedure Parameters

CREATE_ID Procedure

Syntax

Parameters

Table 58-10 CREATE_ID Procedure Parameters

ESTIMATE_MVIEW_SIZE Procedure

Syntax

Parameters

Table 58-11 ESTIMATE_MVIEW_SIZE Procedure Parameters

EVALUATE_MVIEW_STRATEGY Procedure

Syntax

Parameters

Table 58-12 EVALUATE_MVIEW_STRATEGY Procedure Parameters

Usage Notes

GENERATE_MVIEW_REPORT Procedure

Syntax

Parameters

Table 58-13 GENERATE_MVIEW_REPORT Procedure Parameters

GENERATE_MVIEW_SCRIPT Procedure

Syntax

Parameters

Table 58-14 GENERATE_MVIEW_SCRIPT Procedure Parameters

LOAD_WORKLOAD_CACHE Procedure

Syntax

Parameters

Table 58-15 LOAD_WORKLOAD_CACHE Procedure Parameters

LOAD_WORKLOAD_TRACE Procedure

Syntax

Parameters

Table 58-16 LOAD_WORKLOAD_TRACE Procedure Parameters

LOAD_WORKLOAD_USER Procedure

Syntax

Parameters

Table 58-17 LOAD_WORKLOAD_USER Procedure Parameters

PURGE_FILTER Procedure

Syntax

Parameters

Table 58-18 PURGE_FILTER Procedure Parameters

PURGE_RESULTS Procedure

Syntax

Parameters

Table 58-19 PURGE_RESULTS Procedure Parameters

PURGE_WORKLOAD Procedure

Syntax

Parameters

Table 58-20 DBMS_OLAP.PURGE_WORKLOAD Procedure Parameters

RECOMMEND_MVIEW_STRATEGY Procedure

Syntax

Parameters

Table 58-21 RECOMMEND_MVIEW_STRATEGY Procedure Parameters

Usage Notes

SET_CANCELLED Procedure

Syntax

Parameters

Table 58-22 DBMS_OLAP.SET_CANCELLED Procedure Parameters

VALIDATE_DIMENSION Procedure

58
DBMS_OLAP