Selective Plan Capture

 

One of the great feature in Oracle database for the plan stability is SQL Plan management (aka SQL Plan baselines). With baselines only the known (or accepted) plans will be used for execution, however new plans generated during hard parse will be available in the plan history for evaluation with accepted plan. 

 

If these new plans were better than the accepted plan, then added to the plan baselines else they will remain in the plan history.

 

There are actually six different ways to populate plans into SPM

 

·         Automatic capture

·         From SQL Tuning sets

·         From cursor cache

·         Unpacked from a staging table

·         From existing stored outlines

·         From AWR repository (available from 12cR2)

 

Automatic capture is controlled by setting this parameter optimizer_capture_sql_plan_baselines – however starting with 12c, you can decide what sql statement want to be captured the sql plan baselines. Once you have that idea, you can use DBMS_SPM.CONFIGURE procedure to setup filters that will control which sql statement plans will be captured. Currently we can filter on four things.

 

·         Parsing schema

·         Action

·         Module

·         SQL Text

 

Let’s say we have a packaged application like this

 

demo@ORA12C> create or replace package mypkg

  2  as

  3     procedure display_sal ;

  4  end;

  5  /

 

Package created.

 

demo@ORA12C> create or replace package body mypkg

  2  as

  3     procedure display_sal

  4     as

  5     begin

  6             dbms_application_info.set_module(module_name=>'MYPKG',action_name=>'DISPLAY_SAL');

  7             for x in (select deptno,sum(sal) tot_sal

  8                             from emp

  9                             group by deptno )

 10             loop

 11                     null ;

 12             end loop;

 13             dbms_application_info.set_module(null,null);

 14     end;

 15  end;

 16  /

 

Package body created.

 

Now I need to baseline all the sql’s inside the module MYPKG, then we can configure automatic plan capture parameters like this

 

demo@ORA12C> exec dbms_spm.configure('AUTO_CAPTURE_MODULE','MYPKG');

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> exec dbms_spm.configure('AUTO_CAPTURE_ACTION','DISPLAY_SAL');

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select * from dba_sql_management_config;

 

PARAMETER_NAME                           PARAMETER_ LAST_MODIFIED        MODIFIED_BY

---------------------------------------- ---------- -------------------- --------------------

SPACE_BUDGET_PERCENT                     10

PLAN_RETENTION_WEEKS                     53

AUTO_CAPTURE_PARSING_SCHEMA_NAME

AUTO_CAPTURE_MODULE                      module IN  11-DEC-18 05.23.29.0 DEMO

                                         (MYPKG)    00000 PM

 

AUTO_CAPTURE_ACTION                      action IN  11-DEC-18 05.23.53.0 DEMO

                                         (DISPLAY_S 00000 PM

                                         AL)

 

AUTO_CAPTURE_SQL_TEXT

 

6 rows selected.

 

Then set the parameter (optimizer_capture_sql_plan_baselines) and begin the workloads.

 

demo@ORA12C> show parameter optimizer_capture_sql_plan_baselines

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_capture_sql_plan_baselines boolean     FALSE

demo@ORA12C> alter session set optimizer_capture_sql_plan_baselines = true;

 

Session altered.

 

demo@ORA12C> exec mypkg.display_sal;

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select * from dept;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

demo@ORA12C> exec mypkg.display_sal;

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select * from dept;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

demo@ORA12C> alter session set optimizer_capture_sql_plan_baselines = false;

 

Session altered.

 

Note that only the repeated sql statements that meets the configuration will be baselined. Repeated sql statements are statements that got executed more than once, to identify the repeated sql statements the optimizer logs the SQL Signature of each sql statement executed the first time it is compiled in the SQL statement log (sqllog$).

 

demo@ORA12C> select sql_handle,sql_text from dba_sql_plan_baselines;

 

SQL_HANDLE                     SQL_TEXT

------------------------------ ----------------------------------------

SQL_d9fdf5e68f019b40           SELECT DEPTNO,SUM(SAL) TOT_SAL FROM EMP

                               GROUP BY DEPTNO

 

 

demo@ORA12C> select t2.*

  2  from dba_sql_plan_baselines t1,

  3      table( dbms_xplan.display_sql_plan_baseline( t1.sql_handle,t1.plan_name)) t2;

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

SQL handle: SQL_d9fdf5e68f019b40

SQL text: SELECT DEPTNO,SUM(SAL) TOT_SAL FROM EMP GROUP BY DEPTNO

--------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

Plan name: SQL_PLAN_dmzgpwu7h36u0daae7798         Plan id: 3668866968

Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE

Plan rows: From dictionary

--------------------------------------------------------------------------------

 

Plan hash value: 4067220884

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     3 |    21 |     3  (34)| 00:00:01 |

|   1 |  HASH GROUP BY     |      |     3 |    21 |     3  (34)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------

 

21 rows selected.

 

demo@ORA12C>

 

As you can see from above, even though we have executed two repeated SQL statements (one sql executed inside the package and one sql executed outside the package), only one sql plan baselines were created.  

 

The sql statement executed outside the context of the package did not have plan baseline automatically created for it, because it was not one of the application module we told SPM that we wanted to automatically capture SQL Plan baselines for.

 

By selecting only the required configuration that we are really interested in, we can keep the number of SQL Plan baselines to a reasonable amount, that in-turn make it easier to manage them or move them between the environments.

 

Finally to remove the filters, we can have them set to NULL.

 

demo@ORA12C> select * from dba_sql_management_config;

 

PARAMETER_NAME                           PARAMETER_ LAST_MODIFIED        MODIFIED_BY

---------------------------------------- ---------- -------------------- ----------------

SPACE_BUDGET_PERCENT                     10

PLAN_RETENTION_WEEKS                     53

AUTO_CAPTURE_PARSING_SCHEMA_NAME

AUTO_CAPTURE_MODULE                      module IN  11-DEC-18 05.23.29.0 DEMO

                                         (MYPKG)    00000 PM

 

AUTO_CAPTURE_ACTION                      action IN  11-DEC-18 05.23.53.0 DEMO

                                         (DISPLAY_S 00000 PM

                                         AL)

 

AUTO_CAPTURE_SQL_TEXT

 

6 rows selected.

 

demo@ORA12C> exec dbms_spm.configure('AUTO_CAPTURE_MODULE',null);

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> exec dbms_spm.configure('AUTO_CAPTURE_ACTION',null);

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select * from dba_sql_management_config;

 

PARAMETER_NAME                           PARAMETER_ LAST_MODIFIED        MODIFIED_BY

---------------------------------------- ---------- -------------------- ----------------

SPACE_BUDGET_PERCENT                     10

PLAN_RETENTION_WEEKS                     53

AUTO_CAPTURE_PARSING_SCHEMA_NAME

AUTO_CAPTURE_MODULE                                 11-DEC-18 05.30.44.0 DEMO

                                                    00000 PM

 

AUTO_CAPTURE_ACTION                                 11-DEC-18 05.30.44.0 DEMO

                                                    00000 PM

 

AUTO_CAPTURE_SQL_TEXT

 

6 rows selected.

 

demo@ORA12C>

 

ANSI Joins and query rewrites

 

One of the things I love about working with Oracle Database technologies is that there’s just so much one can learn. I make it an active goal of mine to try to learn something new at work each and every day, no matter how big or small.

 

One of many good reason to use ANSI join syntax over an Oracle join syntax is that it supports a clear distinction between join condition and filter condition in the SQL statement.

 

However there is a strange behavior with Oracle database that Materialized view designed for Query rewrite purpose should be defined using Oracle join syntax only, otherwise query rewrites won’t work properly.

 

To illustrate this will create materialized view (one with ANSI and one with Oracle join syntax) and execute different queries that should be able to use Query rewrites.

 

Let’s create a couple of dimension and fact tables for a demo.

 

demo@ORA12C> create table products as

  2  select rownum as prod_id,

  3     object_name as prod_name,

  4     object_type as prod_category,

  5     object_id as prod_category_id,

  6     data_object_id as prod_subcategory_id,

  7     round(dbms_random.value(1,2000)) as prod_min_price,

  8     round(dbms_random.value(1,4000)) as prod_max_price,

  9     'Total' as prod_total,

 10     to_date('01/01/2010','mm/dd/yyyy')+rownum as effective_from,

 11     add_months(to_date('01/01/2010','mm/dd/yyyy')+rownum,3) as effective_to,

 12     status as prod_status

 13  from all_objects ;

 

Table created.

 

demo@ORA12C> alter table products

  2  add constraint prod_pk

  3  primary key(prod_id);

 

Table altered.

 

demo@ORA12C> create table customers as

  2  select rownum as cust_id,

  3       object_name as cust_fname,

  4       subobject_name as cust_lname,

  5       decode(mod(rownum,2),0,'M','F') gender,

  6       to_char(created,'YYYY') as cust_birth_year,

  7       'Single' as cust_marital_status,

  8       dbms_random.string('A',30) as cust_street_address,

  9       dbms_random.value(1,10000) as cust_postal_code,

 10       dbms_random.string('E',5) as cust_city,

 11       dbms_random.string('C',5) as cust_state_province,

 12       round(dbms_random.value(1,9999))||'-'||

 13       round(dbms_random.value(1,999))||'-'||

 14       round(dbms_random.value(1,999)) as cust_phone_no,

 15       object_name||'@company.com' as cust_email,

 16       'CustomerTotal' as cust_total,

 17       to_date('01/01/2010','mm/dd/yyyy')+rownum as cust_effective_from,

 18       add_months(to_date('01/01/2010','mm/dd/yyyy')+rownum,3) as cust_effective_to

 19  from all_objects ;

 

Table created.

 

demo@ORA12C> alter table customers

  2  add constraint cust_pk

  3  primary key(cust_id);

 

Table altered.

 

demo@ORA12C> create table sales as

  2  select mod(rownum,10000)+1 as prod_id,

  3      mod(rownum,1000)+1 as cust_id,

  4      to_date('01-Jan-1998','dd-mon-yyyy')

  5      +mod(rownum,4000) as time_id,

  6      mod(rownum,10) as quantity_sold,

  7      round(dbms_random.value(1,5000)) as amount_sold

  8  from big_table;

 

Table created.

 

demo@ORA12C> alter table sales

  2  add constraint fk_sales_prod

  3  foreign key(prod_id)

  4  references products

  5  modify prod_id not null;

 

Table altered.

 

demo@ORA12C> alter table sales

  2  add constraint fk_sales_cust

  3  foreign key(cust_id)

  4  references customers

  5  modify cust_id not null;

 

Table altered.

 

demo@ORA12C>

 

Build a materialized view using the following statement with ANSI join syntax.

 

demo@ORA12C> create materialized view sales_prod_cust_mv

  2  build immediate

  3  refresh on demand

  4  enable query rewrite

  5  as

  6  select p.prod_id,p.prod_name,

  7        c.cust_id,

  8        sum(s.quantity_sold) as total_qty,

  9        sum(s.amount_sold) as total_amt,

 10        count(*) cnt1,

 11        count(s.quantity_sold) cnt2,

 12        count(s.amount_sold) cnt3

 13  from sales s

 14  join products p

 15     on (s.prod_id = p.prod_id)

 16  join customers c

 17     on (s.cust_id = c.cust_id)

 18  group by p.prod_id,p.prod_name,c.cust_id;

 

Materialized view created.

 

demo@ORA12C> exec dbms_stats.gather_table_stats(user,'sales_prod_cust_mv');

 

PL/SQL procedure successfully completed.

 

 

Now, run several queries, all of them written with ANSI join syntax. The first query looks very similar to definition of materialized view, but doesn’t contain all attributes – in this case query rewrite with partial text match should be possible.

 

 

demo@ORA12C> set autotrace traceonly explain

demo@ORA12C> select p.prod_id,p.prod_name,

  2        c.cust_id,

  3        sum(s.quantity_sold) as total_qty,

  4        sum(s.amount_sold) as total_amt

  5  from sales s

  6  join products p

  7     on (s.prod_id = p.prod_id)

  8  join customers c

  9     on (s.cust_id = c.cust_id)

 10  group by p.prod_id,p.prod_name,c.cust_id;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2068648405

 

-------------------------------------------------------------------------------

| Id  | Operation                    | Name               | Rows  |Cost (%CPU)|

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                    | 10000 |   19  (22)|

|   1 |  MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_CUST_MV | 10000 |   19  (22)|

-------------------------------------------------------------------------------

 

The second query contains the additional attribute of the product dimension, in this case, a general query rewrite with a join back on dimension table should occur – unfortunately that is not the case, the explain plan shows that Materialized view is not used for this query.

 

demo@ORA12C> select p.prod_id,

  2      p.prod_name,

  3      p.prod_category,

  4      sum(s.quantity_sold) as total_qty

  5  from sales s

  6  join products p

  7     on (s.prod_id = p.prod_id)

  8  join customers c

  9     on (s.cust_id = c.cust_id)

 10  group by p.prod_id,

 11             p.prod_name,

 12             p.prod_category ;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2414848021

 

--------------------------------------------------------------------------

| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          | 10000 |  4117  (45)| 00:00:01 |

|   1 |  HASH GROUP BY        |          | 10000 |  4117  (45)| 00:00:01 |

|*  2 |   HASH JOIN           |          | 10000 |  3942  (47)| 00:00:01 |

|   3 |    VIEW               | VW_GBC_5 | 10000 |  3712  (48)| 00:00:01 |

|   4 |     HASH GROUP BY     |          | 10000 |  3712  (48)| 00:00:01 |

|   5 |      TABLE ACCESS FULL| SALES    |  1000K|  1083  (34)| 00:00:01 |

|   6 |    TABLE ACCESS FULL  | PRODUCTS | 72370 |   216  (15)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("ITEM_1"="P"."PROD_ID")

 

 

The third query joins only one fact and one dimension table, but not the CUSTOMERS table. In this case a materialized view delta join should be performed. Because of lossless join to CUSTOMERS dimension, it is possible for a query rewrite, even if that dimension table is not part of that query.

 

But the execution plan shows something else, again the materialized view is not getting used.

 

demo@ORA12C> select p.prod_name,

  2     sum(s.quantity_sold)

  3  from products p

  4  join sales s

  5  on ( s.prod_id = p.prod_id )

  6  group by p.prod_name;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2414848021

 

--------------------------------------------------------------------------

| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          | 10000 |  4093  (45)| 00:00:01 |

|   1 |  HASH GROUP BY        |          | 10000 |  4093  (45)| 00:00:01 |

|*  2 |   HASH JOIN           |          | 10000 |  3940  (47)| 00:00:01 |

|   3 |    VIEW               | VW_GBC_5 | 10000 |  3712  (48)| 00:00:01 |

|   4 |     HASH GROUP BY     |          | 10000 |  3712  (48)| 00:00:01 |

|   5 |      TABLE ACCESS FULL| SALES    |  1000K|  1083  (34)| 00:00:01 |

|   6 |    TABLE ACCESS FULL  | PRODUCTS | 72370 |   213  (14)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("ITEM_1"="P"."PROD_ID")  

 

 

Now let’s drop and rebuild the materialized view with Oracle join syntax.

 

demo@ORA12C> drop materialized view sales_prod_cust_mv;

 

Materialized view dropped.

 

demo@ORA12C> create materialized view sales_prod_cust_mv

  2  build immediate

  3  refresh on demand

  4  enable query rewrite

  5  as

  6  select p.prod_id,p.prod_name,

  7        c.cust_id,

  8        sum(s.quantity_sold) as total_qty,

  9        sum(s.amount_sold) as total_amt,

 10        count(*) cnt1,

 11        count(s.quantity_sold) cnt2,

 12        count(s.amount_sold) cnt3

 13  from sales s , products p ,customers c

 14  where s.prod_id = p.prod_id

 15  and s.cust_id = c.cust_id

 16  group by p.prod_id,p.prod_name,c.cust_id;

 

Materialized view created.

 

demo@ORA12C> exec dbms_stats.gather_table_stats(user,'sales_prod_cust_mv');

 

PL/SQL procedure successfully completed.

 

demo@ORA12C>

 

 

Now execute the queries again (both with ANSI and Oracle join syntax). All queries were rewritten as expected.

 

demo@ORA12C> set autotrace traceonly explain

demo@ORA12C> select p.prod_id,p.prod_name,

  2        c.cust_id,

  3        sum(s.quantity_sold) as total_qty,

  4        sum(s.amount_sold) as total_amt

  5  from sales s

  6  join products p

  7     on (s.prod_id = p.prod_id)

  8  join customers c

  9     on (s.cust_id = c.cust_id)

 10  group by p.prod_id,p.prod_name,c.cust_id;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2068648405

 

--------------------------------------------------------------------------------

| Id  | Operation                    | Name               | Rows  | Cost (%CPU)|

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                    | 10000 |    19  (22)|

|   1 |  MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_CUST_MV | 10000 |    19  (22)|

--------------------------------------------------------------------------------

 

demo@ORA12C> select p.prod_id,p.prod_name,

  2        c.cust_id,

  3        sum(s.quantity_sold) as total_qty,

  4        sum(s.amount_sold) as total_amt

  5  from sales s , products p , customers c

  6  where s.cust_id = c.cust_id

  7  and s.prod_id = p.prod_id

  8  group by p.prod_id,p.prod_name,c.cust_id;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2068648405

 

--------------------------------------------------------------------------------

| Id  | Operation                    | Name               | Rows  | Cost (%CPU)|

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                    | 10000 |    19  (22)|

|   1 |  MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_CUST_MV | 10000 |    19  (22)|

--------------------------------------------------------------------------------

 

demo@ORA12C> select p.prod_id,

  2      p.prod_name,

  3      p.prod_category,

  4      sum(s.quantity_sold) as total_qty

  5  from sales s

  6  join products p

  7     on (s.prod_id = p.prod_id)

  8  join customers c

  9     on (s.cust_id = c.cust_id)

 10  group by p.prod_id,

 11             p.prod_name,

 12             p.prod_category ;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2235290080

 

----------------------------------------------------------------------------------

| Id  | Operation                      | Name               | Rows  | Cost (%CPU)|

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                    | 10000 |   402  (16)|

|   1 |  HASH GROUP BY                 |                    | 10000 |   402  (16)|

|*  2 |   HASH JOIN                    |                    | 10000 |   249  (21)|

|   3 |    MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_CUST_MV | 10000 |    19  (22)|

|   4 |    TABLE ACCESS FULL           | PRODUCTS           | 72370 |   216  (15)|

----------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("P"."PROD_ID"="SALES_PROD_CUST_MV"."PROD_ID")

 

Note

-----

   - this is an adaptive plan

 

demo@ORA12C> select p.prod_id,

  2      p.prod_name,

  3      p.prod_category,

  4      sum(s.quantity_sold) as total_qty

  5  from sales s, products p, customers c

  6  where s.prod_id = p.prod_id

  7  and s.cust_id = c.cust_id

  8  group by p.prod_id,

  9             p.prod_name,

 10             p.prod_category ;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2235290080

 

----------------------------------------------------------------------------------

| Id  | Operation                      | Name               | Rows  | Cost (%CPU)|

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                    | 10000 |   402  (16)|

|   1 |  HASH GROUP BY                 |                    | 10000 |   402  (16)|

|*  2 |   HASH JOIN                    |                    | 10000 |   249  (21)|

|   3 |    MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_CUST_MV | 10000 |    19  (22)|

|   4 |    TABLE ACCESS FULL           | PRODUCTS           | 72370 |   216  (15)|

----------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("P"."PROD_ID"="SALES_PROD_CUST_MV"."PROD_ID")

 

Note

-----

   - this is an adaptive plan

 

demo@ORA12C> select p.prod_name,

  2     sum(s.quantity_sold)

  3  from products p

  4  join sales s

  5  on ( s.prod_id = p.prod_id )

  6  group by p.prod_name;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 370084824

 

---------------------------------------------------------------------------------

| Id  | Operation                     | Name               | Rows  | Cost (%CPU)|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |                    |  7976 |    90  (18)|

|   1 |  HASH GROUP BY                |                    |  7976 |    90  (18)|

|   2 |   MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_CUST_MV | 10000 |    19  (22)|

---------------------------------------------------------------------------------

 

demo@ORA12C> select p.prod_name,

  2     sum(s.quantity_sold)

  3  from products p , sales s

  4  where s.prod_id = p.prod_id

  5  group by p.prod_name;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 370084824

 

---------------------------------------------------------------------------------

| Id  | Operation                     | Name               | Rows  | Cost (%CPU)|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |                    |  7976 |    90  (18)|

|   1 |  HASH GROUP BY                |                    |  7976 |    90  (18)|

|   2 |   MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_CUST_MV | 10000 |    19  (22)|

---------------------------------------------------------------------------------

 

demo@ORA12C> set autotrace off

demo@ORA12C>

 

As you can see in this example, the join syntax used in the Materialized view definition has an impact on the capabilities of query rewrite.

 

If materialized view is written with ANSI join syntax, only full and partial text matching is possible, but all the advanced query rewrite mechanism do not work.

 

If materialized view is written with Oracle join syntax, then all types of query rewrites works as expected.

 

So if materialized view is designed for Query rewrite purpose, stick with Oracle join syntax only rather than ANSI join syntax.