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
  • Views
  • Views
  • Deprecated Subprograms
• Summary of DBMS_OLAP Subprograms

---

Using DBMS_OLAP

• Overview
• Views
• Deprecated Subprograms

---

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.

• ADD_FILTER_ITEM Procedure
• CREATE_ID Procedure
• ESTIMATE_MVIEW_SIZE Procedure
• EVALUATE_MVIEW_STRATEGY Procedure
• GENERATE_MVIEW_REPORT Procedure
• GENERATE_MVIEW_SCRIPT Procedure
• LOAD_WORKLOAD_CACHE Procedure
• LOAD_WORKLOAD_TRACE Procedure
• PURGE_FILTER Procedure
• PURGE_RESULTS Procedure
• PURGE_WORKLOAD Procedure
• RECOMMEND_MVIEW_STRATEGY Procedure
• SET_CANCELLED Procedure
• VALIDATE_DIMENSION Procedure
• VALIDATE_WORKLOAD_CACHE Procedure
• VALIDATE_WORKLOAD_TRACE Procedure
• VALIDATE_WORKLOAD_USER Procedure

---

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: 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 58-9  ADD_FILTER_ITEM Procedure Parameters

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.

---

CREATE_ID Procedure

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 58-10  CREATE_ID Procedure Parameters

Parameter	Description
id	The unique identifier that can be used to identify a filter, a workload, or an Advisor run.

---

ESTIMATE_MVIEW_SIZE Procedure

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 58-11  ESTIMATE_MVIEW_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_MVIEW_STRATEGY Procedure

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 58-12  EVALUATE_MVIEW_STRATEGY Procedure Parameters

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.

Usage Notes

Periodically, the unused results can be purged from the system by calling the DBMS_OLAP.PURGE_RESULTS procedure.

---

GENERATE_MVIEW_REPORT 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 58-13  GENERATE_MVIEW_REPORT Procedure Parameters

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. Or use the parameter DBMS_OLAP.RUNID_ALL to indicate all advisor runs should be reported.
flags	Bit masked flags indicating what sections should be reported DBMS_OLAP.RPT_ACTIVITY -- Overall activities DBMS_OLAP.RPT_JOURNAL -- Runtime journals DBMS_OLAP.RPT_WORKLOAD_FILTER -- Filters DBMS_OLAP.RPT_WORKLOAD_DETAIL -- Workload information DBMS_OLAP.RPT_WORKLOAD_QUERY -- Workload query information DBMS_OLAP.RPT_RECOMMENDATION -- Recommendations DBMS_OLAP.RPT_USAGE -- Materialized view usage DBMS_OLAP.RPT_ALL -- All sections

---

GENERATE_MVIEW_SCRIPT Procedure

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 58-14 GENERATE_MVIEW_SCRIPT Procedure Parameters

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_ALL indicates all advisor runs should be reported.
tspace	Optional tablespace name to use when creating materialized views.

---

LOAD_WORKLOAD_CACHE Procedure

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 58-15  LOAD_WORKLOAD_CACHE Procedure Parameters

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.

---

LOAD_WORKLOAD_TRACE Procedure

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 58-16  LOAD_WORKLOAD_TRACE Procedure Parameters

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.

---

LOAD_WORKLOAD_USER Procedure

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 58-17  LOAD_WORKLOAD_USER Procedure Parameters

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.

---

PURGE_FILTER Procedure

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 58-18 PURGE_FILTER Procedure Parameters

Parameter	Description
filter_id	The parameter DBMS_OLAP.FILTER_ALL indicates all filters should be removed.

---

PURGE_RESULTS Procedure

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 58-19  PURGE_RESULTS Procedure Parameters

Parameter	Description
run_id	An ID generated with the DBMS_OLAP.CREATE_ID procedure. The ID should be associated with a RECOMMEND_MVIEW_STRATEGY or a EVALUATE_MVIEW_STRATEGY or a VALIDATE_DIMENSION run. Use the value DBMS_OLAP.RUNID_ALL to specify all such runs.

---

PURGE_WORKLOAD Procedure

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 58-20 DBMS_OLAP.PURGE_WORKLOAD Procedure Parameters

Parameter	Description
workload_id	An ID number originally assigned by the create_id call. If the value of workload_id is set to DBMS_OLAP.WORKLOAD_ALL, then all workloads for the current user will be deleted.

---

RECOMMEND_MVIEW_STRATEGY Procedure

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 58-21  RECOMMEND_MVIEW_STRATEGY Procedure Parameters

Parameter	Description
run_id	An ID generated by the DBMS_OLAP.CREATE_ID procedure to uniquely 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. If the workload_id is set to NULL, the call will use a hypothetical workload.
filter_id	An optional filter ID that maps to a set of user-supplied filter items. Use the parameter DBMS_OLAP.FILTER_NONE to avoid filtering.
storage_in_bytes	Maximum storage, in bytes, that can be used for storing materialized views. This number must be nonnegative.
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 (for example, nondimensional materialized views) are always retained.
retention_list	Comma-delimited list of materialized view table names. A drop recommendation is not made for any materialized view that appears in this list.
fact_table_filter	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.

---

SET_CANCELLED 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 58-22 DBMS_OLAP.SET_CANCELLED Procedure Parameters

Parameter	Description
run_id	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.

---

VALIDATE_DIMENSION Procedure

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 58-23 VALIDATE_DIMENSION Procedure Parameters

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.

Usage Notes

Periodically, the unused results can be purged from the system by calling the PURGE_RESULTS procedure.

---

VALIDATE_WORKLOAD_CACHE 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 58-24  VALIDATE_WORKLOAD_USER Procedure Parameters

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

---

VALIDATE_WORKLOAD_TRACE Procedure

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 58-25  VALIDATE_WORKLOAD_TRACE Procedure Parameters

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.

---

VALIDATE_WORKLOAD_USER Procedure

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

Table 58-26  VALIDATE_WORKLOAD_USER Procedure Parameters

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.