DBMS_ADVISOR.TUNE_MVIEW
As
well as containing functionality that recommends suitable
materialized views and indexes, DBMS_ADVISOR contains a procedure
called TUNE_MVIEW that is used when working with the query rewrite
mechanism.
Oracle9i came with two packages, DBMS_MVIEW.EXPLAIN_MVIEW and
DBMS_MVIEW.EXPLAIN_REWRITE that could be used to diagnose why a
materialized view wasn't being used for query rewrite. However,
although these packages told you why rewrite hadn't happened, they
left it down to you to work out how to alter your CREATE
MATERIALIZED VIEW statement to ensure that rewrite happened
correctly. Oracle 10g's DBMS_ADVISOR.TUNE_MVIEW builds on
this functionality and tells the DBA what changes to make to a
materialized view to make it suitable for query rewrite, taking as
its input a CREATE MATERIALIZED VIEW statement and outputting a
corrected version that supports query rewrite and features such as
fast refresh.
Tuning a materialized view follows a similar process to that used
with the SQL Access Advisor.
1.
First, create a task and supply a CREATE MATERIALIZED VIEW
statement to DBMS_ADVISOR.TUNE_MVIEW
EXECUTE DBMS_ADVISOR.TUNE_MVIEW(?my_first_mv?, ' -
CREATE MATERIALIZED VIEW sales_mv -
REFRESH FAST -
disable QUERY REWRITE AS -
select category, country, sum(sales) -
from product p, geography g, sales s -
where s.product_id = p.product_id -
and s.geography_id = p.geography_id -
group by p.category, g.country
');
2.
You can then directly query the resulting tuned CREATE
MATERIALIZED VIEW statement from the USER_TUNE_MVIEW view:
SELECT * FROM
USER_TUNE_MVIEW
WHERE TASK_NAME= ?my_first_mv? AND
SCRIPT_TYPE='IMPLEMENTATION';
3.
Or you can output the tuned statement to the filesystem using the
DBMS_ADVISOR.GET_TASK_SCRIPT procedure.
SELECT * FROM
USER_TUNE_MVIEW
WHERE TASK_NAME= ?my_first_mv? AND
SCRIPT_TYPE='IMPLEMENTATION';
The
DBMS_ADVISOR.TUNE_MVIEW procedure has the capability to add
additional aggregate columns and materialized view logs to the
view definition so that it becomes fast refreshable, and it can
restate the view definition (sometimes breaking it into separate,
simpler definitions) so that it is more likely to satisfy query
rewrite restrictions.
DBA Advisor Views: