The analytic design team have completed report and issues SQL query! Oops...
You expected the response time of < 10 seconds, instead the front end is clocking for over 4 minutes, now.
You quickly investigate the active sessions in Oracle and see the BI query is performing FTS on your largest FACT table. Why is it not using the Materialized View you so painstakingly built? Why is Query Rewrite not happening?
DBMS_MVIEW.EXPLAIN_REWRITE comes to rescue!
Conditions that must be satisfied for MV Query Rewrite to occur
The conditions under which Oracle will Rewrite queries using MV are well documented here.
In summary following techniques are used for MV query rewrite.
In addition if your MV is not fresh (not expoected to be due to batch or on-demand rebuild) then it is required to set QUERY_REWRITE_INTEGRITY at session (or system level) to TRUSTED or STALE_TOLERATED.
When the MV is not used for Query Rewrite, how do we find out WHY?
We'll use DBMS_MVIEW.EXPLAIN_REWRITE.
Script for building dimension and fact tables are at the end of document. Borrowed from ASKTOM site.
Based on the setup and MV creation following query does NOT rewrite. Let us check WHY?
We'll create REWRITE_TABLE.
CREATE TABLE REWRITE_TABLE(
statement_id VARCHAR2(30), -- id for the query
mv_owner VARCHAR2(30), -- owner of the MV
mv_name VARCHAR2(30), -- name of the MV
sequence INTEGER, -- sequence no of the msg
query VARCHAR2(2000), -- user query
query_block_no INTEGER, -- block no of the current subquery
rewritten_txt VARCHAR2(2000), -- rewritten query
message VARCHAR2(512), -- EXPLAIN_REWRITE msg
pass VARCHAR2(3), -- rewrite pass no
mv_in_msg VARCHAR2(30), -- MV in current message
measure_in_msg VARCHAR2(30), -- Measure in current message
join_back_tbl VARCHAR2(30), -- Join back table in message
join_back_col VARCHAR2(30), -- Join back column in message
original_cost INTEGER, -- Cost of original query
rewritten_cost INTEGER, -- Cost of rewritten query
flags INTEGER, -- associated flags
reserved1 INTEGER, -- currently not used
reerved2 VARCHAR2(10)); -- currently not used;
truncate table REWRITE_TABLE;
DECLARE
qrytext VARCHAR2(500) :=' select /*+ REWRITE_OR_ERROR */ time_hierarchy.qtr_yyyy, sum(sales_amount)
from sales, time_hierarchy
where sales.trans_date = time_hierarchy.day
group by time_hierarchy.qtr_yyyy';
idno VARCHAR2(30) :='ID1';
BEGIN
DBMS_MVIEW.EXPLAIN_REWRITE(qrytext, '', idno);
END;
/
SELECT message FROM rewrite_table ORDER BY sequence;
QSM-01150: query did not rewrite
QSM-01082: Joining materialized view, SALES_MV, with table, TIME_HIERARCHY, not possible
QSM-01102: materialized view, SALES_MV, requires join back to table, TIME_HIERARCHY, on column, QTR_YYYY
APPENDIX A - Scripts
Set up for data :
set autotrace traceonly statistics;
set timing on;
drop table sales purge;
create table sales
(trans_date date, cust_id int, sales_amount number );
insert /*+ APPEND */ into sales
select trunc(sysdate,'year')+mod(rownum,366) TRANS_DATE,
mod(rownum,100) CUST_ID,
abs(dbms_random.random)/100 SALES_AMOUNT
from all_objects;
commit;
begin
for i in 1 .. 4
loop
insert /*+ APPEND */ into sales
select trans_date, cust_id, abs(dbms_random.random)/100
from sales;
commit;
end loop;
end;
/
select count(*) from sales;
drop table time_hierarchy;
create table time_hierarchy
(day, mmyyyy, mon_yyyy, qtr_yyyy, yyyy)
as
select distinct
trans_date DAY,
cast (to_char(trans_date,'yyyymm') as number) MMYYYY,
to_char(trans_date,'mon-yyyy') MON_YYYY,
'Q' || ceil( to_char(trans_date,'mm')/3) || ' FY'
|| to_char(trans_date,'yyyy') QTR_YYYY,
cast( to_char( trans_date, 'yyyy' ) as number ) YYYY
from sales;
exec dbms_stats.gather_table_stats( user, 'SALES', cascade=>true );
exec dbms_stats.gather_table_stats( user, 'TIME_HIERARCHY', cascade=>true );
exec dbms_stats.gather_table_stats( user, 'SALES_MV', cascade=>true );
drop materialized view sales_mv;
create materialized view sales_mv
build immediate
refresh on demand
enable query rewrite
as
select sales.cust_id, sum(sales.sales_amount) sales_amount,
time_hierarchy.mmyyyy
from sales, time_hierarchy
where sales.trans_date = time_hierarchy.day
group by sales.cust_id, time_hierarchy.mmyyyy;
Check Query Rewrite:
select name, value from v$parameter
where name in ('query_rewrite_enabled','query_rewrite_integrity');
alter session set query_rewrite_enabled = TRUE ;
alter session set query_rewrite_integrity = TRUSTED;
alter session set query_rewrite_integrity = STALE_TOLERATED;
