Oracle Application DBA Portal

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.

Labels: Materialized Views

posted by Srinivasan .R @ 12:25 PM

1 Comments:

At March 7, 2017 at 11:25 PM, ERP said…

oracle fusion Cloud HCM online training at erptree.com is worlds best online training center.we have excelent knowledge sharing Platform. we have students from all over the world.We have 10+ years of experience, we can serve various ascent people. user friendly website developed for students where you will be provided with all the required details and Self-paced DEMO videos.we have our branches in pune, gurgaon, noida, india, usa, uk, uae, oracle fusion hcm training, fusion Procurement training, fusion hcm, scm training