Release 1 (9.0.1)
Part Number A90237-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Data Warehousing Guide Release 1 (9.0.1) Part Number A90237-01 |
|
This chapter illustrates how to use the Summary Advisor, a tool for choosing and understanding materialized views. The chapter contains:
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.
Using the Summary Advisor or the DBMS_OLAP package, you can:
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:
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:
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.
Result tables are internal tables that store both intermediate and final results from all Summary Advisor components.
Read-only views allow you to access recommendations, filters and workloads.These views are MVIEW_RECOMMENDATIONS, MVIEW_EVALUATIONS, MVIEW_FILTER, and MVIEW_WORKLOAD.
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:
SELECT MVIEW_OWNER, MVIEW_NAME, RECOMMENDED_ACTION, PCT_PERFORMANCE_GAIN, BENEFIT_TO_COST_RATIO FROM SYSTEM.MVIEW_RECOMMENDATIONS WHERE RUNID= (SELECT MAX(RUNID) FROM MVIEW_RECOMMENDATIONS) ORDER BY RECOMMENDATION_NUMBER ASC
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, JOIN KEY 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 ANALYZE TABLE 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:
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.
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.
|
See Also:
Oracle Enterprise Manager Configuration Guide for further information regarding the Summary Advisor |
The following sections will help you use the Advisor:
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 shown below.
| 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:
VARIABLE MY_ID NUMBER;
CREATE_ID function to generate a new identifier:
CALL DBMS_OLAP.CREATE_ID(:MY_ID);
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:
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.
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.
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.
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.
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 below:
The following is an example of loading a user workload:
VARIABLE MY_ID NUMBER;
CREATE_ID function to generate a new identifier:
CALL DBMS_OLAP.CREATE_ID(:MY_ID);
CALL DBMS_OLAP.LOAD_WORKLOAD_USER(:MY_ID, DBMS_OLAP.WORKLOAD_NEW, DBMS_OLAP.FILTER_NONE, 'SH', 'MY_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 described below. 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.
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:
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.
ORACLE_TRACE_COLLECTION_PATH = <location of collection files>
ORACLE_TRACE_COLLECTION_SIZE = 0
ORACLE_TRACE_ENABLE = TRUE
ORACLE_TRACE_FACILITY_NAME = oraclesm or oralcee
ORACLE_TRACE_FACILITY_PATH = <location of trace facility files>
Oracle Enterprise Manager Oracle Trace User's Guide for further information regarding these parameters
See Also:
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.
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.
GATHER_TABLE_STATS procedure of the DBMS_STATS package or ANALYZE...ESTIMATE STATISTICS 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 CREATE DIMENSION statement).
CREATE_ID procedure of the DBMS_OLAP package to get a unique workload_id for this workload.
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 shown below.
VARIABLE MY_ID NUMBER:
CREATE_ID function to generate a new identifier:
CALL DBMS_OLAP.CREATE_ID(:MY_ID);
CALL DBMS_OLAP.LOAD_WORKLOAD_TRACE(:MY_ID, DBMS_OLAP.WORKLOAD_NEW, DBMS_ OLAP.FILTER_NONE, 'myapp', 7, 'SH');
You obtain a SQL cache workload using the procedure LOAD_WORKLOAD_CACHE described below. 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 the 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.
An example of how to load a SQL Cache workload is shown below.
VARIABLE MY_ID NUMBER:
CREATE_ID function to generate a new identifier:
CALL DBMS_OLAP.CREATE_ID(:MY_ID);
CALL DBMS_OLAP.LOAD_WORKLOAD_CACHE(:MY_ID, DBMS_OLAP.WORKLOAD_NEW, DBMS_ OLAP.FILTER_NONE, 'Payroll', 7);
Prior to loading a workload, one of the three VALIDATE_WORKLOAD procedures:
may be called to check that the workload exists. This procedure does not check that the contents of the workload are valid, it merely checks that the workload exists.
The following are examples of validating the three types of workload:
DECLARE isitgood NUMBER; err_text VARCHAR2(200); BEGIN DBMS_OLAP.VALIDATE_WORKLOAD_CACHE (isitgood, err_text); END; DECLARE isitgood NUMBER; err_text VARCHAR2(200); BEGIN DBMS_OLAP.VALIDATE_WORKLOAD_TRACE ('SH', isitgood, err_text); END; DECLARE isitgood NUMBER; err_text VARCHAR2(200); BEGIN DBMS_OLAP.VALIDATE_WORKLOAD_USER ('SH', 'USER_WORKLOAD', isitgood, err_text); END;
When workloads are no longer needed, they can be removed using the procedure PURGE_WORKLOAD. You can delete all workloads or a specific collection.
The following is an example of removing a specific workload
VARIABLE workload_id NUMBER; DBMS_OLAP.PURGE_WORKLOAD(:workload_id);
This example removes all workloads.
EXECUTE DBMS_OLAP.PURGE_WORKLOAD(DBMS_OLAP.WORKLOAD_ALL);
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.
The Advisor supports nine 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-separated 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 NULL value indicates infinity.
You can define a number of different types of filter as shown in Table 16-9:
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.
Below is an example illustrating how to add three different types of filter
VARIABLE MY_ID NUMBER:
CREATE_ID function to generate a new identifier:
CALL DBMS_OLAP.CREATE_ID(:MY_ID);
CALL DBMS_OLAP.ADD_FILTER_ITEM(:MY_ID,'BASETABLE', 'SCOTT.EMP', NULL, NULL, NULL, NULL); CALL DBMS_OLAP.ADD_FILTER_ITEM(:MY_ID, 'OWNER', 'SCOTT,PAYROLL,PERSONNEL', NULL, NULL, NULL, NULL); CALL DBMS_OLAP.ADD_FILTER_ITEM(:MY_ID, 'FREQUENCY', NULL, 500, NULL, NULL, NULL);
The above 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 shown below:
VARIABLE MY_ID NUMBER: CALL DBMS_OLAP.CREATE_ID(:MY_ID); CALL DBMS_OLAP.ADD_FILTER_ITEM(:MY_ID,'BASETABLE', 'SCOTT.EMP', NULL, NULL, NULL, NULL); CALL DBMS_OLAP.CREATE_ID(:MY_ID); CALL DBMS_OLAP.ADD_FILTER_ITEM(:MY_ID, 'OWNER', 'SCOTT, PAYROLL,PERSONNEL', NULL, NULL, NULL, ULL); CALL DBMS_OLAP.CREATE_ID(:MY_ID); CALL DBMS_OLAP.ADD_FILTER_ITEM(:MY_ID, 'FREQUENCY', NULL, 500,NULL, NULL,NULL);
A filter can be removed at anytime by calling the procedure PURGE_FILTER which is described below. 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.
| Parameter | Datatype | Description |
|---|---|---|
filterid |
NUMBER |
A filter ID number used to identify the filter to be deleted |
VARIABLE MY_FILTER_ID NUMBER: CALL DBMS_OLAP.PURGE_FILTER(:MY_FILTER_ID); CALL DBMS_OLAP.PURGE_FILTER(DBMS_OLAP.FILTER_ALL);
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.
In order to use the Summary advisor, you must have the SELECT ANY TABLE privilege.
|
See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for detailed information about the |
This procedure has the following parameters:
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.
Below are several examples of how you can use the Advisor recommendation process:
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.
DECLARE workload_id NUMBER; run_id NUMBER; BEGIN -- load the workload DBMS_OLAP.CREATE_ID (workload_id); DBMS_OLAP.LOAD_WORKLOAD_USER(workload_id, DBMS_OLAP.WORKLOAD_NEW, DBMS_OLAP.FILTER_NONE,'SH','USER_WORKLOAD' ); -- run recommend_mv DBMS_OLAP.CREATE_ID (run_id); DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY(run_id, workload_id, NULL, 1000000, 100, NULL, 'sales'); END;
In this example, the workload is derived from the current contents of the SQL cache and then filtered for only the application called sales_hist:
DECLARE workload_id NUMBER; filter_id NUMBER; run_id NUMBER; BEGIN -- add a filter for application sales_hist DBMS_OLAP.CREATE_ID(filter_id); DBMS_OLAP.ADD_FILTER_ITEM(filter_id, 'APPLICATION', 'sales_hist', NULL, NULL, NULL, NULL); -- load the workload DBMS_OLAP.CREATE_ID(workload_id); DBMS_OLAP.LOAD_WORKLOAD_CACHE (workload_id, DBMS_OLAP.WORKLOAD_NEW, DBMS_ OLAP.FILTER_NONE, NULL ,NULL); -- run recommend_mv DBMS_OLAP.CREATE_ID (run_id ); DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY(run_id, workload_id, NULL, 1000000, 100, NULL, 'sales'); END;
In this example, the workload is from Oracle Trace without filtering.
DECLARE workload_id NUMBER; run_id NUMBER; BEGIN DBMS_OLAP.CREATE_ID (workload_id); DBMS_OLAP.LOAD_WORKLOAD_TRACE (workload_id, DBMS_OLAP.WORKLOAD_NEW, DBMS_ OLAP.FILTER_NONE, NULL,NULL,NULL ); -- run recommend_mv DBMS_OLAP.CREATE_ID(run_id); DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY(run_id, workload_id, NULL,10000000, 100, NULL, 'sales'); END;
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:
GENERATE_MVIEW_SCRIPT (filename VARCHAR2, id NUMBER, tablespace_name VARCHAR2)
filename
Contains the fully-specified output file name
id
Contains the Advisor run ID for which the script will be created
tablespace_name
Contains an optional tablespace in which new materialized views will be placed.
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.
/***************************************************************************** ** Oracle Summary Advisor 9i - Production ** ** Summary Advisor Recommendation Script *****************************************************************************/ /***************************************************************************** ** Recommendations for run ID #9999 *****************************************************************************/ /***************************************************************************** ** Rank 1 ** Storage 0 bytes ** Gain 0.00% ** Benefit Ratio 0.00 ** SELECT COUNT(*), AVG(dollar_cost) ** FROM sales ** GROUP BY store_key *****************************************************************************/ CREATE MATERIALIZED VIEW mv_id_9999_rank_1 TABLESPACE user BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT COUNT(*),AVG(dollar_cost) FROM sales GROUP BY store_key; /***************************************************************************** ** Rank 2 ** Storage 6,000 bytes ** Gain 13.00% ** Benefit Ratio 874.00 *****************************************************************************/ DROP MATERIALIZED VIEW sh.mview_fact_01; /***************************************************************************** ** Rank 3 ** Storage 6,000 bytes ** Gain 76.00% ** Benefit Ratio 8,744.00 ** ** SELECT COUNT(*), MAX(dollar_cost), MIN(dollar_cost) ** FROM sh.sales ** WHERE store_key IN (10, 23) ** AND unit_sales > 5000 ** GROUP BY store_key, promotion_key *****************************************************************************/ CREATE MATERIALIZED VIEW mv_id_9999_rank_3 TABLESPACE user BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT COUNT(*), MAX(dollar_cost), MIN(dollar_cost) FROM sh.sales WHERE store_key IN (10,23) AND unit_sales > 5000 GROUP BY store_key, promotion_key;
A Summary Data Report offers you data about workloads and filters, and then generates recommendations. The report format is HTML and the contents are
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
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
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
This section describes the Advisor's results from an evaluation of existing materialized views
The workload report lists the details of each SQL query for the current user's workload collections. The report is arranged by table references
The workload filter report lists details of workload filters for the current user
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
PROCEDURE GENERATE_MVIEW_REPORT (file_name IN VARCHAR2, id IN NUMBER, flags IN NUMBER)
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.
The Advisor ID number used to collect or analyze data. NULL indicates all data for the requested section.
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 the the Oracle9i Java Developer's Guide and is as follows:
CALL DBMS_JAVA.GRANT_PERMISSION('Oracle-user-goes-here', 'java.io.FilePermission', 'directory-spec-goes-here/*', 'read, write');
Below is a example of how to call this report
CALL DBMS_OLAP.GENERATE_MVIEW_REPORT( '/usr/mydev/myname/report.html', 0, DBMS_OLAP.RPT_ALL);
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:
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
xxxx is the filename portion of the user-supplied file specification.
All files appear in the same directory, which is the one you specify.
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.
| Parameter | Datatype | Description |
|---|---|---|
run_id |
NUMBER |
An ID used to identify the results to delete |
CALL DBMS_OLAP.PURGE_RESULTS (DBMS_OLAP.RUNID_ALL);
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.
Here are some complete examples of how to use the Summary Advisor.
REM=============================================================== REM Setup for demos REM=============================================================== CONNECT system/manager GRANT SELECT ON mview_recommendations to sh; GRANT SELECT ON mview_workload to sh; GRANT SELECT ON mview_filter to sh; DISCONNECT REM*************************************************************** REM * Demo 1: Materialized View Recommendation With User Workload* REM*************************************************************** REM=============================================================== REM Step 1. Define user workload table and add artificial workload queries. REM=============================================================== CONNECT sh/sh CREATE TABLE user_workload( query VARCHAR2(40), owner VARCHAR2(40), application VARCHAR2(30), frequency NUMBER, lastuse DATE, priority NUMBER, responsetime NUMBER, resultsize NUMBER ) / INSERT INTO user_workload values ( 'SELECT SUM(s.quantity_sold) FROM sales s, products p WHERE s.prod_id = p.prod_id and p.prod_category = ''Boys'' GROUP BY p.prod_category', 'SH', 'app1', 10, NULL, 5, NULL, NULL ) / INSERT INTO user_workload values ( 'SELECT SUM(s.amount) FROM sales s, products p WHERE s.prod_id = p.prod_id AND p.prod_category = ''Girls'' GROUP BY p.prod_category', 'SH', 'app1', 10, NULL, 6, NULL, NULL ) / INSERT INTO user_workload values ( 'SELECT SUM(quantity_sold) FROM sales s, products p WHERE s.prod_id = p.prod_id and p.prod_category = ''Men'' GROUP BY p.prod_category ', 'SH', 'app1', 11, NULL, 3, NULL, NULL ) / INSERT INTO user_workload VALUES ( 'SELECT SUM(quantity_sold) FROM sales s, products p WHERE s.prod_id = p.prod_id and p.prod_category in (''Women'', ''Men'') GROUP BY p.prod_category ', 'SH', 'app1', 1, NULL, 8, NULL, NULL ) / REM=================================================================== REM Step 2. Create a new identifier to identify a new collection in the REM internal repository and load the user-defined workload into the REM workload collection without filtering the workload. REM ======================================================================= VARIABLE WORKLOAD_ID NUMBER; EXECUTE DBMS_OLAP.CREATE_ID(:workload_id); EXECUTE DBMS_OLAP.LOAD_WORKLOAD_USER(:workload_id,\ DBMS_OLAP.WORKLOAD_NEW,\ DBMS_OLAP.FILTER_NONE, 'SH', 'USER_WORKLOAD'); SELECT COUNT(*) FROM SYSTEM.MVIEW_WORKLOAD WHERE workloadid = :workload_id; REM==================================================================== REM Step 3. Create a new identifier to identify a new filter object. Add REM two filter items such that the filter can filter out workload REM queries with priority >= 5 and frequency <= 10. REM===================================================================== VARIABLE filter_id NUMBER; EXECUTE DBMS_OLAP.CREATE_ID(:filter_id); EXECUTE DBMS_OLAP.ADD_FILTER_ITEM(:filter_id, 'PRIORITY', NULL, 5, NULL, NULL, NULL); EXECUTE DBMS_OLAP.ADD_FILTER_ITEM(:filter_id, 'FREQUENCY', NULL, NULL, 10, NULL, NULL); SELECT COUNT(*) FROM SYSTEM.MVIEW_FILTER WHERE filterid = :filter_id; REM===================================================================== REM Step 4. Recommend materialized views with part of the previous workload REM collection that satisfy the filter conditions. Create a new REM identifier to identify the recommendation output. REM=================================================================== VARIABLE RUN_ID NUMBER; EXECUTE DBMS_OLAP.CREATE_ID(:run_id); EXECUTE DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY(:run_id, :workload_id, :filter_id, 100000, 100, NULL, NULL); SELECT COUNT(*) FROM SYSTEM.MVIEW_RECOMMENDATIONS; REM=================================================================== REM Step 5. Generate HTML reports on the output. REM=================================================================== EXECUTE DBMS_OLAP.GENERATE_MVIEW_REPORT('/tmp/output1.html', :run_id, DBMS_ OLAP.RPT_RECOMMENDATION); REM==================================================================== REM Step 6. Cleanup current output, filter and workload collection REM FROM the internal repository, truncate the user workload table REM for new user workloads. REM==================================================================== EXECUTE DBMS_OLAP.PURGE_RESULTS(:run_id); EXECUTE DBMS_OLAP.PURGE_FILTER(:filter_id); EXECUTE DBMS_OLAP.PURGE_WORKLOAD(:workload_id); SELECT COUNT(*) FROM SYSTEM.MVIEW_WORKLOAD WHERE workloadid = :WORKLOAD_ID; TRUNCATE TABLE user_workload; DROP TABLE user_workload; DISCONNECT REM******************************************************************* REM * Demo 2: Materialized View Recommendation With SQL Cache. * REM******************************************************************* CONNECT sh/sh REM=================================================================== REM Step 1. Run some applications or some SQL queries, so that the REM Oracle SQL Cache is populated with target queries. REM=================================================================== REM Clear Pool of SQL queries ALTER SYSTEM FLUSH SHARED_POOL; SELECT SUM(s.quantity_sold) FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_category; SELECT SUM(s.amount) FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_category; SELECT t.calendar_month_desc, SUM(s.amount) AS dollars FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_desc; SELECT t.calendar_month_desc, SUM(s.amount) AS dollars FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_desc; REM==================================================================== REM Step 2. Create a new identifier to identify a new collection in the REM internal repository and grab a snapshot of the Oracle SQL cache REM into the new collection. REM==================================================================== EXECUTE DBMS_OLAP.CREATE_ID(:WORKLOAD_ID); EXECUTE DBMS_OLAP.LOAD_WORKLOAD_CACHE(:WORKLOAD_ID, DBMS_OLAP.WORKLOAD_NEW, DBMS_OLAP.FILTER_NONE, NULL, 1); SELECT COUNT(*) FROM SYSTEM.MVIEW_WORKLOAD WHERE workloadid = :WORKLOAD_ID; REM==================================================================== REM Step 3. Recommend materialized views with all of the workload workload REM and no filtering. REM===================================================================== EXECUTE DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY(:run_id, :workload_id, DBMS_ OLAP.FILTER_NONE, 10000000, 100, NULL, NULL); SELECT COUNT(*) FROM SYSTEM.MVIEW_RECOMMENDATIONS; REM=================================================================== REM Step 4. Generate HTML reports on the output. REM==================================================================== EXECUTE DBMS_OLAP.GENERATE_MVIEW_REPORT('/tmp/output2.html', :run_id, DBMS_OLAP.RPT_RECOMMENDATION); REM==================================================================== REM Step 5. Evaluate materialized views. REM==================================================================== EXECUTE DBMS_OLAP.CREATE_ID(:run_id); EXECUTE DBMS_OLAP.EVALUATE_MVIEW_STRATEGY(:run_id, workload_id, DBMS_ OLAP.FILTER_NONE); REM================================================================== REM Step 5. Cleanup current output, and workload collection REM FROM the internal repository. REM=================================================================== EXECUTE DBMS_OLAP.PURGE_RESULTS(:run_id); EXECUTE DBMS_OLAP.PURGE_WORKLOAD(:workload_id); DISCONNECT REM=================================================================== REM Cleanup for demos. REM=================================================================== CONNECT system/manager REVOKE SELECT ON MVIEW_RECOMMENDATIONS FROM sh; REVOKE SELECT ON MVIEW_WORKLOAD FROM sh; REVOKE SELECT ON MVIEW_FILTER FROM sh; DISCONNECT
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-15 lists the parameters to this procedure.
ESTIMATE_SUMMARY_SIZE returns:
In the example shown below, 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.
DBMS_OLAP.ESTIMATE_SUMMARY_SIZE ('simple_store', 'SELECT product_key1, product_key2, SUM(dollar_sales) AS sum_dollar_sales, SUM(unit_sales) AS sum_unit_sales, SUM(dollar_cost) AS sum_dollar_cost, SUM(customer_count) AS no_of_customers FROM fact GROUP BY product_key1, product_key2', no_of_rows, mv_size );
The procedure returns two values: an estimate for the number of rows, and the size of the materialized view in bytes, as shown below.
No of Rows: 17284 Size of Materialized view (bytes): 2281488
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.
In the example below, the utilization of materialized views is analyzed and the results are displayed.
DBMS_OLAP.EVALUATE_MVIEW_STRATEGY(:run_id, NULL, DBMS_OLAP.FILTER_NONE);
Shown below is a sample output obtained by querying the view SYSTEM.MVIEW_EVALUATIONS, which provides the following information:
MVIEW_OWNER MVIEW_NAME RANK SIZE FREQ CUMULATIVE BENEFIT ----------- ------------------- ----- ------ ---- ---------- ---------- GROCERY STORE_MIN_SUM 1 340 1 9001 26.4735294 GROCERY STORE_MAX_SUM 2 380 1 9001 23.6868421 GROCERY STORE_STDCNT_SUM 3 3120 1 3000.38333 .961661325 GROCERY QTR_STORE_PROMO_SUM 4 196020 2 0 0 GROCERY STORE_SALES_SUM 5 340 1 0 0 GROCERY STORE_SUM 6 21 10 0 0
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|