| Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-01 |
|
|
View PDF |
| Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-01 |
|
|
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 |
|---|---|---|---|
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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 |
|---|---|
| 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 ] |
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 |
|
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. |
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 |
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 |
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.
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
|
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. |
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 |
Note: the flags have the same behavior irrespective of the |
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 |
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 |
Note: the flags have the same behavior irrespective of the |
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 |
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 |
Note: the flags have the same behavior irrespective of the |
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 |
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. |
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. |
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. |
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 |
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 |
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.
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 |
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 |
|
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.
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 |
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 |
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
