Summary Advisor

4.7 Summary Advisor

Now that we have discussed all the benefits that materialized views provide, we come to the question: How do you know what materialized views to create? Decision-support applications typically involve a well-defined set of queries. The simplest solution to improve performance of these queries would be to create one materialized view for each query. In practice, the disk space requirements and refresh overhead will soon get prohibitive. With query rewrite you can use the same materialized view to rewrite a large class of queries. However, determining the optimal set of materialized views to create for a bunch of queries can be tricky. The Summary Advisor tool is designed to help with the problem of choosing the materialized views for an application.

The Summary Advisor can be used to determine what materialized views to create for a given set of queries. The Oracle 9i Summary Advisor is tightly integrated with the query rewrite engine and is aware of the various rules for query rewrite. The Summary Advisor will optimize the materialized views to fit into a storage limit provided by the user.

In order to run the Oracle 9i Summary Advisor you must have Java enabled in the database, and statistics must have been collected on all the relevant tables and their columns. If you have constraints and dimensions defined in your schema to express your business rules, the Summary Advisor will make better recommendations, because it can take advantage of the more sophisticated rewrite rules discussed earlier.

There are two ways you can run the summary advisor: using the Summary Advisor Wizard in Oracle Enterprise Manager or using the DBMS_OLAP package from SQL*Plus.

4.7.1 Summary Advisor Wizard

Oracle Enterprise Manager provides a Summary Advisor Wizard, which guides you through the various steps required for analysis. You can access the Summary Advisor Wizard by navigating to the Warehouse node, followed by the Summary Management node, and then choosing Summary Advisor in the Object menu. Once the analysis is finished you can accept some or all of the recommendations and implement them. Let us look at a typical run of the Summary Advisor Wizard.

The first step is to tell the wizard what SQL statements your application uses. This is known as the workload. The workload can be extracted from various sources, such as Oracle's SQL cache, Oracle trace, or a user-defined table. The Summary Advisor will consider query rewrite rules and statement statistics, such as priority, execution time, and frequency, in coming up with the set of materialized views.

Figure 4.9 shows the Summary Advisor Wizard page for loading the workload.

Figure 4.9: Summary Advisor Wizard—loading the workload.

Since the workload may have several statements, the Summary Advisor provides a mechanism to select only relevant statements using a filter. The filter can be on various criteria, such as the executing user, tables referenced in the query, response time, and frequency. The filter can be applied either while collecting the workload or during the recommendation process. In Figure 4.10 we are setting filters only to consider SQL executed by the EASYDW use and on tables CUSTOMERS and PURCHASES. Next you have the option of choosing fact tables and marking existing materialized views to retain.

Figure 4.10: Summary Advisor Wizard—filters.

Once the analysis is completed, the recommendations are displayed graphically, as shown in Figure 4.11. The X-axis of the graph shows the space used, and the Y-axis shows the cumulative performance gained from the recommendations. Users can decide how much space they would actually like to use by moving the arrow.

Figure 4.11: Summary Advisor Wizard—recommendation graph.

The recommendations that fit the chosen space budget can be viewed in detail by clicking the View/Modify Recommendations button. Figure 4.12 shows the wizard page for the recommendations. The user can change the materialized view name and options for any newly recommended materialized views.

Figure 4.12: Summary Advisor Wizard—view/modify recommendations.

Once the required modifications are made, you can press the Next button in Figure 4.11 to see the SQL required to implement the recommendations, as shown in Figure 4.13. You can also save the generated SQL statements to a file. Pressing the Finish button will implement the recommendations. In order to implement the recommendations, you must have the requisite privileges.

Figure 4.13: Summary Advisor Wizard—implement recommendations.

Next, we will see how to run the summary advisor on the command line.

4.7.2 Using the DBMS_OLAP package

The Summary Advisor can be run without the graphical interface using the procedures in the DBMS_OLAP package. We will show a typical session using the Summary Advisor APIs.

Loading workload

The first step to run the Summary Advisor is to load the workload. The workload can be loaded from the SQL cache, a user-defined table, or generated using the dimension and constraint information in the database. In the following example, we load the workload from the SQL cache using the LOAD_WORKLOAD_CACHE procedure. Before issuing the API to load the workload, you must create an identifier for the workload using the CREATE_ID procedure. This will later be provided to the RECOMMEND_MVIEW_STRATEGY procedure.

 Variable workload_id number; EXECUTE DBMS_OLAP.CREATE_ID(:workload_id); EXECUTE DBMS_OLAP.LOAD_WORKLOAD_CACHE (:workload_id,                                        DBMS_OLAP.WORKLOAD_NEW,                                        DBMS_OLAP.FILTER_NONE,                                        'Easydw', 1); 

User-defined workload

Sometimes you may have a specific set of SQL statements you wish to tune. The SQL cache may not accurately represent these statements. In this case, you can place your statements in a table and use the LOAD_WORKLOAD_USER procedure to provide this workload to the Summary Advisor. The user workload table must at the minimum specify the query text and the user executing the query. To get better recommendations, you should provide some statistics, such as frequency and response time, if available.

The following example shows the format of a user workload table and how it is loaded.

 CREATE TABLE my_workload (query           varchar2(2000) not null,  owner           varchar2(30)   not null,  application     varchar2(30),  frequency       number,  lastuse         number,  priority        number,  responsetime    number,  resultsize      number,  sql_addr        number,  sql_hash        number ); <insert workload statements into my_workload> 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,                                       'EASYDW', 'MY_WORKLOAD'); 

Once the workload is loaded, you follow the remaining steps, discussed in the following text, to obtain recommendations.

Filters

As discussed earlier, filters can be used to reduce the number of queries to be analyzed. Filters can be based on tables or schemas referenced in the queries, frequency, response time, cardinality of tables, or a user-defined priority field (for user-defined workload). In the following example, we will create a filter that only picks queries that have EASYDW tables in them. This narrows the focus of the Advisor, thereby reducing its analysis time. Further, we will only choose queries that occur at least ten times. As with the workload, you must create an identifier for the filter using the CREATE_ID procedure. We will use this identifier to provide the filter to the recommendation procedure.

 Variable filter_id number; EXECUTE DBMS_OLAP.CREATE_ID(:filter_id); EXECUTE DBMS_OLAP.ADD_FILTER_ITEM(:filter_id, 'SCHEMA',  'EASYDW',                                    NULL, NULL, NULL, NULL); EXECUTE  DBMS_OLAP.ADD_FILTER_ITEM (:filter_id,  'FREQUENCY',                                    NULL, 10, NULL, NULL, NULL); 

Recommending materialized views

Now we are ready to start the recommendation process. To generate recommendations, we use the DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY procedure. Before running this procedure, you must generate a run_id using the CREATE_ID procedure. This is used to identify the results of the Advisor's analysis in the SYSTEM.MVIEW_RECOMMENDATIONS catalog view.

The RECOMMEND_MVIEW_STRATEGY procedure must be provided with the workload and filters using the identifiers—workload_id and filter_id, that we had generated earlier. Optionally, you can also provide a storage budget available for materialized views (500 MB in the following example) and a list of materialized views that should not be dropped.

 Variable run_id number; EXECUTE DBMS_OLAP.CREATE_ID(:run_id); EXECUTE DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY(:run_id, :workload_id,                                            :filter_id, 500000, 100,                                            NULL, NULL); 

Summary advisor reports and scripts

Once the recommendations have been generated, you can generate a report or script using the procedures DBMS_OLAP.GENERATE_MVIEW_REPORT and GENERATE_MVIEW_SCRIPT, respectively. The script provides the SQL statements used to generate the recommended materialized views.

In order to generate script and report files, the user issuing these procedures must have been granted read and write permissions on the specific directories. To grant user EASYDW read and write access to the h:\oracle\reports directory you would issue the following statement:

 EXECUTE DBMS_JAVA.GRANT_PERMISSION('EASYDW',                                    'java.io.FilePermission',                                    'h:\oracle\reports\*',                                    'read, write'); 

The following code segment is a script generated by the Summary Advisor for the previous example. We have specified that the script should use the SUMMARY tablespace for the generated materialized view statements. The script is placed in the directory that was granted permissions previously.

 EXECUTE DBMS_OLAP.GENERATE_MVIEW_SCRIPT('h:\oracle\reports\mview_rec.sql',                                 :run_id, 'SUMMARY'); /***************************************************************************** **  Oracle Summary Advisor 9i - Production ** **  Summary Advisor Recommendation Script *****************************************************************************/ /***************************************************************************** **  Recommendations for run ID #59 *****************************************************************************/ /***************************************************************************** **  Rank 1 **  Storage 16,240 bytes **  Gain 78.72% **  Benefit Ratio 201,344.00 ** **  SELECT easydw.customer.customer_id, easydw.time.month, easydw.time.year, **         sum(easydw.purchases.purchase_price), count( **         easydw.purchases.purchase_price), count(*) **      FROM easydw.purchases, easydw.time, easydw.customer **      WHERE easydw.time.time_key = easydw.purchases.time_key **        AND easydw.customer.customer_id = easydw.purchases.customer_id **      GROUP BY easydw.customer.customer_id, easydw.time.month, **               easydw.time.year *****************************************************************************/ CREATE MATERIALIZED VIEW mv_id_59_rank_1   TABLESPACE summary   BUILD IMMEDIATE   REFRESH COMPLETE   ENABLE QUERY REWRITE AS     SELECT EASYDW.CUSTOMER.CUSTOMER_ID, EASYDW.TIME.MONTH, EASYDW.TIME.YEAR, SUM("EASYDW"."PURCHASES"."PURCHASE_PRICE"), COUNT("EASYDW"."PURCHASES"."PURCHASE_PRICE"), COUNT(*) FROM EASYDW.PURCHASES, EASYDW.TIME, EASYDW.CUSTOMER WHERE EASYDW.TIME.TIME_KEY = EASYDW.PURCHASES.TIME_KEY AND EASYDW.CUSTOMER.CUSTOMER_ID = EASYDW.PURCHASES.CUSTOMER_ID GROUP BY EASYDW.CUSTOMER.CUSTOMER_ID, EASYDW.TIME.MONTH, EASYDW.TIME.YEAR; <remainder of script skipped> 

You can also get a detailed HTML report of the Summary Advisor analysis by issuing the DBMS_OLAP.GENERATE_MVIEW_REPORT procedure, as shown in the following example. You can customize the report to include different detailed sections of the report, such as workload details, filters, recommendations, and existing materialized view use. If the run_id is not passed in, a report is generated for all data for the user issuing the procedure. Figure 4.14 shows a section of a Summary Advisor report displayed in a Web browser.

Figure 4.14: Summary Advisor report.

 EXECUTE DBMS_OLAP.GENERATE_MVIEW_REPORT         ('h:\oracle\reports\report_rec.html',           :run_id, DBMS_OLAP.RPT_ALL); 

Hypothetical workload

If you are still in the process of developing the application, you may not have the set of SQL statements that may get executed. The Summary Advisor can still be used if you have the schema for the queries and some additional information such as constraints or dimensions defined. In this case, the Summary Advisor will determine a hypothetical workload using the relationships between various columns and then recommend materialized views for it. This works well for star schemas, which are typical in a data warehouse. To generate recommendations using a hypothetical workload, pass in a workload_id of DBMS_OLAP.WORKLOAD_NONE in the DBMS_OLAP.RECOMMEND_MV procedure.

 EXECUTE DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY(:run_id,                                            DBMS_OLAP.WORKLOAD_NONE,                                            DBMS_OLAP.FILTER_NONE,                                            500000, 100, NULL, NULL); 

You can view the recommendations and generate reports and scripts as described previously.

Purging results

You can clean up the results of the analysis by calling the DBMS_OLAP.PURGE_RESULTS procedure.

 EXECUTE DBMS_OLAP.PURGE_RESULTS(:run_id); 

You can also use DBMS_OLAP.RUNID_ALL to purge all Advisor results.

 EXECUTE DBMS_OLAP.PURGE_RESULTS(DBMS_OLAP.RUNID_ALL); 

You can remove a workload that is no longer required by using the PURGE_WORKLOAD procedure by passing in the specific workload_id or DBMS_OLAP.WORKLOAD_ALL to remove all workloads. Similarly, you can remove a filter using the PURGE_FILTER procedure.

Monitoring existing materialized view use

You may sometimes want to know which summaries are being used. A major problem for a DBA is to determine when it is safe to remove a summary from the data warehouse. The DBMS_OLAP package provides the EVALUATE_MVIEW_STRATEGY procedure to evaluate the utilization of existing materialized views. This helps determine if some materialized views are not being used by your current workload. The results of the evaluation may be viewed in the SYSTEM.MVIEW_EVALUATIONS view.

 EXECUTE DBMS_OLAP.EVALUATE_MVIEW_STRATEGY(:run_id, :workload_id,                                           :filter_id); SELECT runid, mview_owner, mview_name, storage, freq,        cumulative_benefit as benefit FROM SYSTEM.MVIEW_EVALUATIONS; RUNID MVIEW_OWNER  MVIEW_NAME          STORAGE  FREQ    BENEFIT ----- -----------  ------------------- -------  ----   --------- 141   EASYDW       SALES_MV              1539    12     41351428 141   EASYDW       PRODUCT_SALES_BY_MON 35303     5      4295396 

Estimating the size of a materialized view

If you were going to create a summary, it would be nice to know how much space it is likely to occupy. You can find out this information by calling the procedure DBMS_OLAP.ESTIMATE_SUMMARY_SIZE, as shown in the following code segment. This procedure is very easy to use: Simply pass in the SELECT expression used to define the materialized view.

 SET SERVEROUTPUT on DECLARE no_of_rows NUMBER; mv_size NUMBER; BEGIN no_of_rows :=0; mv_size :=0; DBMS_OLAP.estimate_summary_size ('purchases_by_county',  'SELECT SUM(ps.purchase_price), ct.county   FROM PURCHASES ps, CUSTOMER ct   WHERE ps.customer_id = ct.customer_id   GROUP BY ct.county' , no_of_rows, mv_size); DBMS_OUTPUT.put_line ( 'SELECT on Purchases GROUP by County '); DBMS_OUTPUT.put_line ( 'No of Rows: ' || no_of_rows ); DBMS_OUTPUT.put_line ( 'Size of Summary (bytes): ' || mv_size); DBMS_OUTPUT.put_line ( ''); END; / SELECT on Purchases GROUP by County No of Rows: 3 Size of Materialized view (bytes): 96 

In this example, we report the number of rows and the expected materialized view size. Also note that in this example, we have used DBMS_OUTPUT.PUT_LINE to display the results from the package.