DBMS_OLAP , 2 of 2

Summary of DBMS_OLAP Subprograms

Table 38-2 lists the subprograms available with DBMS_OLAP.

Table 38-2 DBMS_OLAP Package Subprograms

Subprogram	Description
ADD_FILTER_ITEM Procedure	Filters the contents being used during the recommendation process.
CREATE_ID Procedure	Generates an internal ID used by a new workload collection, a new filter, or a new advisor run
ESTIMATE_MVIEW_SIZE Procedure	Estimates the size of a materialized view that you might create, in bytes and rows.
EVALUATE_MVIEW_STRATEGY Procedure	Measures the utilization of each existing materialized view.
GENERATE_MVIEW_REPORT Procedure	Generates an HTML-based report on the given Advisor run
GENERATE_MVIEW_SCRIPT Procedure	Generates a simple script containing the SQL commands to implement Summary Advisor recommendations
LOAD_WORKLOAD_CACHE Procedure	Obtains a SQL cache workload.
LOAD_WORKLOAD_TRACE Procedure	Loads a workload collected by Oracle Trace.
LOAD_WORKLOAD_USER Procedure	Loads a user-defined workload.
PURGE_FILTER Procedure	Deletes a specific filter or all filters.
PURGE_RESULTS Procedure	Removes all results or those for a specific run.
PURGE_WORKLOAD Procedure	Deletes all workloads or a specific collection.
RECOMMEND_MVIEW_STRATEGY Procedure	Generates a set of recommendations about which materialized views should be created, retained, or dropped.
SET_CANCELLED Procedure	Stops the Advisor if it takes too long returning results.
VALIDATE_DIMENSION Procedure	Verifies that the relationships specified in a dimension are correct.
VALIDATE_WORKLOAD_CACHE Procedure	Validates the SQL Cache workload before performing load operations
VALIDATE_WORKLOAD_TRACE Procedure	Validates the Oracle Trace workload before performing load operations
VALIDATE_WORKLOAD_USER Procedure	Validates the user-supplied workload before performing load operations

ADD_FILTER_ITEM Procedure

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

Table 38-3 ADD_FILTER_ITEM Procedure Parameters

Parameter	Datatype	Description
filter_id	NUMBER	An ID that uniquely describes the filter. It is generated by the `DBMS_OLAP.CREATE_ID` procedure
filter_name	VARCHAR2	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 responsetime 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	VARCHAR2	A comma-delimited list of strings. This parameter is only used by the filter items of the string type
number_min	NUMBER	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	NUMBER	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	VARCHAR2	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	VARCHAR2	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

This 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);

Table 38-4 CREATE_ID Procedure Parameters

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

ESTIMATE_MVIEW_SIZE Procedure

This 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 38-5 ESTIMATE_MVIEW_SIZE Procedure Parameters

Parameter	Datatype	Description
stmt_id	NUMBER	Arbitrary string used to identify the statement in an `EXPLAIN PLAN`.
select_clause	STRING	The `SELECT` statement to be analyzed.
num_rows	NUMBER	Estimated cardinality.
num_bytes	NUMBER	Estimated number of bytes.

EVALUATE_MVIEW_STRATEGY Procedure

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.

See Also:

"DBMS_OLAP Interface Views"

Syntax

DBMS_OLAP.EVALUATE_MVIEW_STRATEGY (
run_id        IN NUMBER,
workload_id   IN NUMBER,
filter_id     IN NUMBER);

Parameters

Table 38-6 EVALUATE_MVIEW_STRATEGY Procedure Parameters

Parameter	Datatype	Description
run_id	NUMBER	An ID generated by the `DBMS_OLAP.CREATE_ID` procedure to identify results of a run
workload_id	NUMBER	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	NUMBER	Specify filter for the workload to be used. The value `DBMS_OLAP.FILTER_NONE` indicates no filtering

GENERATE_MVIEW_REPORT 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);

Table 38-7 GENERATE_MVIEW_REPORT Procedure Parameters

Parameter Datatype Description

filename

VARCHAR2

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

NUMBER

An ID that identifies an advisor run. Or use the parameter DBMS_OLAP.RUNID_ALL to indicate all advisor runs should be reported

flags

NUMBER

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

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

Table 38-8 GENERATE_MVIEW_SCRIPT Procedure Parameters

Parameter	Datatype	Description
filename	VARCHAR2	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	NUMBER	An ID that identifies an advisor run. The parameter `DBMS_OLAP.RUNID_AL`L indicates all advisor runs should be reported.
tspace	VARCHAR2	Optional tablespace name to use when creating materialized views.

LOAD_WORKLOAD_CACHE 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);

Table 38-9 LOAD_WORKLOAD_CACHE Procedure Parameters

Parameter	Datatype	Description
workload_id	NUMBER	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	NUMBER	`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	NUMBER	Specify filter for the workload to be loaded
application	VARCHAR2	The default business application name. This value will be used for a query if one is not found in the target workload
priority	NUMBER	The default business priority to be assigned to every query in the target workload

LOAD_WORKLOAD_TRACE 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);

Table 38-10 LOAD_WORKLOAD_TRACE Procedure Parameters

Parameter	Datatype	Description
collectionid	NUMBER	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	NUMBER	`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	NUMBER	Specify filter for the workload to be loaded
application	VARCHAR2	The default business application name. This value will be used for a query if one is not found in the target workload
priority	NUMBER	The default business priority to be assigned to every query in the target workload
owner_name	VARCHAR2	The schema that contains the Oracle Trace data. If omitted, the current user will be used

LOAD_WORKLOAD_USER Procedure

A user-defined workload is loaded using the procedure LOAD_WORKLOAD_USER.

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

Table 38-11 LOAD_WORKLOAD_USER Procedure Parameters

Parameter	Datatype	Description
workload_id	NUMBER	The required id that was returned by the `DBMS_OLAP.CREATE_ID` call
flags	NUMBER	`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	NUMBER	Specify filter for the workload to be loaded
owner_name	VARCHAR2	The schema that contains the user supplied table or view
table_name	VARCHAR2	The table or view name containing valid workload data

PURGE_FILTER Procedure

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.

Syntax

DBMS_OLAP.PURGE_FILTER (
filter_id    IN NUMBER);

Table 38-12 PURGE_FILTER Procedure Parameters

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

PURGE_RESULTS Procedure

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.

Syntax

DBMS_OLAP.PURGE_RESULTS (
run_id IN NUMBER);

Parameters

Table 38-13 PURGE_RESULTS Procedure Parameters

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

PURGE_WORKLOAD Procedure

When workloads are no longer needed, they can be removed using the procedure PURGE_WORKLOAD. You can delete all workloads or a specific collection.

Syntax

DBMS_OLAP.PURGE_WORKLOAD (
workload_id IN NUMBER);

Table 38-14 DBMS_OLAP.PURGE_WORKLOAD Procedure Parameters

Parameter	Datatype	Description
workload_id	NUMBER	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

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

See Also:

"DBMS_OLAP Interface Views"

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

SET_CANCELLED Procedure

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

Table 38-16 DBMS_OLAP.SET_CANCELLED Procedure Parameters

Parameter	Datatype	Description
run_id	NUMBER	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

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.

See Also:

"DBMS_OLAP Interface Views"

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

VALIDATE_WORKLOAD_CACHE Procedure

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

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

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

DBMS_OLAP Interface 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.

See Also:

Oracle9i Data Warehousing Guide for more information regarding how to use DBMS_OLAP

SYSTEM.MVIEW_EVALUATIONS

Table 38-21 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 38-22 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 38-23 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 38-24 SYSTEM.MVIEW_FILTER

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 38-25 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 38-26 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, Retain, Create, 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 38-27 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