11G OCM Troubleshoot fast materialized views to fast refresh and query rewrite

References

Oracle® Database Performance Tuning Guide 11g Release 1 (11.1)

Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1)

Oracle® Database Reference 11g Release 1 (11.1)

Oracle® Database SQL Language Reference 11g Release 1 (11.1)

Overview

The objective from the 11G OCM Upgrade Exam is:

Troubleshoot fast materialized views to fast refresh and query rewrite

Reading Notes

Overview of the SQL Access Advisor

“Overview of the SQL Access Advisor” says that:

The SQL Access Advisor, using the TUNE_MVIEW procedure, also recommends how to optimize materialized views so that they can be fast refreshable and take advantage of general query rewrite.

Tuning Materialized Views for Fast Refresh and Query Rewrite

“Tuning Materialized Views for Fast Refresh and Query Rewrite” says that:

To further facilitate the use of materialized views, the TUNE_MVIEW procedure shows you how to optimize your CREATE MATERIALIZED VIEW statement and to meet other requirements such as materialized view log and rewrite equivalence relationship for fast refresh and general query rewrite. TUNE_MVIEW analyzes and processes the CREATE MATERIALIZED VIEW statement and generates two sets of output results: one for the materialized view implementation and the other for undoing the CREATE MATERIALIZED VIEW operations. The two sets of output results can be accessed through views or be stored in external script files created by the SQL Access Advisor. These external script files are ready to execute to implement the materialized view.

EXPLAIN_MVIEW

“EXPLAIN_MVIEW” says that:

This procedure enables you to learn what is possible with a materialized view or potential materialized view. For example, you can determine if a materialized view is fast refreshable and what types of query rewrite you can perform with a particular materialized view.

EXPLAIN_REWRITE

“EXPLAIN_REWRITE” says that:

This procedure enables you to learn why a query failed to rewrite, or, if it rewrites, which materialized views will be used. Using the results from the procedure, you can take the appropriate action needed to make a query rewrite if at all possible.

Test Case

Set Up Test User

The SH user is set up for the test as follows:

ALTER USER sh ACCOUNT UNLOCK;

GRANT ADVISOR, CREATE MATERIALIZED VIEW TO sh;

Using TUNE_MVIEW

Using the test query from Use SQL Tuning Advisor, I attempted to invoke the TUNE_MVIEW procedure as the SH user:

DECLARE

sql_text VARCHAR2(4000);

task_name VARCHAR2(32);

BEGIN

sql_text := 'CREATE MATERIALIZED VIEW ocm11g AS -

SELECT -

products.prod_category, -

products.prod_subcategory, -

times.calendar_year, -

SUM( sales.quantity_sold ) -

AS total_quantity_sold, -

SUM( sales.amount_sold ) -

AS total_amount_sold -

FROM -

sales -

INNER JOIN -

products -

USING( prod_id ) -

INNER JOIN -

customers -

USING( cust_id ) -

INNER JOIN -

countries -

USING( country_id ) -

INNER JOIN -

channels -

USING( channel_id ) -

INNER JOIN -

times -

USING( time_id ) -

WHERE -

products.prod_valid = ''A'' -

AND -

channels.channel_class = ''Direct'' -

AND -

customers.cust_valid = ''A'' -

AND -

countries.country_name = ''Australia'' -

GROUP BY -

products.prod_category, -

products.prod_subcategory, -

times.calendar_year -

ORDER BY -

products.prod_category, -

products.prod_subcategory, -

times.calendar_year';

task_name := 'OCM11G';

dbms_advisor.TUNE_MVIEW( task_name, sql_text );

END;

/

However, this fails with the following error messages:

ERROR at line 1: ORA-13600: error encountered in Advisor QSM-03112: Invalid CREATE MATERIALIZED VIEW statement ORA-00928: missing SELECT keyword ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 205 ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 460 ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 901 ORA-06512: at "SYS.DBMS_ADVISOR", line 734 ORA-06512: at line 48

When I execute the script for the materialized view by itself, the materialized view is created successfully.

Second Attempt with TUNE_MVIEW

I decided to use a simpler materialized view to test the TUNE_MVIEW procedure:

variable task_name varchar2(32)

variable sql_text varchar2(4000)

exec :task_name := 'OCM11GPROD'

exec :sql_text := 'CREATE MATERIALIZED VIEW ocm11g_prod AS select prod_category, count(*) as num_products from products group by prod_category'

exec dbms_advisor.tune_mview( :task_name, :sql_text )

This worked fine. The results from DBA_TUNE_MVIEW are:

SELECT * FROM DBA_TUNE_MVIEW WHERE task_name = 'OCM11GPROD';

It appears that I can make the materialized view fast refreshable, but query rewrite is disabled.

Using EXPLAIN_MVIEW

First, the user, SH, is granted the appropriate authorities:

GRANT CREATE TABLE TO sh;

Then, the user, SH, creates the MV_CAPABILITIES_TABLE:

@?/rdbms/admin/utlxmv

Next, the first materialized view created above, OCM11G, is examined:

EXEC DBMS_MVIEW.EXPLAIN_MVIEW( 'OCM11G', 'OCM11G' )

COMMIT;

SELECT CAPABILITY_NAME,

POSSIBLE,

RELATED_TEXT,

MSGNO,

MSGTXT

FROM mv_capabilities_table

WHERE statement_id = 'OCM11G';

The result is:

And, the second materialized view created above, OCM11G_PROD, is examined:

EXEC DBMS_MVIEW.EXPLAIN_MVIEW( 'OCM11G_PROD', 'OCM11G2' )

COMMIT;

SELECT CAPABILITY_NAME,

POSSIBLE,

RELATED_TEXT,

MSGNO,

MSGTXT

FROM mv_capabilities_table

WHERE statement_id = 'OCM11G2';

The result is:

Enable Query Rewrite

In both of the materialized views examined above, query rewrite is disabled .