Oracle Database 10g: The Top 20 Features for DBAs

Join Oracle Magazine's 2003 "DBA of the Year" Arup Nanda over the next 20 weeks as he presents his list of the top Oracle Database 10g features for database administrators

Follow along! Download Oracle Database 10g


Week 12
Materialized Views

Managing materialized views is much easier in 10g with compulsory query rewrite and the introduction of powerful new tuning advisors that take guesswork out of the picture

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

The column SCRIPT_TYPE shows the nature of the recommendation. Most of the lines are to be implemented, hence the name IMPLEMENTATION. The recommended actions, if accepted, should be followed in a specific sequence indicated by the ACTION_ID column.

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

Now that you can see the recommendations, you may want to implement them. One way is to select the column STATEMENT, spool to a file, and execute that script file. An easier alternative is to call a supplied packaged procedure:

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!

By now you must have realized how important and useful the Query Rewrite feature is. It significantly reduces I/O and processing and returns results faster.

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)

Note the value of consistent gets, which is 6—a very low value. This result is based on the fact that the query was rewritten to use the two MVs created on the three tables. The selection was not from the tables, but from the MVs, thereby consuming fewer resources such as I/O and CPU.

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)

Note the value of consistent gets: it jumps to 16 from 6. In a real-life situation, this result may be unacceptable because the additional required resources are unavailable, and thus you may want to rewrite the query yourself. In that case, you can ensure that the query should be allowed if and only if it is rewritten.

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

ORA-30393 is a special type of error that indicates the statement could not be rewritten to make use of the MVs; hence, the statement failed. This failsafe will prevent potentially long running queries from hogging system resources. Beware of one potential pitfall, however: the query will be successful if one, not all, of the MVs could be used in rewriting the query. So if MV_ACTUAL_SALES but not MV_HOTEL_RESV can be used, the query will be rewritten and the error will not occur. In this case the execution plan will look like:

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)

The query did use MV_ACTUAL_SALES but not MV_HOTEL_RESV; thus, the tables HOTELS and RESERVATIONS are accessed. This approach, especially the full table scan of the latter, will definitely use more resources—a situation you would note while designing queries and MVs.

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

In the previous example, note the line in the explain plan output:

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

Managing MVs is much easier in 10g with the introduction of the powerful new tuning advisors that can tell you a lot about the design of the MVs, taking the guesswork out of the picture. I especially like the tuning recommendations that can generate a complete script that can be implemented quickly, saving significant time and effort. The ability to force rewriting or abort the query can be very helpful in decision-support systems where resources must be conserved, and where a query that is not rewritten should not be allowed to run amuck inside the database.

For more information about managing MVs in 10g, see Chapter 8 of the Oracle Database Data Warehousing Guide 10g Release 1 (10.1).

Next Week: Enterprise Manager 10g

Back to Series Index


Please rate this document:

Excellent Good Average Below Average Poor

Send us your comments