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

References

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';

OWNER	TASK_NAME	ACTION_ID	SCRIPT_TYPE	STATEMENT
SH	OCM11GPROD	3	IMPLEMENTATION	CREATE MATERIALIZED VIEW LOG ON "SH"."PRODUCTS" WITH ROWID, SEQUENCE ("PROD_CATEGORY") INCLUDING NEW VALUES
SH	OCM11GPROD	4	IMPLEMENTATION	ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."PRODUCTS" ADD ROWID, SEQUENCE ("PROD_CATEGORY") INCLUDING NEW VALUES
SH	OCM11GPROD	5	IMPLEMENTATION	CREATE MATERIALIZED VIEW SH.OCM11G_PROD REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT SH.PRODUCTS.PROD_CATEGORY C1, COUNT(*) M1 FROM SH.PRODUCTS GROUP BY SH.PRODUCTS.PROD_CATEGORY
SH	OCM11GPROD	6	UNDO	DROP MATERIALIZED VIEW SH.OCM11G_PROD

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:

CAPABILITY_NAME	POSSIBLE	RELATED_TEXT	MSGNO	MSGTXT
PCT	Y
REFRESH_COMPLETE	Y
REFRESH_FAST	Y
REWRITE	N
PCT_TABLE	Y	SALES
PCT_TABLE	N	PRODUCTS	2068	relation is not a partitioned table
PCT_TABLE	N	CUSTOMERS	2068	relation is not a partitioned table
PCT_TABLE	N	COUNTRIES	2068	relation is not a partitioned table
PCT_TABLE	N	CHANNELS	2068	relation is not a partitioned table
PCT_TABLE	N	TIMES	2068	relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT	N	SH.SALES	2162	the detail table does not have a materialized view log
REFRESH_FAST_AFTER_INSERT	N	SH.PRODUCTS	2162	the detail table does not have a materialized view log
REFRESH_FAST_AFTER_INSERT	N	SH.CUSTOMERS	2162	the detail table does not have a materialized view log
REFRESH_FAST_AFTER_INSERT	N	SH.COUNTRIES	2162	the detail table does not have a materialized view log
REFRESH_FAST_AFTER_INSERT	N	SH.CHANNELS	2162	the detail table does not have a materialized view log
REFRESH_FAST_AFTER_INSERT	N	SH.TIMES	2162	the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML	N	TOTAL_AMOUNT_SOLD	2143	SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML	N	TOTAL_QUANTITY_SOLD	2143	SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML	N		2146	see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML	N		2142	COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ONETAB_DML	N		2143	SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML	N		2161	see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT	Y
REWRITE_FULL_TEXT_MATCH	N		2159	query rewrite is disabled on the materialized view
REWRITE_PARTIAL_TEXT_MATCH	N		2159	query rewrite is disabled on the materialized view
REWRITE_GENERAL	N		2159	query rewrite is disabled on the materialized view
REWRITE_PCT	N		2158	general rewrite is not possible or PCT is not possible on any of the detail tables
PCT_TABLE_REWRITE	N	SALES	2182	PCT is enabled through a join dependency
PCT_TABLE_REWRITE	N	PRODUCTS	2068	relation is not a partitioned table
PCT_TABLE_REWRITE	N	CUSTOMERS	2068	relation is not a partitioned table
PCT_TABLE_REWRITE	N	COUNTRIES	2068	relation is not a partitioned table
PCT_TABLE_REWRITE	N	CHANNELS	2068	relation is not a partitioned table
PCT_TABLE_REWRITE	N	TIMES	2068	relation is not a partitioned table

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:

CAPABILITY_NAME	POSSIBLE	RELATED_TEXT	MSGNO	MSGTXT
PCT	N
REFRESH_COMPLETE	Y
REFRESH_FAST	N
REWRITE	N
PCT_TABLE	N	PRODUCTS	2068	relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT	N	SH.PRODUCTS	2162	the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML	N		2146	see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML	N		2161	see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT	N		2157	PCT is not possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH	N		2159	query rewrite is disabled on the materialized view
REWRITE_PARTIAL_TEXT_MATCH	N		2159	query rewrite is disabled on the materialized view
REWRITE_GENERAL	N		2159	query rewrite is disabled on the materialized view
REWRITE_PCT	N		2158	general rewrite is not possible or PCT is not possible on any of the detail tables
PCT_TABLE_REWRITE	N	PRODUCTS	2068	relation is not a partitioned table

Enable Query Rewrite

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