| Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-02 |
|
|
View PDF |
| Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-02 |
|
|
View PDF |
Note:
With Oracle Database 10g, 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 SQL Access 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 35, "DBMS_DIMENSION".
If you had previously used DBMS_OLAP.ESTIMATE_MVIEW_SIZE, you should now use DBMS_MVIEW.ESTIMATE_MVIEW_SIZE described in Chapter 61, "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 Database Data Warehousing Guide for more information.This chapter contains the following topics:
Overview
Views
Deprecated Subprograms
This section contains topics which relate to using the DBMS_OLAP package.
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.
Several views are created when using DBMS_OLAP. All are in the SYSTEM schema. To access these views, you must have a DBA role.
Table 65-1 SYSTEM.MVIEW_EVALUATIONS
| Column | NULL? | Datatype | Description |
|---|---|---|---|
|
|
|
|
Run ID identifying a unique Advisor call. |
|
|
|
|
Owner of materialized view. |
|
|
|
|
Name of an exiting materialized view in this database. |
|
|
|
|
Rank of this materialized view in descending order of |
|
|
|
|
Size of the materialized view in bytes. |
|
|
|
|
Number of times this materialized view appears in the workload. |
|
|
|
|
The cumulative benefit of the materialized view. |
|
|
|
|
The ratio of |
Table 65-2 SYSTEM.MVIEW_EXCEPTIONS
| Column | NULL? | Datatype | Description |
|---|---|---|---|
|
|
|
|
Run ID identifying a unique Advisor call. |
|
|
|
|
Owner name. |
|
|
|
|
Table name. |
|
|
|
|
Dimension name. |
|
|
|
|
Violated relation name. |
|
|
|
|
Location of offending entry. |
Table 65-3 SYSTEM.MVIEW_FILTER
| Column | NULL? | Datatype | Description |
|---|---|---|---|
|
|
|
|
Unique number used to identify the operation that used this filter. |
|
|
|
|
A unique ID number that groups all filter items together. A corresponding filter header record can be found in the |
|
|
|
|
Filter item number. |
|
|
|
|
String attribute for items that require strings. |
|
|
|
|
Numeric low for items that require numbers. |
|
|
|
|
Numeric high for items that require numbers. |
|
|
|
|
Date low for items that require dates. |
|
|
|
|
Date high for items that require dates. |
Table 65-4 SYSTEM.MVIEW_FILTERINSTANCE
| Column | NULL? | Datatype | Description |
|---|---|---|---|
|
|
|
|
Unique number used to identify the operation that used this filter. |
|
|
|
|
A unique ID number that groups all filter items together. A corresponding filter header record can be found in the |
|
|
|
|
Filter item number. |
|
|
|
|
Filter item type. |
|
|
|
|
String attribute for items that require strings. |
|
|
|
|
Numeric low for items that require numbers. |
|
|
|
|
Numeric high for items that require numbers. |
|
|
|
|
Date low for items that require dates. |
|
|
|
|
Date high for items that require dates. |
Table 65-5 SYSTEM.MVIEW_LOG
| Column | NULL? | Datatype | Description |
|---|---|---|---|
|
|
|
|
Unique number used to identify the table entry. The number must be created using the |
|
|
|
|
Optional filter ID. Zero indicates no user-supplied filter has been applied to the operation. |
|
|
|
|
Date at which the operation began. |
|
|
|
|
Date at which the operation ended. |
|
|
|
|
A name that identifies the type of operation. |
|
|
|
|
The current operational status. |
|
|
|
|
Informational message indicating current operation or condition. |
|
|
|
|
Number of steps completed by operation. |
|
|
|
|
Total number steps to be performed. |
|
|
|
|
Oracle error code in the event of an error. |
Table 65-6 SYSTEM.MVIEW_RECOMMENDATIONS
| Column | NULL? | Datatype | Description |
|---|---|---|---|
|
|
|
|
Run ID identifying a unique Advisor call. |
|
|
|
|
A comma-delimited list of fully qualified table names for structured recommendations. |
|
|
|
|
A comma-delimited list of grouping levels, if any, for structured recommendation. |
|
|
|
|
- |
|
|
|
|
Query text of materialized view if |
|
|
|
|
Unique identifier for this recommendation. |
|
|
|
|
|
|
|
|
|
Owner of the materialized view if |
|
|
|
|
Name of the materialized view if |
|
|
|
|
Actual or estimated storage in bytes. |
|
|
|
|
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 |
|
|
|
|
Ratio of the incremental improvement in performance to the size of the materialized view in bytes, or |
Table 65-7 SYSTEM.MVIEW_WORKLOAD
| Column | NULL? | Datatype | Description |
|---|---|---|---|
|
|
|
|
Optional application name for the query. |
|
|
|
|
Total cardinality of all of tables in query. |
|
|
|
|
Workload ID identifying a unique sampling. |
|
|
|
|
Number of times query executed. |
|
|
|
|
Date at which item was collected. |
|
|
|
|
Last date of execution. |
|
|
|
|
User who last executed query. |
|
|
|
|
User-supplied ranking of query. |
|
|
|
|
Query text. |
|
|
|
|
Id number identifying a unique query. |
|
|
|
|
Execution time in seconds. |
|
|
|
|
Total bytes selected by the query. |
The DBMS_OLAP subprograms have been replaced with improved technology: see Chapter 12, "DBMS_ADVISOR", Chapter 35, "DBMS_DIMENSION" and Chapter 61, "DBMS_MVIEW". All DBMS_OLAP subprograms are obsolete with Oracle Database 10g, and while Oracle will continue to support them, they are documented only for reasons of backward compatibility.
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 SQL Access 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 35, "DBMS_DIMENSION".
If you had previously used DBMS_OLAP.ESTIMATE_MVIEW_SIZE, you should now use DBMS_MVIEW.ESTIMATE_MVIEW_SIZE described in Chapter 61, "DBMS_MVIEW"
Table 65-8 DBMS_OLAP Package Subprograms
| Subprogram | Description |
|---|---|
|
Filters the contents being used during the recommendation process [see Deprecated Subprograms ] |
|
|
Generates an internal ID used by a new workload collection, a new filter, or a new Advisor run [see Deprecated Subprograms ] |
|
|
Estimates the size of a materialized view that you might create, in bytes and rows [see Deprecated Subprograms ] |
|
|
Measures the utilization of each existing materialized view [see Deprecated Subprograms ] |
|
|
Generates an HTML-based report on the given Advisor run [see Deprecated Subprograms ] |
|
|
Generates a simple script containing the SQL commands to implement Summary Advisor recommendations [see Deprecated Subprograms ] |
|
|
Obtains a SQL cache workload [see Deprecated Subprograms ] |
|
|
Loads a workload collected by Oracle Trace [see Deprecated Subprograms ] |
|
|
Loads a user-defined workload [see Deprecated Subprograms ] |
|
|
Deletes a specific filter or all filters [see Deprecated Subprograms ] |
|
|
Removes all results or those for a specific run [see Deprecated Subprograms ] |
|
|
Deletes all workloads or a specific collection [see Deprecated Subprograms ] |
|
|
Generates a set of recommendations about which materialized views should be created, retained, or dropped [see Deprecated Subprograms ] |
|
|
Stops the Advisor if it takes too long returning results [see Deprecated Subprograms ] |
|
|
Verifies that the relationships specified in a |
|
|
Validates the SQL Cache workload before performing load operations [see Deprecated Subprograms ] |
|
|
Validates the Oracle Trace workload before performing load operations [see Deprecated Subprograms ] |
|
|
Validates the user-supplied workload before performing load operations [see Deprecated Subprograms ] |
Note:
See Deprecated Subprograms.This procedure adds a new filter item to an existing filter to make it more restrictive. It also creates a filter to restrict what is analyzed for the workload.
Syntax
ADD_FILTER_ITEM ( filter_id IN NUMBER, filter_name IN VARCHAR2, string_list IN VARCHAR2, number_min IN NUMBER, number_max IN NUMBER, date_min IN VARCHAR2, date_max IN VARCHAR2);
Parameters
Table 65-9 ADD_FILTER_ITEM Procedure Parameters
| Parameter | Description |
|---|---|
|
|
An ID that uniquely describes the filter. It is generated by the |
|
|
|
|
|
A comma-delimited list of strings. This parameter is only used by the filter items of the string type. |
|
|
The lower bound of a numerical range. |
|
|
The upper bound of a numerical range, |
|
|
The lower bound of a date range. |
|
|
The upper bound of a date range. |
Note:
See Deprecated Subprograms.This procedure creates a unique identifier, which is used to identify a filter, a workload or results of an Advisor or dimension validation run.
Syntax
CALL DBMS_OLAP.CREATE_ID ( id OUT NUMBER);
Parameters
Table 65-10 CREATE_ID Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The unique identifier that can be used to identify a filter, a workload, or an Advisor run |
Note:
See Deprecated Subprograms.This procedure estimates the size of a materialized view that you might create, in bytes and number of rows.
Syntax
DBMS_OLAP.ESTIMATE_MVIEW_SIZE ( stmt_id IN VARCHAR2, select_clause IN VARCHAR2, num_rows OUT NUMBER, num_bytes OUT NUMBER);
Parameters
Table 65-11 ESTIMATE_MVIEW_SIZE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Arbitrary string used to identify the statement in an |
|
|
The |
|
|
Estimated cardinality |
|
|
Estimated number of bytes |
Note:
See Deprecated Subprograms.This procedure measures the utilization of each existing materialized view based on the materialized view usage statistics collected from the workload. The workload_id is optional. If not provided, EVALUATE_MVIEW_STRATEGY uses a hypothetical workload.
Syntax
DBMS_OLAP.EVALUATE_MVIEW_STRATEGY ( run_id IN NUMBER, workload_id IN NUMBER, filter_id IN NUMBER);
Parameters
Table 65-12 EVALUATE_MVIEW_STRATEGY Procedure Parameters
| Parameter | Description |
|---|---|
|
|
An ID generated by the |
|
|
An optional workload ID that maps to a workload in the current repository. Use the parameter |
|
|
Specify filter for the workload to be used. The value |
Usage Notes
Periodically, the unused results can be purged from the system by calling the DBMS_OLAP.PURGE_RESULTS procedure.
Note:
See Deprecated Subprograms.This procedure generates an HTML-based report on the given Advisor run.
Syntax
DBMS_OLAP.GENERATE_MVIEW_REPORT ( filename IN VARCHAR2, id IN NUMBER, flags IN NUMBER);
Parameters
Table 65-13 GENERATE_MVIEW_REPORT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
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. |
|
|
An ID that identifies an Advisor run. Or use the parameter |
|
|
Bit masked flags indicating what sections should be reported
|
Note:
See Deprecated Subprograms.This procedure generates a simple script containing the SQL commands to implement Summary Advisor recommendations.
Syntax
DBMS_OLAP.GENERATE_MVIEW_SCRIPT( filename IN VARCHAR2, id IN NUMBER, tspace IN VARCHAR2);
Parameters
Table 65-14 GENERATE_MVIEW_SCRIPT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
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. |
|
|
An ID that identifies an Advisor run. The parameter |
|
|
Optional tablespace name to use when creating materialized views. |
Note:
See Deprecated Subprograms.This procedure loads a SQL cache workload.
Syntax
DBMS_OLAP.LOAD_WORKLOAD_CACHE ( workload_id IN NUMBER, flags IN NUMBER, filter_id IN NUMBER, application IN VARCHAR2, priority IN NUMBER);
Parameters
Table 65-15 LOAD_WORKLOAD_CACHE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
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. |
|
|
Note: the flags have the same behavior irrespective of the |
|
|
Specify filter for the workload to be loaded |
|
|
The default business application name. This value will be used for a query if one is not found in the target workload. |
|
|
The default business priority to be assigned to every query in the target workload |
Note:
See Deprecated Subprograms.This procedure loads an Oracle Trace workload.
Syntax
DBMS_OLAP.LOAD_WORKLOAD_TRACE ( workload_id IN NUMBER, flags IN NUMBER, filter_id IN NUMBER, application IN VARCHAR2, priority IN NUMBER, owner_name IN VARCHAR2);
Parameters
Table 65-16 LOAD_WORKLOAD_TRACE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
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. |
|
|
Note: the flags have the same behavior irrespective of the |
|
|
Specify filter for the workload to be loaded |
|
|
The default business application name. This value will be used for a query if one is not found in the target workload. |
|
|
The default business priority to be assigned to every query in the target workload |
|
|
The schema that contains the Oracle Trace data. If omitted, the current user will be used |
Note:
See Deprecated Subprograms.This procedure loads a user-defined workload.
Syntax
DBMS_OLAP.LOAD_WORKLOAD_USER ( workload_id IN NUMBER, flags IN NUMBER, filter_id IN NUMBER, owner_name IN VARCHAR2, table_name IN VARCHAR2);
Parameters
Table 65-17 LOAD_WORKLOAD_USER Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The required id that was returned by the |
|
|
Note: the flags have the same behavior irrespective of the |
|
|
Specify filter for the workload to be loaded |
|
|
The schema that contains the user supplied table or view |
|
|
The table or view name containing valid workload data |
Note:
See Deprecated Subprograms.This procedure removes a filter at any time. You can delete a specific filter or all filters.
Syntax
DBMS_OLAP.PURGE_FILTER ( filter_id IN NUMBER);
Parameters
Table 65-18 PURGE_FILTER Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The parameter |
Note:
See Deprecated Subprograms.Many procedures in the DBMS_OLAP package generate output in system tables, such as recommendation results for RECOMMEND_MVIEW_STRATEGY and evaluation results for EVALUATE_MVIEW_STRATEGY, and dimension validation results for VALIDATE_DIMENSION. When these outputs are no longer required, they should be removed using the procedure PURGE_RESULTS. You can remove all results or those for a specific run.
Syntax
DBMS_OLAP.PURGE_RESULTS ( run_id IN NUMBER);
Parameters
Table 65-19 PURGE_RESULTS Procedure Parameters
| Parameter | Description |
|---|---|
|
|
An ID generated with the |
Note:
See Deprecated Subprograms.This procedure removes workloads when they are no longer needed. You can delete all workloads or a specific collection.
Syntax
DBMS_OLAP.PURGE_WORKLOAD ( workload_id IN NUMBER);
Parameters
Table 65-20 PURGE_WORKLOAD Procedure Parameters
| Parameter | Description |
|---|---|
|
|
An ID number originally assigned by the |
Note:
See Deprecated Subprograms.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 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.
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 65-21 RECOMMEND_MVIEW_STRATEGY Procedure Parameters
| Parameter | Description |
|---|---|
|
|
An ID generated by the |
|
|
An optional workload ID that maps to a workload in the current repository. Use the parameter If the |
|
|
An optional filter ID that maps to a set of user-supplied filter items. Use the parameter |
|
|
Maximum storage, in bytes, that can be used for storing materialized views. This number must be nonnegative. |
|
|
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 |
|
|
A comma-delimited list of materialized view table names. A drop recommendation is not made for any materialized view that appears in this list. |
|
|
Optional list of fact tables used to filter real or ideal workload |
Usage Notes
Periodically, the unused results can be purged from the system by calling the PURGE_RESULTS procedure.
Note:
See Deprecated Subprograms.If the Summary Advisor takes too long to make its recommendations using the procedures RECOMMEND_MVIEW_STRATEGY, you can stop it by calling the procedure SET_CANCELLED and passing in the run_id for this recommendation process.
Syntax
DBMS_OLAP.SET_CANCELLED ( run_id IN NUMBER);
Parameters
Table 65-22 SET_CANCELLED Procedure Parameters
| Parameter | Description |
|---|---|
|
|
ID that uniquely identifies an Advisor analysis operation. This call can be used to cancel a long running workload collection as well as an Advisor analysis session |
Note:
See Deprecated Subprograms.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.
The validation results are stored in system tables, which can be accessed through the view SYSTEM.MVIEW_EXCEPTIONS.
Syntax
DBMS_OLAP.VALIDATE_DIMENSION ( dimension_name IN VARCHAR2, dimension_owner IN VARCHAR2, incremental IN BOOLEAN, check_nulls IN BOOLEAN, run_id IN NUMBER);
Parameters
Table 65-23 VALIDATE_DIMENSION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the dimension to analyze |
|
|
Name of the dimension owner |
|
|
If |
|
|
|
|
|
An ID generated by the |
Usage Notes
Periodically, the unused results can be purged from the system by calling the PURGE_RESULTS procedure.
Note:
See Deprecated Subprograms.This procedure validates the SQL Cache workload before performing load operations.
Syntax
DBMS_OLAP.VALIDATE_WORKLOAD_CACHE ( valid OUT NUMBER, error OUT VARCHAR2);
Parameters
Table 65-24 VALIDATE_WORKLOAD_USER Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Return |
|
|
|
Note:
See Deprecated Subprograms.This procedure validates the Oracle Trace workload before performing load operations.
Syntax
DBMS_OLAP.VALIDATE_WORKLOAD_TRACE ( owner_name IN VARCHAR2, valid OUT NUMBER, error OUT VARCHAR2);
Parameters
Table 65-25 VALIDATE_WORKLOAD_TRACE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Owner of the trace workload table |
|
|
Return |
|
|
|
Note:
See Deprecated Subprograms.This procedure validates the user-supplied workload before performing load operations.
Syntax
DBMS_OLAP.VALIDATE_WORKLOAD_USER ( owner_name IN VARCHAR2, table_name IN VARCHAR2, valid OUT NUMBER, error OUT VARCHAR2);
Parameters
