Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)
Part Number B14258-02
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Contact Us
Go to previous page
Previous		 Go to next page
Next
PDF · Mobi · ePub

65 DBMS_OLAP

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.

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:

Using DBMS_OLAP

This section contains topics which relate to using the DBMS_OLAP package.

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 65-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_CSOT_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 65-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 65-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 65-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 65-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 65-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 65-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 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.

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 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

ADD_FILTER_ITEM Procedure

Filters the contents being used during the recommendation process [see Deprecated Subprograms ]

CREATE_ID Procedure

Generates an internal ID used by a new workload collection, a new filter, or a new Advisor run [see Deprecated Subprograms ]

ESTIMATE_MVIEW_SIZE Procedure

Estimates the size of a materialized view that you might create, in bytes and rows [see Deprecated Subprograms ]

EVALUATE_MVIEW_STRATEGY Procedure

Measures the utilization of each existing materialized view [see Deprecated Subprograms ]

GENERATE_MVIEW_REPORT Procedure

Generates an HTML-based report on the given Advisor run [see Deprecated Subprograms ]

GENERATE_MVIEW_SCRIPT Procedure

Generates a simple script containing the SQL commands to implement Summary Advisor recommendations [see Deprecated Subprograms ]

LOAD_WORKLOAD_CACHE Procedure

Obtains a SQL cache workload [see Deprecated Subprograms ]

LOAD_WORKLOAD_TRACE Procedure

Loads a workload collected by Oracle Trace [see Deprecated Subprograms ]

LOAD_WORKLOAD_USER Procedure

Loads a user-defined workload [see Deprecated Subprograms ]

PURGE_FILTER Procedure

Deletes a specific filter or all filters [see Deprecated Subprograms ]

PURGE_RESULTS Procedure

Removes all results or those for a specific run [see Deprecated Subprograms ]

PURGE_WORKLOAD Procedure

Deletes all workloads or a specific collection [see Deprecated Subprograms ]

RECOMMEND_MVIEW_STRATEGY Procedure

Generates a set of recommendations about which materialized views should be created, retained, or dropped [see Deprecated Subprograms ]

SET_CANCELLED Procedure

Stops the Advisor if it takes too long returning results [see Deprecated Subprograms ]

VALIDATE_DIMENSION Procedure

Verifies that the relationships specified in a dimension are correct [see Deprecated Subprograms ]

VALIDATE_WORKLOAD_CACHE Procedure

Validates the SQL Cache workload before performing load operations [see Deprecated Subprograms ]

VALIDATE_WORKLOAD_TRACE Procedure

Validates the Oracle Trace workload before performing load operations [see Deprecated Subprograms ]

VALIDATE_WORKLOAD_USER Procedure

Validates the user-supplied workload before performing load operations [see Deprecated 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 65-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-workloads 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-workloads frequency column.

  • LASTUSE: Date-workloads lastuse column. Not used by SQL Cache workload.

  • OWNER: String-workloads owner column. Expected in uppercase unless owner defined explicitly to be not all in uppercase.

  • PRIORITY: Numerical-workloads priority column. Not used by SQL Cache workload.

  • RESPONSETIME: Numerical-workloads 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 65-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 65-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 65-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 65-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 65-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 65-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 65-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 65-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 65-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 65-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 65-20 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 65-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

A 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 65-22 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 65-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 non-NULL; otherwise, this check is omitted.

  • Specify FALSE when non-NULLness 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 65-24 VALIDATE_WORKLOAD_USER Procedure Parameters

Parameter Description

valid

Return DBMS_OLAP.VALID or DBMS_OLAP.INVALID. Indicates 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 65-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. Indicates 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 65-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

Go to previous page
Previous		 Go to next page
Next
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Contact Us