Release 2 (9.2)
Part Number A96520-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Data Warehousing Guide Release 2 (9.2) Part Number A96520-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 SYSTEM.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 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 illustrated in the following section.
| 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:
VARIABLE MY_ID NUMBER;
CREATE_ID function to generate a new identifier.
EXECUTE 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:
The following is an example of loading a user workload.
VARIABLE MY_ID NUMBER;
CREATE_ID function to generate a new identifier.
EXECUTE DBMS_OLAP.CREATE_ID(:MY_ID);
MY_WORKLOAD tables the queries you want advice on.
INSERT INTO advisor_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)
EXECUTE 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. 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 filesORACLE_TRACE_COLLECTION_SIZE = 0ORACLE_TRACE_ENABLE = TRUEORACLE_TRACE_FACILITY_NAME = oraclesm or oralceeORACLE_TRACE_FACILITY_PATH = location of trace facility files
| See Also:
Oracle9i Database Performance Tuning Guide and Reference for further information regarding these parameters |
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 illustrated as follows.
VARIABLE MY_ID NUMBER:
CREATE_ID function to generate a new identifier.
EXECUTE DBMS_OLAP.CREATE_ID(:MY_ID);
EXECUTE 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. 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.
An example of how to load a SQL Cache workload is as follows.
VARIABLE MY_ID NUMBER:
CREATE_ID function to generate a new identifier.
EXECUTE DBMS_OLAP.CREATE_ID(:MY_ID);
EXECUTE DBMS_OLAP.LOAD_WORKLOAD_CACHE(:MY_ID, DBMS_OLAP.WORKLOAD_NEW, DBMS_ OLAP.FILTER_NONE, 'Payroll', 7);
Prior to loading a workload, you can call one of the three VALIDATE_WORKLOAD procedures to check that the workload exists:
These procedures do not check that the contents of the workload are valid, they merely check 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);
The following 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 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.
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
VARIABLE MY_ID NUMBER:
CREATE_ID function to generate a new identifier.
EXECUTE DBMS_OLAP.CREATE_ID(:MY_ID);
EXECUTE DBMS_OLAP.ADD_FILTER_ITEM(:MY_ID,'BASETABLE', 'SCOTT.EMP', NULL, NULL, NULL, NULL); EXECUTE DBMS_OLAP.ADD_FILTER_ITEM(:MY_ID, 'OWNER', 'SCOTT,PAYROLL,PERSONNEL', NULL, NULL, NULL, NULL); EXECUTE DBMS_OLAP.ADD_FILTER_ITEM(:MY_ID, 'FREQUENCY', NULL, 500, NULL, NULL, NULL);
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:
VARIABLE MY_ID NUMBER: EXECUTE DBMS_OLAP.CREATE_ID(:MY_ID); EXECUTE DBMS_OLAP.ADD_FILTER_ITEM(:MY_ID,'BASETABLE', 'SCOTT.EMP', NULL, NULL, NULL, NULL); EXECUTE DBMS_OLAP.CREATE_ID(:MY_ID); EXECUTE DBMS_OLAP.ADD_FILTER_ITEM(:MY_ID, 'OWNER', 'SCOTT, PAYROLL,PERSONNEL', NULL, NULL, NULL, ULL); EXECUTE DBMS_OLAP.CREATE_ID(:MY_ID); EXECUTE 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 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.
| Parameter | Datatype | Description |
|---|---|---|
filterid |
NUMBER |
A filter ID number used to identify the filter to be deleted |
VARIABLE MY_FILTER_ID NUMBER: EXECUTE DBMS_OLAP.PURGE_FILTER(:MY_FILTER_ID); EXECUTE 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.
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 |
The parameters for RECOMMEND_MVIEW_STRATEGY and their descriptions are given in Table 16-11.
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.
The following are several examples of how you can use the Summary 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)
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 the following:
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)
| Parameter | Description |
|---|---|
|
|
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. |
|
|
Report flags to indicate required detail sections. Multiple sections can be selected by referencing the following constants.
|
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:
EXECUTE DBMS_JAVA.GRANT_PERMISSION('Oracle-user-goes-here', 'java.io.FilePermission', 'directory-spec-goes-here/*', 'read, write');
The following is an example of how to call this report:
EXECUTE 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:
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.
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 |
|---|---|---|
|
|
|
An ID used to identify the results to delete |
EXECUTE 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_sold) 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_sold) 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_sold) 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
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:
SELECT runid#, text FROM system.mview$_adv_journal
The text column will contain information regarding missing statistics.
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.
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.
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_SUMMARY_SIZE returns the following:
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.
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 illustrated in the following.
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 following example, the utilization of materialized views is analyzed and the results are displayed:
DBMS_OLAP.EVALUATE_MVIEW_STRATEGY(:run_id, NULL, DBMS_OLAP.FILTER_NONE);
The following 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
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.
| See Also:
Oracle Enterprise Manager Configuration Guide for further information regarding the Summary Advisor |
The Summary Advisor requires only the completion of a few steps to generate the recommendations. In Figure 16-2, you see the first step where you have to define the type of workload being used.
Text description of the illustration sawkld.gif
If no workload is available, then select Hypothetical. Otherwise, specify where the workload comes from:
Also, at this time, the workload can be filtered by selecting this option and clicking on the Specify Filter 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 Available Tables column to the Selected Tables column using the > button as shown in Figure 16-3. Alternatively, you can select which are your fact tables.
Text description of the illustration safact.gif
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.
Text description of the illustration sarec.gif
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/Modify Recommendations button.
Default schema, tablespace and refresh method can be supplied for all recommendations. Then by pressing the View/Modify Recommendations 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.
Text description of the illustration sacust.gif
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.
Text description of the illustration safinal.gif
Figure 16-7 shows the progress of the process implementing the recommendations.
Text description of the illustration saimpl.gif
When finished, the materialized views can now be displayed in Oracle Enterprise Manager as illustrated in Figure 16-8.
Text description of the illustration oemmv.gif
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|