Oracle9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2) Part Number A96612-01 |
|
DBMS_OLAP , 2 of 2
Table 38-2 lists the subprograms available with DBMS_OLAP
.
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.
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);
This creates a unique identifier, which is used to identify a filter, a workload or results of an advisor or dimension validation run.
CALL DBMS_OLAP.CREATE_ID ( id OUT NUMBER);
Parameter | Datatype | Description |
---|---|---|
id |
NUMBER |
The unique identifier that can be used to identify a filter, a workload, or an Advisor run |
This estimates the size of a materialized view that you might create, in bytes and number of rows.
DBMS_OLAP.ESTIMATE_MVIEW_SIZE ( stmt_id IN VARCHAR2, select_clause IN VARCHAR2, num_rows OUT NUMBER, num_bytes OUT NUMBER);
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.
Periodically, the unused results can be purged from the system by calling the DBMS_OLAP.PURGE_RESULTS
procedure.
DBMS_OLAP.EVALUATE_MVIEW_STRATEGY ( run_id IN NUMBER, workload_id IN NUMBER, filter_id IN NUMBER);
Generates an HTML-based report on the given Advisor run.
DBMS_OLAP.GENERATE_MVIEW_REPORT ( filename IN VARCHAR2, id IN NUMBER, flags IN NUMBER);
Generates a simple script containing the SQL commands to implement Summary Advisor recommendations.
DBMS_OLAP.GENERATE_MVIEW_SCRIPT( filename IN VARCHAR2, id IN NUMBER, tspace IN VARCHAR2);
Loads a SQL cache workload.
DBMS_OLAP.LOAD_WORKLOAD_CACHE ( workload_id IN NUMBER, flags IN NUMBER, filter_id IN NUMBER, application IN VARCHAR2, priority IN NUMBER);
Loads an Oracle Trace workload.
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);
A user-defined workload is loaded using the procedure LOAD_WORKLOAD_USER
.
DBMS_OLAP.LOAD_WORKLOAD_USER ( workload_id IN NUMBER, flags IN NUMBER, filter_id IN NUMBER, owner_name IN VARCHAR2, table_name IN VARCHAR2);
A filter can be removed at anytime by calling the procedure PURGE_FILTER
which is described as follows. You can delete a specific filter or all filters.
DBMS_OLAP.PURGE_FILTER ( filter_id IN NUMBER);
Parameter | Datatype | Description |
---|---|---|
filter_id |
NUMBER |
The parameter DBMS_OLAP.FILTER_ALL indicates all filters should be removed. |
Many procedures in the DBMS_OLAP
package generate output in system tables, such as recommendation results for DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY
and evaluation results for DBMS_OLAP.EVALUATE_MVIEW_STRATEGY
, dimension validation results for DBMS_OLAP.VALIDATE_DIMENSION
. These results can be accessed through a set of interface views, as shown in "DBMS_OLAP Interface Views". When they are no longer required, they should be removed using the procedure PURGE_RESULTS
. You can remove all results or those for a specific run.
DBMS_OLAP.PURGE_RESULTS ( run_id IN NUMBER);
When workloads are no longer needed, they can be removed using the procedure PURGE_WORKLOAD
. You can delete all workloads or a specific collection.
DBMS_OLAP.PURGE_WORKLOAD ( workload_id IN NUMBER);
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 DBMS_STATS.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
.
Periodically, the unused results can be purged from the system by calling the DBMS_OLAP.PURGE_RESULTS
procedure
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);
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.
DBMS_OLAP.SET_CANCELLED ( run_id IN NUMBER);
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
.
Periodically, the unused results can be purged from the system by calling the DBMS_OLAP.PURGE_RESULTS
procedure.
DBMS_OLAP.VALIDATE_DIMENSION ( dimension_name IN VARCHAR2, dimension_owner IN VARCHAR2, incremental IN BOOLEAN, check_nulls IN BOOLEAN, run_id IN NUMBER);
This procedure validates the SQL Cache workload before performing load operations.
DBMS_OLAP.VALIDATE_WORKLOAD_CACHE ( valid OUT NUMBER, error OUT VARCHAR2);
Parameter | Description |
---|---|
valid |
Return |
error |
|
This procedure validates the Oracle Trace workload before performing load operations.
DBMS_OLAP.VALIDATE_WORKLOAD_TRACE ( owner_name IN VARCHAR2, valid OUT NUMBER, error OUT VARCHAR2);
Parameter | Description |
---|---|
owner_name |
Owner of the trace workload table |
valid |
Return |
error |
|
This procedure validates the user-supplied workload before performing load operations.
DBMS_OLAP.VALIDATE_WORKLOAD_USER ( owner_name IN VARCHAR2, table_name IN VARCHAR2, valid OUT NUMBER, error OUT VARCHAR2);
Several views are created when using DBMS_OLAP
. All are in the SYSTEM
schema. To access these views, you must have a DBA role.
See Also:
Oracle9i Data Warehousing Guide for more information regarding how to use |
|
Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|