Using TUNE_MVIEW and EXPLAIN_REWRITE to enable us to tune MV Fast Refreshes and Query Rewrites

 We can use the DBMS_MVIEW.EXPLAIN_REWRITE package (which was first introduced in Oracle 9i if memory serves me right) to diagnose why Query redirection to a materialized view is not happening.

We can redirect the output to a table called REWRITE_TABLE which is created via the utlxrw.sql script located under $ORACLE_HOME/rdbms/admin.

 

Let us take a simple example to see how this works.

We have created a materialized view SALES_MV which is based on the following query in the SH schema:

create materialized view sales_mv
build immediate
enable query rewrite
as
SELECT p.prod_name, SUM(amount_sold)
FROM sales s, products p
WHERE s.prod_id = p.prod_id
AND prod_name > 'B%'
AND prod_name < 'C%'
GROUP BY prod_name;

We will now run the EXPLAIN_REWRITE procedure to see if a particular query will be redirected to the MV instead.

EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE -
('SELECT p.prod_name, SUM(amount_sold)-
FROM sales s, products p -
WHERE s.prod_id = p.prod_id -
AND prod_name > ''B%''-
AND prod_name < ''C%''-
GROUP BY prod_name',-
'SALES_MV','SH');

PL/SQL procedure successfully completed.

SQL> SELECT message FROM rewrite_table ORDER BY sequence;

MESSAGE
--------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01001: query rewrite not enabled
Well we have made a simple mistake – parameter QUERY_REWRITE_ENABLED was not set to TRUE!

 

Let us set the parameter QUERY_REWRITE_ENABLE to TRUE and will see that running the same EXPLAIN_MVIEW procedure will show that query redirection in fact occur.

SQL> SELECT message FROM rewrite_table ORDER BY sequence;

MESSAGE
--------------------------------------------------------------------------------
QSM-01151: query was rewritten
QSM-01209: query rewritten with materialized view, SALES_MV, using text match algorithm

Let’s now see what happens if we alter our SQL query as shown below. Query Rewrite does not happen in this case and w ecan see the reasons why.

SQL> truncate table rewrite_table;

Table truncated.

SQL>  EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE -
('SELECT p.prod_name, SUM(amount_sold)-
FROM sales s, products p -
WHERE s.prod_id = p.prod_id -
AND prod_name NOT LIKE ''A%'' -
GROUP BY prod_name',-
'SALES_MV','SH');> > > > > >

PL/SQL procedure successfully completed.

SQL> SELECT message FROM rewrite_table ORDER BY sequence;

MESSAGE
--------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01112: WHERE clause of mv, SALES_MV, is more restrictive than query
QSM-01052: referential integrity constraint on table, PRODUCTS, not VALID in ENFORCED integrity mode
Another package TUNE_MVIEW can also help us in another way by actually rewriting the CREATE MATERIALIZED VIEW statement for us to enable things like Fast Refreshes and also Query Rewrites.

 

Let us suppose we have a materialized view CUST_MV defined with a fast refresh and we then go an update some rows on the base table. When we try and refresh fast that MV, we will see that we are faced with an ORA-32314 error which states that a Refresh Fast is not supported on this MV after deletes/updates.

CREATE MATERIALIZED VIEW cust_mv
REFRESH FAST
ENABLE QUERY REWRITE AS
SELECT s.prod_id, s.cust_id, SUM(s.amount_sold) sum_amount
FROM sales s, customers cs
WHERE s.cust_id = cs.cust_id
GROUP BY s.prod_id, s.cust_id;

SQL> update sales set QUANTITY_SOLD=100 where prod_id=13;

6002 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('CUST_MV');
BEGIN dbms_mview.refresh('CUST_MV'); END;

*
ERROR at line 1:
ORA-32314: REFRESH FAST of "SH"."CUST_MV" unsupported after deletes/updates
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748
ORA-06512: at line 1

Let us now see how the TUNE_MVIEW package will help us to recreate the MV with a different definition which will allow for fast refreshes.

Define two variables – one for the Advisor task name and another for MV DDL text.

SQL> VARIABLE task_cust_mv VARCHAR2(30);
SQL> VARIABLE create_mv_ddl VARCHAR2(4000);
SQL> EXECUTE :task_cust_mv := 'TEST_TUNE_MV';

PL/SQL procedure successfully completed.

SQL> EXECUTE :create_mv_ddl := '-
CREATE MATERIALIZED VIEW cust_mv-
REFRESH FAST-
ENABLE QUERY REWRITE AS -
SELECT s.prod_id, s.cust_id, SUM(s.amount_sold) sum_amount-
FROM sales s, customers cs-
WHERE s.cust_id = cs.cust_id-
GROUP BY s.prod_id, s.cust_id';

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);

PL/SQL procedure successfully completed.

We can redirect the output to a script as well. In this case we have a directory DATA_PUMP_DIR which points to an OS location and we specify the script which should be created.

Note - usage of the DBMS_ADVISOR package will require the system privilege ADVISOR to be granted to the user executing the package.

SQL> EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv), -
> 'DATA_PUMP_DIR','create_mv.sql');

PL/SQL procedure successfully completed.
We can either view the recommendations via the view *_TUNE_MVIEW or from the generated SQL script file.

SQL> set long 500000
SQL> SELECT STATEMENT FROM USER_TUNE_MVIEW
WHERE TASK_NAME= :task_cust_mv AND SCRIPT_TYPE='IMPLEMENTATION';
  2
STATEMENT
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW SH.CUST_MV   REFRESH FAST WITH ROWID ENABLE QUERY REWRI
TE AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_
SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID
, SH.SALES.PROD_ID

SQL> !cat /u01/app/oracle/admin/11gdba/dpdump/create_mv.sql
Rem  SQL Access Advisor: Version 11.2.0.2.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            TEST_TUNE_MV
Rem  Execution date:
Rem

CREATE MATERIALIZED VIEW SH.CUST_MV
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD")
       M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
       SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID,
       SH.SALES.PROD_ID;

Now we will drop the MV and recreate it using the script provided by the Access Advisor. We will then make an update to the SALES table and see that even after the update, Fast Refresh of the MV is now possible and we do not get the same error as before.

SQL> drop materialized view SH.CUST_MV;

Materialized view dropped.

SQL> CREATE MATERIALIZED VIEW SH.CUST_MV
  2      REFRESH FAST WITH ROWID
  3      ENABLE QUERY REWRITE
  4      AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD")
  5         M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
  6         SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID,
  7         SH.SALES.PROD_ID;

Materialized view created.

SQL> update sales set quantity_sold=10 where prod_id=13;

6002 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('CUST_MV','FAST');

PL/SQL procedure successfully completed

What has happened to our other MV SALES_MV because we had updated the base table SALES. Let us see if the query rewrite is still happening.

SQL>  EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE -
('SELECT p.prod_name, SUM(amount_sold)-
FROM sales s, products p -
WHERE s.prod_id = p.prod_id -
AND prod_name > ''B%''-
AND prod_name < ''C%''-
GROUP BY prod_name',-
'SALES_MV','SH');> > > > > > >

PL/SQL procedure successfully completed.

SQL> SELECT message FROM rewrite_table ORDER BY sequence;

MESSAGE
--------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01106: materialized view, SALES_MV, is stale with respect to some partition(s) in the base table(s)

QSM-01052: referential integrity constraint on table, PRODUCTS, not VALID in ENFORCED integrity mode

QSM-01029: materialized view, SALES_MV, is stale in ENFORCED integrity mode

Let us try the same after we have refreshed the MV. We will see that the query rewrite has indeed happened and this is confirmed by running the EXPLAIN_REWRITE procedure as well as by looking the output of the EXPLAIN PLAN

SQL> exec dbms_mview.refresh('SALES_MV');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE -
('SELECT p.prod_name, SUM(amount_sold)-
FROM sales s, products p -
WHERE s.prod_id = p.prod_id -
AND prod_name > ''B%''-
AND prod_name < ''C%''-
GROUP BY prod_name',-
'SALES_MV','SH');> > > > > > >

PL/SQL procedure successfully completed.

SQL> SELECT message FROM rewrite_table ORDER BY sequence;

MESSAGE
------------------------------------------------------------------------------------------------------------------------
QSM-01151: query was rewritten
QSM-01209: query rewritten with materialized view, SALES_MV, using text match algorithm

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3876866481

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    40 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| SALES_MV |     1 |    40 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------