Overview of the Summary Advisor in the DBMS_OLAP Package

Materialized views provide high performance for complex, data-intensive queries. The Summary Advisor helps you achieve this performance benefit by choosing the proper set of materialized views for a given workload. In general, as the number of materialized views and space allocated to materialized views is increased, query performance improves. But the additional materialized views have some cost: they consume additional storage space and must be refreshed, which increases maintenance time. The Summary Advisor considers these costs and makes the most cost-effective trade-offs when recommending the creation of new materialized views and evaluating the performance of existing materialized views.

To help you select from among the many possible materialized views in your schema, Oracle provides a collection of materialized view analysis and advisory functions and procedures in the DBMS_OLAP package. Collectively, these functions are called the Summary Advisor, and they are callable from any PL/SQL program. Figure 16-1 shows how the Summary Advisor recommends materialized views from a hypothetical or user-defined workload or one obtained from the SQL cache, or Oracle Trace. You can run the Summary Advisor from Oracle Enterprise Manager or by invoking the DBMS_OLAP package. You must have Java enabled to use the Summary Advisor.

All data and results generated by the Summary Advisor is stored in a set of tables referred to as the Summary Advisor repository. These tables are owned by SYSTEM and start with MVIEW$_ADV_*. Only DBAs can access these tables directly, but other users can access the data relevant to them using a set of read-only views. These views start with MVIEW_. Thus, the table MVIEW$_ADV_WORKLOAD stores the workload of all users, but a user accesses his workload through the MVIEW_WORKLOAD view.

All of these tasks can be performed independently of one another. However, sometimes you need to use several procedures from the DBMS_OLAP package to complete a task. For example, to recommend a set of materialized views based on a workload, you have to first load the workload and then generate the set of recommendations.

Before you can use any of these procedures, you must create a unique identifier for the data they are about to create. This number is obtained by calling the procedure CREATE_ID and the unique number is known subsequently as a run ID, workload ID or filter ID depending on the procedure it is given.

The identifier is used to store the Advisor artifacts in the repository. Each activity in the Advisor requires a unique identifier to distinguish it from other objects. For example, when you add a filter item, you associate the item with a filter ID. When you load a workload, the data gets stored using the unique workload ID. In addition, when you run RECOMMEND_MVIEW_STRATEGY or EVALUATE_MVIEW_STRATEGY, a unique ID is associated with the run.

Because the ID is just a unique number, Oracle uses the same CREATE_ID function to acquire the value. It is only when a specific operation is performed (such as a load workload) that the ID is identified as a workload ID.

You can use the Summary Advisor with or without a workload, but better results are achieved if a workload is provided. This can be supplied by:

The user

Oracle Trace

The current SQL cache contents

Once the workload is loaded into the Advisor workload repository or at the time the materialized view recommendations are generated, a filter can be applied to the workload to restrict what is analyzed. This provides the ability to generate different sets of recommendations based on different workload scenarios.

These filters are created using the procedure ADD_FILTER_ITEM. You can create any number of filters, and use more than one at a time to filter a workload. See "Using Filters with the Summary Advisor" for further details.

The Summary Advisor uses four types of schema objects, some of which are defined in the user's schema and some are in the system schema:

User schema

For both V-table and workload tables, before the workload is available to the recommendation process. It must be loaded into the advisor workload repository.

V-tables

V-tables are generated by Oracle Trace for storing results of formatting server-collected trace. Please note that these V-tables are different from the V$ tables.

Workload tables

Workload tables are user tables that store workload information, and can reside in any schema.

System schema

Result tables

Result tables are internal tables that store both intermediate and final results from all Summary Advisor components.

Whenever the Summary Advisor is run, the results, with the exception of estimated size, are placed in internal tables, which can be accessed from read-only views in the database. These results can be queried, so you do not have to keep running the Advisor process.

If you want to view the results of the last materialized view recommendation, you can issue the following statement:

The advisory functions and procedures of the DBMS_OLAP package require you to gather structural statistics about fact and dimension table cardinalities, and the distinct cardinalities of every dimension level column, JOINKEY column, and fact table key column. You do this by loading your data warehouse, then gathering either exact or estimated statistics with the DBMS_STATS package or the ANALYZETABLE statement. Because gathering statistics is time-consuming and extreme statistical accuracy is not required, it is generally preferable to estimate statistics.

Using information from the system workload table, schema metadata and statistical information generated by the DBMS_STATS package, the Advisor engine generates summary recommendations and summary usage evaluations and stores the results in result tables.

To use the Summary Advisor with a workload, some or all of the following steps must be followed:

Optionally obtain an identifier number as a filter ID and define one or more filter items.

Obtain an identifier number as a workload ID and load a workload. If a filter was defined in step 1, then it can be used during the operation to refine the SQL statements as they are collected from the workload source. Load the workload.

Call the procedure RECOMMEND_MVIEW_STRATEGY to generate the recommendations.

These steps can be repeated several times with different workloads to see the effect on the materialized views.

Identifier Numbers

Most of the DBMS_OLAP procedures require a unique identifier as one of their parameters. You obtain this by calling the procedure CREATE_ID, which is illustrated in the following section.

DBMS_OLAP.CREATE_ID Procedure

Table 16-1 DBMS_OLAP.CREATE_ID Procedure Parameters

Parameter

Datatype

Description

id

NUMBER

The unique identifier that can be used to create a filter, load a workload, or create an analysis

With a SQL utility such as SQL*Plus, do the following:

Declare an output variable to receive the new identifier.

VARIABLE MY_ID NUMBER;

Call the CREATE_ID function to generate a new identifier.

EXECUTE DBMS_OLAP.CREATE_ID(:MY_ID);

Workload Management

The Advisor performs best when a workload based on usage is available. The Advisor Workload Repository is capable of storing multiple workloads, so that the different uses of a real-world data warehousing environment can be viewed over a long period of time and across the life cycle of database instance startup and shutdown.

To facilitate wider use of the Summary Advisor, three types of workload are supported:

Current contents of the SQL cache

Oracle Trace collection

User-specified workload

When the workload is loaded using the appropriate load_workload procedure, it is stored in a new workload repository in the SYSTEM schema called MVIEW_WORKLOAD whose format is shown in Table 16-2. A specific workload can be removed by calling the PURGE_WORKLOAD routine and passing it a valid workload ID. To remove all workloads for the current user, call PURGE_WORKLOAD and pass the constant value DBMS_OLAP.WORKLOAD_ALL.

Table 16-2 MVIEW_WORKLOAD Table

Column

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

Once the workload has been collected using the appropriate LOAD_WORKLOAD routine, there is also a filter mechanism that may be applied, this lets you specify the portion of workload that is to be loaded into the repository. You can also use the same filter mechanism to restrict workload-based summary recommendation and evaluation to a subset of the queries contained in the workload repository. Once the workload has been loaded, the Summary Advisor is run by calling the procedure RECOMMEND_MVIEW_STRATEGY. A major benefit of this approach is that it is easy to model different workloads by simply modifying the frequency column, removing some SQL queries, or adding new queries.

Summary Advisor can retrieve workload information from the SQL cache as well as Oracle Trace. If the collected data was retrieved from a server with the instance parameter cursor_sharing set to SIMILAR or FORCE, then user queries with embedded literal values will be converted to a statement that contains system-generated bind variables.

Note:

Oracle Trace will be deprecated in a future release.

In Oracle9i, it is not possible to retrieve the bind-variable data in order to reconstruct the statement in the form originally submitted by the user. This will, in turn, cause Summary Advisor to not consider the query for rewrite and potentially miss a critical statement in the user's workload. As a work-around, if the Advisor will be used to recommend materialized views, then the server should set the instance parameter CURSOR_SHARING to EXACT.

Loading a User-Defined Workload

A user-defined workload is loaded using the procedure LOAD_WORKLOAD_USER. The workload_id is obtained by calling the procedure CREATE_ID. The value of the flags parameter determines whether the workload is considered to be new, should be used to overwrite an existing workload, or should be appended to an existing workload. The optional filter_id can be supplied to specify the filter that is to be used against this workload. Where the filter would have been defined using the ADD_FILTER_ITEM procedure.

DBMS_OLAP.LOAD_WORKLOAD_USER Procedure

Table 16-3 DBMS_OLAP.LOAD_WORKLOAD_USER Procedure Parameters

Parameter

Datatype

Description

workload_id

NUMBER

The required workload id that was returned by the create_id call

flags

NUMBER

Can take one of the following values:

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

The actual workload is defined in a separate table and the two parameters owner_name and table_name describe where it is stored. There is no restriction on which schema the workload resides in, the name for the table, or how many of these user-defined tables exist. The only restriction is that the format of the user table must correspond to the USER_WORKLOAD table, as described in Table 16-4:

Loading a Trace Workload

Alternatively, you can collect a Trace workload from Oracle Enterprise Manager to gather dynamic information about your query workload, which can be used by an advisory function. If Oracle Trace is available, consider using it to collect materialized view usage. Doing so enables you to see which materialized views are in use. It also lets the Advisor detect any unusual query requests from users that would result in recommending some different materialized views.

A workload collected by Oracle Trace is loaded using the procedure LOAD_WORKLOAD_TRACE. You obtain workload_id by calling the procedure CREATE_ID. The value of the flags parameter will determine whether the workload is considered new, should be used to overwrite an existing workload or should be appended to an existing workload. The optional filter ID can be supplied to specify the filter that is to be used against this workload. In addition, you can specify an application name to describe this workload and give every query a default priority. The application name is simply a tag that enables you to classify the workload query. The name can later be used to filter the workload during a RECOMMEND_MVIEW_STRATEGY or EVALUATE_MVIEW_STRATEGY operation.

The priority is an important piece of information. It tells the Advisor how important the query is to the business. When recommendations are formed, the priority will determine its value and will cause the Advisor to make decisions that favor higher ranking queries.

If the owner_name parameter is not defined, then the procedure will expect to find the formatted trace tables in the schema for the current user.

DBMS_OLAP.LOAD_WORKLOAD_TRACE Procedure

Table 16-5 DBMS_OLAP.LOAD_WORKLOAD_TRACE Procedure Parameters

Parameter

Datatype

Description

workload_id

NUMBER

The required id that was returned by the CREATE_ID call

flags

NUMBER

Can take one of the following values:

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

Oracle Trace collects two types of data. One is a duration event which causes a data item to be collected twice: once at the start of the operation and once at the end of the operation. The duration of the data item is the difference between the start and end of the operation. For example, execution time is collected as a duration event. It first collects the clock time when the operation starts. Then it collects the clock time when the operation ends. Execution time is calculated by subtracting the start time from the end time.

A point event is a static data item that doesn't change over time. For example, an owner name is a static data item that would be the same at the start and the end of an operation.

To collect, analyze and load the summary event set, you must do the following:

Set six initialization parameters to collect data using Oracle Trace. Enabling these parameters incurs some additional overhead at database connection, but is otherwise transparent.

ORACLE_TRACE_COLLECTION_NAME = oraclesm or oraclee

ORACLEE is the Oracle Expert collection which contains Summary Advisor data and additional data that is only used by Oracle Expert.

ORACLESM is the Summary Advisor collection that contains only Summary Advisor data and is the preferred collection type.

Run the Oracle Trace Manager, specify a collection name, and select the SUMMARY_EVENT set. Oracle Trace Manager reads information from the associated configuration file and registers events to be logged with Oracle. While collection is enabled, the workload information defined in the event set gets written to a flat log file.

When collection is complete, Oracle Trace automatically formats the Oracle Trace log file into a set of relations, which have the predefined synonyms beginning with V_192216243_. Alternatively, the collection file, which usually has an extension of .CDF, can be formatted manually using the otrcfmt utility, as shown in this example:

otrcfmt collection_name.cdf user/password@database

The trace data can be formatted in any schema. The LOAD_WORKLOAD_TRACE call lets you specify the location of the data.

Run the GATHER_TABLE_STATS procedure of the DBMS_STATS package or ANALYZE ... ESTIMATESTATISTICS to collect cardinality statistics on all fact tables, dimension tables, and key columns (any column that appears in a dimension LEVEL clause or JOIN clause of a CREATEDIMENSION statement).

Run the CREATE_ID procedure of the DBMS_OLAP package to get a unique workload_id for this workload.

Run the LOAD_WORKLOAD_TRACE procedure of the DBMS_OLAP package to load this workload into the repository.

Once these six steps have been completed, you will be ready to make recommendations about your materialized views. An example of how to load a trace workload is illustrated as follows.

Loading a SQL Cache Workload

You obtain a SQL cache workload using the procedure LOAD_WORKLOAD_CACHE. At the time this procedure is called, the current contents of the SQL cache are analyzed and placed into the read-only view SYSTEM.MVIEW_WORKLOAD.

You obtain workload_id by calling the procedure CREATE_ID. The value of the flags parameter determines whether the workload is treated as new, should be used to overwrite an existing workload, or should be appended to an existing workload. The optional filter ID can be supplied to specify the filter that is to be used against this workload. Where the filter would have been defined using the ADD_FILTER_ITEM procedure. In addition, you can specify an application name to describe this workload and give every query a default priority.

DBMS_OLAP.LOAD_WORKLOAD_CACHE Procedure

Table 16-6 DBMS_OLAP.LOAD_WORKLOAD_CACHE Procedure Parameters

Parameter

Datatype

Description

workload_id

NUMBER

The required ID that was returned by the CREATE_ID call

flags

NUMBER

Can take one of the following values:

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. The value DBMS_OLAP.FILTER_NONE indicates no filtering

application

VARCHAR2

String workload's application column. Not used by SQL Cache workload

priority

NUMBER

The default business priority to be assigned to every query in the target workload

Removing a Workload

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 Procedure

Table 16-7 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 workload collections for the current user will be deleted

The following is an example of removing a specific workload:

VARIABLE workload_id NUMBER;
DBMS_OLAP.PURGE_WORKLOAD(:workload_id);

The following example removes all workloads:

EXECUTE DBMS_OLAP.PURGE_WORKLOAD(DBMS_OLAP.WORKLOAD_ALL);

Using Filters with the Summary Advisor

The entire contents of a workload do not have to be used during the recommendation process. Any workload can be filtered by creating a filter item using the procedure ADD_FILTER_ITEM, which is described is Table 16-8.

DBMS_OLAP.ADD_FILTER_ITEM Procedure

Table 16-8 DBMS_OLAP.ADD_FILTER_ITEM Procedure Parameters

Parameter

Datatype

Description

filter_id

NUMBER

An ID that uniquely describes the filter. It is generated by the create_id call

filter_name

VARCHAR2

APPLICATIONString-workload's application column

BASETABLEString-based tables referenced by workload queries. Name must be fully qualified including owner and table name (for example, SH.SALES)

CARDINALITYNumerical-sum of cardinality of the referenced base tables

FREQUENCYNumerical-workload's frequency column

LASTUSEDate-workload's lastuse column. Not used by SQL Cache workload

OWNERString-workload's owner column. Expected in uppercase unless owner defined explicitly to be not all in uppercase

PRIORITYNumerical-workload's priority column. Not used by SQL Cache workload

RESPONSETIMENumerical-workload's responsetime column. Not used by SQL Cache workload

SCHEMAString-based schema referenced be workload queries.

TRACENAMEString-list of oracle trace collection names. Only used by a Trace Workload

string_list

VARCHAR2

A comma-delimited list of strings

number_min

NUMBER

The lower bound of a numerical range. NULL represents the lowest possible value

number_max

NUMBER

The upper bound of a numerical range, NULL for no upper bound. NULL represents the highest possible value

The Advisor supports ten different filter item types. For each filter item, Oracle stores an attribute that tells Advisor how to apply the selection rule. For example, an APPLICATION item requires a string attribute that can be either a single name as in GREG, or it can be a comma-delimited list of names like GREG, ROSE, KALLIE, HANNAH. For a single name, the Advisor takes the value and only accept the workload query if the application name exactly matches the supplied name. For a list of names, the queries application name must appear in the list. Referring to my example, a query whose application name is GREG would match either a single application filter item containing GREG or the list GREG, ROSE, KALLIE, HANNAH. Conversely, a query whose application is KALLIE will only match the filter item list GREG, ROSE, KALLIE, HANNAH.

For numeric filter items such as CARDINALITY, the attribute represents a possible range of values. Advisor will determine if the filter item represents a bounded range such as 500 to 1000000, or it could be an exact match like 1000 to 1000. When the range value is specified as NULL, then the value is infinitely small or large, depending upon which attribute is set.

Data filters, such as LASTUSE behave similar to numeric filter except Advisor treats the range test as two dates. A value of NULL indicates infinity.

You can define a number of different types of filter as shown in Table 16-9.

Table 16-9 Workload Filters and Attribute Types

Filter Item Name

string_list

number_min

number_max

date_min

date_max

Description

APPLICATION

Required

N/A

N/A

N/A

N/A

Query should be from the list applications defined in string_list. Multiple application names must separated by commas

CARDINALITY

N/A

Required

Required

N/A

N/A

Sum of cardinalities of base tables found in a query

LASTUSE

N/A

N/A

N/A

Required

Required

Last execution date of the query

FREQUENCY

N/A

Required

Required

N/A

N/A

Number of executions for the query

OWNER

Required

N/A

N/A

N/A

N/A

List of database users who executed queries. Multiple owners must be separated by commas

PRIORITY

N/A

Required

Required

N/A

N/A

User-supplied priority value

BASETABLE

Required

N/A

N/A

N/A

N/A

List of fully qualified tables that appear in a candidate query. Multiple tables must be separated by commas

RESPONSETIME

N/A

Required

Required

N/A

N/A

Query response time in seconds

SCHEMA

Required

N/A

N/A

N/A

N/A

Query should be from the list schemas defined in string_list. Multiple schema names must separated by commas

TRACENAME

Required

N/A

N/A

N/A

N/A

List of Oracle Trace collection names. If this filter is not used, then the collection operation will choose the entire Oracle Trace collection, regardless of it collection name. Multiple names must be separated by commas

When dealing with a workload, the client can optionally attach a filter to reduce or refine the set of target SQL statements. If no filter is attached, then all target SQL statements will be collected or used.

A new filter can be created with the CREATE_ID call. Filter items can be added to the filter by using the ADD_FILTER_ITEM call. When a filter is created, an entry is stored in the read-only view SYSTEM.MVIEW_FILTER.

The following is an example illustrating how to add three different types of filter

This example defines a filter with three filter items. The first filter will only allow queries that reference the table SCOTT.EMP. The second item will accept queries that were executed by one of the users SCOTT, PAYROLL or PERSONNEL. Finally, the third filter item accepts queries that execute at least 500 times.

Note, all filter items must match for a single query to be accepted. If any of the items fail to match, then the query will not be accepted.

In the previous example, three filters will be applied against the data. However, each filter item could have created with its only unique filter id, thus creating three different filters as illustrated in the following:

Removing a Filter

A filter can be removed at anytime by calling the procedure PURGE_FILTER, which is described in the following table. You can delete a specific filter or all filters. You can remove all filters using the purge_filter call by specifying DBMS_OLAP.FILTER_ALL as the filter ID.

DBMS_OLAP.PURGE_FILTER Procedure

Table 16-10 DBMS_OLAP.PURGE_FILTER Procedure Parameters

Parameter

Datatype

Description

filterid

NUMBER

A filter ID number used to identify the filter to be deleted

DBMS_OLAP.PURGE_FILTER Example

Recommending Materialized Views

The analysis and advisory procedure for materialized views is RECOMMEND_MVIEW_STRATEGY in the DBMS_OLAP package. This procedure automatically recommends which materialized view to create, retain, or drop. RECOMMEND_MVIEW_STRATEGY uses structural statistics and optionally workload statistics.

You can call this procedure to obtain a list of materialized view recommendations that you can select, modify, or reject. Alternatively, you can use the DBMS_OLAP package directly in your PL/SQL programs for the same purpose.

To use the Summary Advisor, you must have the SELECTANYTABLE privilege.

The parameters for RECOMMEND_MVIEW_STRATEGY and their descriptions are given in Table 16-11.

RECOMMEND_MVIEW_STRATEGY Procedure Parameters

Table 16-11 RECOMMEND_MVIEW_STRATEGY Parameters

Parameter

I/O

Datatype

Description

run_id

IN

NUMBER

A return value that uniquely identifies the current operation

workoad_id

IN

NUMBER

An optional workload ID that maps to a workload in the current repository

filter_id

IN

NUMBER

An optional filter ID that maps to a set of user-supplied filter items

storage_in_
bytes

IN

NUMBER

Maximum storage, in bytes, that can be used for storing materialized views. This number must be non-negative

retention_pct

IN

NUMBER

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, non-dimensional materialized views) are always retained

retention_list

IN

VARCHAR2

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

IN

VARCHAR2

Comma-delimited list of fact table names to analyze, or NULL to analyze all fact tables

The results from calling this package are put in the table SYSTEM.MVIEW_RECOMMENDATIONS shown in Table 16-12. The output can be queried directly using the MVIEW_RECOMMENDATION table or a structured report can be generated using the DBMS_OLAP.GENERATE_MVIEW_REPORT procedure.

Owner of the materialized view summary 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 Storage

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. Performance gain

BENEFIT_TO_COST_RATIO

NUMBER

Ratio of the incremental improvement in performance to the size of the materialized view in bytes, or NULL if unknown. Benefit / Cost

Summary Advisor Usage Examples

The following are several examples of how you can use the Summary Advisor recommendation process.

Example 1 Summary Advisor (USER_WORKLOAD)

In this example, a workload is loaded from the table USER_WORKLOAD and no filtering is applied to the workload. The fact table is called sales.

SQL Script Generation

When the Summary Advisor is run using Oracle Enterprise Manager the facility is provided to implement the advisors recommendations. But when the procedure RECOMMEND_MVIEW_STRATEGY is called directly the procedure GENERATE_MVIEW_SCRIPT must be used to create a script which will implement the advisors recommendations. The parameters are as follows:

The resulting script is a executable SQL file that can contain DROP and CREATE statements for materialized views. For new materialized views, the name of the materialized views is auto-generated by combining the user-specified ID and the Rank value of the materialized views. It is recommended that the user review the generated SQL script before attempting to execute it.

The filename specification requires the same security model as described in the GENERATE_MVIEW_REPORT routine.

Summary Data Report

A Summary Data Report offers you data about workloads and filters, and then generates recommendations. The report format is HTML and the contents are the following:

Activity Journal Details

This section describes the recorded data. A journal is simply a mechanism to permit the Advisor to record any interesting event that may occur during processing. During processing, many decisions can made by the Advisor that are not necessarily visible to you. The journal enables you to see the internal processes and steps taken by the Summary Advisor. It contains work-in-progress messages, debugging messages and error messages for a particular Advisor element.

Activity Log Details

This section describes the various Advisor activities that have been executed by the current user. Activities include workload filter maintenance, workload collections and analysis operations.

Materialized View Recommendations

This section contains detail information regarding Advisor analysis sessions. It presents various recommendations on the creation of new materialized views as well as the removal of inappropriate or expensive materialized views.

Materialized View Usage

This section describes the Advisor's results from an evaluation of existing materialized views.

Workload Collection Details

The workload report lists the details of each SQL query for the current user's workload collections. The report is arranged by table references.

Workload Filter Details

The workload filter report lists details of workload filters for the current user.

Workload Query Details

This report contains the actual SQL queries for the current user's workload collections. Each query can be linked back to an entry in the Workload report.

PL/SQL Interface Syntax

Table 16-14 GENERATE_MVIEW_REPORT Parameters

Parameter

Description

file_name

A valid output file specification. Note, the Oracle9i restricts file access within Oracle Stored Procedures. This means that file locations and names must adhere to the known file permissions in the Policy Table. See the Security and Performance section of the Oracle9i Java Developer's Guide for more information on file permissions

id

The Advisor ID number used to collect or analyze data. NULL indicates all data for the requested section

flags

Report flags to indicate required detail sections. Multiple sections can be selected by referencing the following constants.

RPT_ALL

RPT_ACTIVITY

RPT_JOURNAL

RPT_RECOMMENDATION

RPT_USAGE

RPT_WORKLOAD_DETAIL

RPT_WORKLOAD_FILTER

RPT_WORKLOAD_QUERY

Because of the Oracle security model, report output file directories must be granted read and write permission prior to executing this call. The call is described in Oracle9i Java Developer's Guide and is as follows:

This produces the HTML file /usr/mydev/myname/report.html. In this example, report.html is the Table of Contents for the report. It will contain links to each section of the report, which are found in external files with names derived from the original filename. Because no ID was specified for the second parameter, all data for the current user will be reported. If, for example, you want only a report on a particular recommendation run, then that run ID should be passed into the call. The report can generate the following HTML files:

HTML File

Description

xxxx.html

Table of Contents

xxxx_log.html

Activity Section

xxxx_jou.html

Journal Section

xxxx_fil.html

Workload Filter Section

xxxx_wrk.html

Workload Section

xxxx_rec.html

Materialized View Recommendation Section

xxxx_usa.html

Materialized View Usage Section

In this table, xxxx is the filename portion of the user-supplied file specification.

All files appear in the same directory, which is the one you specify.

When Recommendations are No Longer Required

Every time the Summary Advisor is run, a new set of recommendations is created. 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 Procedure

Table 16-15 DBMS_OLAP.PURGE_RESULTS Procedure Parameters

Parameter

Datatype

Description

run_id

NUMBER

An ID used to identify the results to delete

EXECUTE DBMS_OLAP.PURGE_RESULTS (DBMS_OLAP.RUNID_ALL);

Stopping the Recommendation Process

If the Summary Advisor takes too long to make its recommendations using the procedure 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 Procedure

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

Summary Advisor and Missing Statistics

The Summary Advisor will only perform materialized view analysis on table objects that contain a complete set of statistics as generated by the SQL ANALYZE statement or the DBMS_STATS package. While running Summary Advisor, the following Oracle error can occur:

QSM-00508: statistics missing on tables/columns

If this error occurs, then at least one table or column is missing the required statistics. To determine which object has missing statistics, issue the following statement:

Database statistics are required for both the table and its set of defined columns. A common mistake occurs when the user only checks for valid table statistics, unaware that the column statistics have not been set.

Summary Advisor Privileges and ORA-30446

When processing a workload, the Summary Advisor attempts to validate each statement in order to identify table and column references. If the current database user does not have select privileges to a particular table, the Advisor bypasses the statement referencing the table. This may cause many statements to be excluded from analysis. If the Advisor excludes all statements in a workload, the workload is invalid and the Advisor returns the following message:

ORA-30446, valid workload queries not found

To avoid missing critical workload queries, the current database user must have select privileges on the tables that are targeted for materialized view analysis. Moreover, these select privileges cannot be obtained through a role.

Estimating Materialized View Size

A materialized view occupies storage space in the database, so it is helpful to know how much space will be required before it is created. Rather than guess or wait until it has been created and then discover that insufficient space is available in the tablespace, use the procedure ESTIMATE_MVIEW_SIZE. Calling this procedure instantly returns an estimate of the size in bytes for the materialized view. Table 16-17 lists the parameters to this procedure.

ESTIMATE_MVIEW_SIZE Parameters

Table 16-17 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

ESTIMATE_SUMMARY_SIZE returns the following:

The number of rows it expects in the materialized view

The size of the materialized view in bytes

In the following example, the query specified in the materialized view is passed into the ESTIMATE_SUMMARY_SIZE procedure. Note that the SQL statement is passed in without a semicolon at the end.

The procedure returns two values: an estimate for the number of rows, and the size of the materialized view in bytes, as illustrated in the following.

No of Rows: 17284
Size of Materialized view (bytes): 2281488

Is a Materialized View Being Used?

One of the major administrative problems with materialized views is knowing whether they are being used. Some materialized views might be in regular use. Others could have been created for a one-time problem that has now been resolved. However, the users who requested this level of analysis might never have told you that it was no longer required, so the materialized views remain in the database occupying storage space and possibly being regularly refreshed.

If a workload is available, then it can advise you which materialized views are in use. The workload will report only on materialized views that were used while it was collecting statistics. Therefore, if too small a window is chosen, not all the materialized views that are in use will be reported. To obtain the information, the procedure EVALUATE_MVIEW_STRATEGY is called. It analyzes the data and then the results can be viewed through the SYSTEM_MVIEW_EVALUATIONS view.

DBMS_OLAP.EVALUATE_MVIEW_STRATEGY Procedure

Table 16-18 EVALUATE_MVIEW_STRATEGY Procedure Parameters

Parameter

Datatype

Description

run_id

NUMBER

The Advisor-assigned ID for the current session

workload_id

NUMBER

An optional workload ID that maps to a user-supplied workload

filter_id

NUMBER

The optional filter ID is used to identify a filter against the target workload

In the following example, the utilization of materialized views is analyzed and the results are displayed:

Summary Advisor Wizard

The Summary Advisor Wizard in Oracle Enterprise Manager provides an interactive environment to recommend and build materialized views. Using the Wizard, you will be asked where the materialized views are to be placed, which fact tables to use, and which of the existing materialized views are to be retained. If a workload exists, it may be automatically selected. Otherwise, the Wizard will display the recommendations that are generated from the RECOMMEND_MVIEW_STRATEGY procedure.

All of the steps required to maintain your materialized views can be completed by answering the Wizard's questions. No subsequent DML operations are required.

You cannot use it to review or delete the recommendations, display the reports, or purge the workloads or filters.

Figure 16-2 Summary Advisor Wizard: Workload Statistics

If no workload is available, then select Hypothetical. Otherwise, specify where the workload comes from:

The current contents of the SQL cache

A user defined workload table which is selected from the drop down list

An Oracle trace workload

Also, at this time, the workload can be filtered by selecting this option and clicking on the SpecifyFilter button. A new screen is displayed where the filters can be specified. There are four tabs: General, SQL, Advanced, and Trace where the filtering information is specified.

The Summary Advisor then attempts to determine which tables are the fact tables.

Step 2 displays these results and asks you to move the tables it has identified as fact tables and you want to be used as a fact table from the AvailableTables column to the SelectedTables column using the > button as shown in Figure 16-3. Alternatively, you can select which are your fact tables.

Figure 16-3 Summary Advisor: Select Fact Tables

If there are any materialized views that already exist, the Summary Advisor wizard shows how much space they are using and asks if they should be retained. Then, it actually generates its recommendations and the screen shown in Figure 16-4 is displayed.

Figure 16-4 Summary Advisor: Recommendations

The graph shown on the left of the screen shows the calculated gains for these recommendations. By sliding the marker along the line of the graph, depending on whether more performance is required or less storage space is used.

A set of materialized views will be recommended for that point on the graph. The actual recommendations are viewed by clicking on the View/ModifyRecommendations button.

Default schema, tablespace and refresh method can be supplied for all recommendations. Then by pressing the View/ModifyRecommendations button, each recommendation can be accepted or rejected and customized to your own requirements as to its name and other characteristics as shown in Figure 16-5.

Figure 16-5 Summary Advisor: Customize Recommendations

Finally, once you are satisfied with the recommendations, Figure 16-6 is displayed where you can see the actual script which will be used to implement the recommendations. At this time, this script can be saved to a file and run later, or, if the Finish button is clicked, the recommendations are implemented.