0&&parent.frames.length) { d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);} if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<(a.length-2);i+=3) if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];} } //-->

Analyzing Query Rewrites of Materialized Views

Purpose

This module shows you how to use execution plans to make query rewrites with materialized views easier to interpret and use the REWRITE_OR_ERROR hint to make query rewrite debugging easier.

Topics

This module discusses the following topics:

 Overview
 Prerequisites
 Generating Explain Plans and Interpreting the Results
 Using the REWRITE_ON_ERROR Hint

Viewing Screenshots

 Place the cursor on this icon to display all the screenshots. You can also place the cursor on each individual icon in the following steps to see only the screenshot associated with that step.

Overview

Back to List

Using Explain Plans to Analyze Query Rewrites

Prior to Oracle Database 10g, external tables were read-only. In Oracle Database 10g, external tables can also be written to. Although neither data manipulation language (DML) operations nor index creation is allowed on an external table, you can use the CREATE TABLE AS SELECT command to populate an external table composed of proprietary format (Direct Path API) flat files that are operating system independent.

In the context of external tables, loading data refers to the act of data being read from an external table and loaded into a table in the database. Unloading data refers to the act of reading data from a table in the database and inserting it into an external table. Both these operations can be used with external tables using the new Data Pump access driver.

REWRITE_OR_ERROR Hint

There may be situations where you want to stop the query from executing if it did not rewrite. One such situation can be when you expect the un-rewritten query to take an unacceptably long time to execute. To support this requirement, Oracle Database 10g provides a new hint called REWRITE_OR_ERROR. This is a query block-level hint. For example, if the SELECT statement is not rewritten, the error displayed in the message is thrown. The REWRITE_OR_ERROR hint allows you to run DBMS_MVIEW.EXPLAIN_REWRITE() on the query, resolve the problems that caused rewrite to fail, and run the query again.

Prerequisites

Back to List

Before starting this module, you should have:

1.

Completed the Configuring Linux for the Installation of Oracle Database 10g lesson

 

2.

Completed the Installing the Oracle Database 10g on Linux lesson

 

3.

Completed the Postinstallation Tasks lesson.

 

4.

Downloaded and unzipped mvplans.zip into your working directory (for example, /home/oracle/wkdir)

 

Generating Explain Plans and Interpreting the Results

Back to Topic List

It is common practice to use naming conventions for materialized views (MVs); for example, to distinguish MVs from regular tables in execution plans. Oracle Database 10g improves this situation by providing better information in the PLAN_TABLE and in the V$SQL_PLAN view; they show MATERIALIZED VIEW instead of TABLE. Moreover, they show the difference between MV usage as a result of query rewrite and direct MV access. Perform the following steps:

1.

Create the materialized view and gather statistics on the materialized view and its underlying tables. From your terminal window, execute the following commands:

                               
                                 
cd wkdir
sqlplus sh/sh
@mvsetup
                              
                            

The query in the mvsetup.sql script is as follows:

                               
drop materialized view sales_prod;
                                 
create materialized view sales_prod
build immediate
enable query rewrite
as
SELECT s.prod_id
, t.fiscal_month_number
, sum(s.amount_sold) AS sum_amount
FROM sales s, times t
WHERE s.time_id = t.time_id
AND t.fiscal_year = 2000
GROUP BY s.prod_id, t.fiscal_month_number;
execute dbms_stats.gather_table_stats('SH','SALES_PROD');
execute dbms_stats.gather_table_stats('SH','SALES');
execute dbms_stats.gather_table_stats('SH','TIMES');

Move your mouse over this icon to see the image

 

2.

Now you can generate an execution plan for a query that will be rewritten. Execute the following script from your terminal window:

                               
                                 
@execplan01
                              
                            

The query in the execplan01.sql script is as follows:

                               
EXPLAIN PLAN FOR
                                
SELECT s.prod_id
, t.fiscal_month_number
, sum(s.amount_sold) AS sum_amount
FROM sales s, times t
WHERE s.time_id = t.time_id
AND t.fiscal_year = 2000
GROUP BY s.prod_id, t.fiscal_month_number
ORDER BY s.prod_id, t.fiscal_month_number;

Move your mouse over this icon to see the image

 

3.

Now that the execution plan is generated, you can use the DBMS_XPLAN package to display the execution plan. From your terminal window, execute the following command:

                               
                                 
SELECT * FROM table(dbms_xplan.display);
                              
                            

Move your mouse over this icon to see the image

Note the execution plan explicitly shows materialized view usage and the purpose ( REWRITE).

 

4.

To see what would happen if the materialized view was explicitly accessed in the FROM component of a query, execute the following command:

                               
                                 
EXPLAIN PLAN FOR SELECT * FROM sales_prod;
                              
                            

Move your mouse over this icon to see the image

 

5.

Now you can display the execution plan again to see the difference. From your terminal window, execute the following command:

                               
                                 
SELECT * FROM table(dbms_xplan.display);
                              
                            

Move your mouse over this icon to see the image

Note the execution plan still shows MAT_VIEW access (as opposed to TABLE access) but the REWRITE is gone.

 

Using the REWRITE_OR_ERROR Hint

Back to Topic List

There may be situations where you want to stop a query from executing if it did not rewrite. One such situation is when you expect the un-rewritten query to take an unacceptably long time to execute. To support this requirement, Oracle Database 10g provides a new hint called REWRITE_OR_ERROR. If a query is not rewritten, error ORA-30393 is thrown. This feature allows you to run DBMS_MVIEW.EXPLAIN_REWRITE() on the query, resolve the problems that caused rewrite to fail, and run the query again. To obtain EXPLAIN_REWRITE output into a table, you must run the utlxrw.sql script before calling EXPLAIN_REWRITE. This script creates a table named REWRITE_TABLE in the current schema. Perform the following steps:

1.

Before you begin you need to re-create your rewrite table. The utlxrw.sql script creates the REWRITE table. You need this table to capture the output of the EXPLAIN_REWRITE procedure. From your terminal window, execute the following command:

                               
                                 
@crewrt01
                              
                            

The command in the crewrt01 .sql script is as follows:

                               
                                 
drop table rewrite_table;
                                  
@$ORACLE_HOME/rdbms/admin/utlxrw

Move your mouse over this icon to see the image

 

2.

Typically when a query rewrite fails, the Oracle database executes the statement against the underlying base tables. The REWRITE_OR_ERROR hint changes the behavior, and generates an error message when query rewrite fails. To use a hint in a query, execute the following command from your terminal window:

                               
                                 
@hint01
                              
                            

The command in the hint01.sql script is as follows:

                               
SELECT   /*+ REWRITE_OR_ERROR */
                                
s.prod_id
, sum(s.quantity_sold)
FROM sales s
GROUP BY s.prod_id;

Move your mouse over this icon to see the image

Note that an error message was generated.

 

3.

You can use the EXPLAIN_REWRITE procedure to determine why query rewrite failed. The results will be captured in the REWRITE_TABLE you created previously. Execute the following script:

                               
                                 
@exprewrt01
                              
                            

The command in the exprewrt 01.sql script is as follows:

                               
execute dbms_mview.EXPLAIN_REWRITE             -
                                
( query => 'SELECT s.prod_id -
, sum(s.quantity_sold) -
FROM sales s -
GROUP BY s.prod_id' -
, mv => 'SH.SALES_PROD' -
, statement_id => 'EXPLAIN_REWRITE demo' -
);

Move your mouse over this icon to see the image

 

4.

Now you can query the REWRITE_TABLE to obtain results. Execute the following script:

                               
                                 
@shresult01
                              
                            

The command in the shresult 01.sql script is as follows:

                               
SELECT message
                                
FROM rewrite_table
WHERE statement_id = 'EXPLAIN_REWRITE demo';

Move your mouse over this icon to see the image

Note that there are three issues with the query.

 

5.

To see where the issues are, look at the materialized view definition you created at the beginning of this lesson as follows:

                               
CREATE  
                              
MATERIALIZED VIEW sales_prod
  build immediate
  enable query rewrite
  as
     SELECT s.prod_id
          , t.fiscal_month_number 
          , sum(s.amount_sold) AS sum_amount
     FROM sales s, times t
     WHERE s.time_id = t.time_id
     AND t.fiscal_year = 2000
     GROUP BY s.prod_id, t.fiscal_month_number;
                            

Then you need to look at the query you executed as follows:

                               
SELECT s.prod_id  
                              
    , sum(s.quantity_sold)
FROM sales s
GROUP BY s.prod_id;
                            

As you can see, the materialized view utilizes the TIMES dimension, which is not used in the query, and the materialized view aggregates AMOUNT_SOLD while the query is aggregating QUANTITY_SOLD. Therefore query rewrite is impossible.

 

 Place the cursor on this icon to hide all screenshots.

 

Left Curve
Popular Downloads
Right Curve
Untitled Document