Materialized Views for 10g DBA

Materialized views (MVs), also known as snapshots, have been around for quite some time now. MVs store the result of a query in a segment and can return that result to the user when the query is submitted, eliminating the need to re-execute the query—an advantage when the query is issued several times, as is typical in data warehouse environments. MVs can be refreshed from base tables either completely or incrementally using a fast refresh mechanism.

Assume you have defined an MV as follows:

create materialized view mv_hotel_resv
refresh fast
enable query rewrite
as
select distinct city, resv_id, cust_name
from hotels h, reservations r
where r.hotel_id = h.hotel_id';

How would you know that all the necessary objects have been created for this MV to work perfectly? Prior to Oracle Database 10g, this determination was performed with the procedures EXPLAIN_MVIEW and EXPLAIN_REWRITE in the package DBMS_MVIEW. These procedures, which are still available in 10g, explain very succinctly whether a specific capability—such as fast refreshability or query rewritability—are possible with the said MV but don't offer any recommendations to make those capabilities possible. Instead, a visual inspection of the structure of each MV is required, which is quite impractical.

In 10g, a procedure called TUNE_MVIEW in the new package DBMS_ADVISOR makes that job very easy: You call the package with the IN parameter, which constitutes the whole text of the MV creation script. The procedure creates an Advisor Task, which has a specific name passed back to you using only the OUT parameter.

Here's an example. Because the first parameter is an OUT parameter, you need to define a variable to hold it in SQL*Plus.

SQL> -- first define a variable to hold the OUT parameter
SQL> var adv_name varchar2(20)
SQL> begin
2 dbms_advisor.tune_mview
3 (
4 :adv_name,
5 'create materialized view mv_hotel_resv refresh fast enable query rewrite as
select distinct city, resv_id, cust_name from hotels h,
reservations r where r.hotel_id = h.hotel_id');
6* end;

Now you can find out the name of the Advisor from the variable.

SQL> print adv_name

ADV_NAME
-----------------------
TASK_117

Next, get the advice provided by this Advisor by querying a new DBA_TUNE_MVIEW. Make sure you execute SET LONG 999999 before running this command because the column statement in this view is a CLOB and by default only 80 characters are displayed.

select script_type, statement
from dba_tune_mview
where task_name = 'TASK_117'
order by script_type, action_id;

Here is the output:

SCRIPT_TYPE    STATEMENT
-------------- ------------------------------------------------------------
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."HOTELS" WITH ROWID,
               SEQUENCE ("HOTEL_ID","CITY")  INCLUDING NEW VALUES

IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."HOTELS" ADD
               ROWID, SEQUENCE ("HOTEL_ID","CITY")  INCLUDING NEW VALUES

IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."RESERVATIONS" WITH
               ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
               INCLUDING NEW VALUES

IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."RESERVATIONS"
               ADD ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
               INCLUDING NEW VALUES

IMPLEMENTATION CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV   REFRESH FAST
               WITH ROWID ENABLE QUERY REWRITE AS SELECT
               ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID
               C2, ARUP.HOTELS.CITY C3, COUNT(*) M1 FROM ARUP.RESERVATIONS,
               ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID =
               ARUP.RESERVATIONS.HOTEL_ID GROUP BY
               ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
               ARUP.HOTELS.CITY

UNDO           DROP MATERIALIZED VIEW ARUP.MV_HOTEL_RESV

If you review these automatically-generated recommendations carefully, you'll note that they are similar to what you would have produced yourself via visual analysis. The recommendations are logical; the presence of fast refresh needs to have a MATERIALIZED VIEW LOG on the base tables with appropriate clauses such as those including new values. The STATEMENT column even provides the exact SQL statements for implementing these recommendations.

In the final step of the implementation, the Advisor suggests changes in the way the MV is created. Note the difference in our example: a count(*) has been added to the MV. Because we defined this MV as fast refreshable, the count(*) has to be there, so the Advisor corrected the omission.

The procedure TUNE_MVIEW goes beyond what was available in EXPLAIN_MVIEW and EXPLAIN_REWRITE not just in its recommendations, but also by identifying easier and more efficient paths for creating the same MV. Sometimes the Advisor can actually recommend more than a single MV to make the query more efficient.

How is that useful, you may ask, when any seasoned DBA can find out what was missing in the MV creation script and then adjust it themselves? Well, the Advisor does exactly that: it is a seasoned, highly motivated, robotic DBA that can make recommendations comparable to a human but with a very important difference: it works for free and doesn't ask for vacations or raises. This benefit frees senior DBAs to offload routine tasks to less senior ones, allowing them to apply their expertise to more strategic goals.

You can also pass an Advisor name as the value to the parameter in the TUNE_MVIEW procedure, which generates an Advisor with that name instead of a system-generated one.

Easier Implementation

begin
dbms_advisor.create_file (
dbms_advisor.get_task_script ('TASK_117'),
'MVTUNE_OUTDIR',
'mvtune_script.sql'
);
end;
/

This procedure call assumes that you have defined a directory object, such as:

create directory mvtune_outdir as '/home/oracle/mvtune_outdir';

The call to dbms_advisor will create a file called mvtune_script.sql in the directory /home/oracle/mvtune_outdir. If you take a look at this file, you will see:

Rem SQL Access Advisor: Version 10.1.0.1 - Production
Rem
Rem Username: ARUP
Rem Task: TASK_117
Rem Execution date:
Rem

set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60

whenever sqlerror CONTINUE

CREATE MATERIALIZED VIEW LOG ON
"ARUP"."HOTELS"
WITH ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."HOTELS"
ADD ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"ARUP"."RESERVATIONS"
WITH ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID C2, ARUP.HOTELS.CITY
C3, COUNT(*) M1 FROM ARUP.RESERVATIONS, ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID
= ARUP.RESERVATIONS.HOTEL_ID GROUP BY ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITY;

whenever sqlerror EXIT SQL.SQLCODE

begin
dbms_advisor.mark_recommendation('TASK_117',1,'IMPLEMENTED');
end;
/

This file contains everything you need to implement the recommendations, saving you considerable trouble in creating a file by hand. Once again, the robotic DBA can do your job for you.

Rewrite or Die!

Let's imagine a situation based on the above example. The user issues the following query:

Select city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
group by city;

The execution stats show the following:

0   recursive calls
0   db block gets
6   consistent gets
0   physical reads
0   redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2   SQL*Net roundtrips to/from client
1   sorts (memory)
0   sorts (disk)

But what if the query rewrite had failed? It could fail for several reasons: If the value of the initialization parameter query_rewrite_integrity is set to TRUSTED and the MV status is STALE, the query will not be rewritten. You could simulate this process by setting the value in the session before the query.

alter session set query_rewrite_enabled = false;

After this command, the explain plan shows the selection from all three tables and not from the MVs. The execution stats now show:

0   recursive calls
0   db block gets
16  consistent gets
0   physical reads
0   redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2   SQL*Net roundtrips to/from client
2   sorts (memory)
0   sorts (disk)

In Oracle9i Database and below, the decision is one-way: you can disable Query Rewrite but not the base table access. Oracle Database 10g, however, provides a mechanism to do that via a special hint, REWRITE_OR_ERROR. The above query would be written with the hint like this:

select /*+ REWRITE_OR_ERROR */ city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
group by city;

Note the error message now.

from hotels h, reservations r, trans t
     *
ERROR at line 2:
ORA-30393: a query block in the statement did not rewrite

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=6 Bytes=156)
1    0   SORT (GROUP BY) (Cost=11 Card=6 Bytes=156)
2    1     HASH JOIN (Cost=10 Card=80 Bytes=2080)
3    2       MERGE JOIN (Cost=6 Card=80 Bytes=1520)
4    3         TABLE ACCESS (BY INDEX ROWID) OF 'HOTELS' (TABLE) (Cost=2 Card=8 Bytes=104)
5    4           INDEX (FULL SCAN) OF 'PK_HOTELS' (INDEX (UNIQUE)) (Cost=1 Card=8)
6    3         SORT (JOIN) (Cost=4 Card=80 Bytes=480)
7    6           TABLE ACCESS (FULL) OF 'RESERVATIONS' (TABLE) 
				(Cost=3 Card=80 Bytes=480)
8    2       MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES'
			(MAT_VIEW REWRITE) (Cost=3 Card=80 Bytes=560)

Although you can always control resource utilization using Resource Manager, using the hint will prevent the issuance of queries even before the Resource Manager is called. Resource Manager estimates required resources based on optimizer statistics, so the presence or absence of reasonably accurate statistics will affect that process. The rewrite or error feature, however, will stop table access regardless of statistics.

Explain Plan Explains Better

MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE)

This method of access—MAT_VIEW REWRITE—is new; it shows that the MV is being accessed, not the table or segment. This procedure immediately tells you if the table or MV is used, even if the names don't imply the nature of the segment.

Conclusion