Slow Fast Mview Refreshes

Today i was doing a small proof of a concept with a materialized view - i don't use these that often as use cases don't come up too much. In this particular case i was doing a test of what is/isn't possible when having a fast refresh-able mview on a table join.

For my initial basic test i just set up the following example (there are no PK's here -  not my choosing - it's a 3rd party app so everything is having to use rowid).

So to set up the test i do the following

SQL> create snapshot log on ab_tran with rowid;

Materialized view log created.

SQL> create snapshot log on ab_tran_info with rowid;

Materialized view log created.

After a bit of trial and error i get the syntax right and build the mview

  create materialized view richdemo refresh fast  as
  select a.TRAN_NUM,a.DEAL_TRACKING_NUM,a.TRAN_TYPE,a.tran_status,b.TYPE_ID,b.VALUE,b.PERSONNEL_ID,a.rowid as arow,b.rowid as brow
 from ab_tran a,ab_tran_info b
 where a.tran_num=b.tran_num
 and a.tran_status in (2,3)

Materialized view created.

All good so far - now lets check the fast bit works ok......

update ab_tran set tran_status=tran_status
where tran_status=2
and rownum <2;

1 row updated.

Elapsed: 00:00:00.28
SQL> commit;

Commit complete.

Elapsed: 00:00:00.32

SQL> exec dbms_mview.refresh('RICHDEMO');

PL/SQL procedure successfully completed.

Elapsed: 00:00:26.10

Right...... that's not really so fast is it. Not looking good. Are join mviews just generally a bit slow - whats going on here?

Lets do a trace and find out whats going on

Alter session set sql_trace=TRUE;

 (yes i know i need to drag myself out of the 90's - but its easy to remember......)

Looking at the trace files i see 2 things i need to address

Mostly this

INSERT INTO "DAILY"."RICHDEMO" SELECT /*+ NO_MERGE("JV$") */ "JV$"."TRAN_NUM",
  "JV$"."DEAL_TRACKING_NUM","JV$"."TRAN_TYPE","JV$"."TRAN_STATUS",
  "MAS$0"."TYPE_ID","MAS$0"."VALUE","MAS$0"."PERSONNEL_ID","JV$"."RID$",
  "MAS$0".ROWID FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM
  "DAILY"."AB_TRAN" "MAS$" WHERE ROWID IN (SELECT  /*+ HASH_SJ */
  CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM "DAILY"."MLOG$_AB_TRAN" "MAS$"
   WHERE "MAS$".SNAPTIME$$ > :B_ST1 ))  AS OF SNAPSHOT(:B_SCN) "JV$",
  "AB_TRAN_INFO" AS OF SNAPSHOT(:B_SCN)  "MAS$0" WHERE "JV$"."TRAN_NUM"=
  "MAS$0"."TRAN_NUM" AND ("JV$"."TRAN_STATUS"=2 OR "JV$"."TRAN_STATUS"=3)

And to a lesser extent this

DELETE FROM "DAILY"."RICHDEMO" SNA$

WHERE

 "AROW" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ * FROM (SELECT

  CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM "DAILY"."MLOG$_AB_TRAN" "MAS$"

   WHERE "MAS$".SNAPTIME$$ > :B_ST1 ) AS OF SNAPSHOT(:B_SCN) MAS$)

The second one is easily dealt with by this

create index richdemoifx on richdemo(arow);

Which instantly fixes it - however the first one is more of a problem - the oracle generated hints are doing it any favours here and there seemed to be no easy solution in my control.

A quick google search revealed though that this is a common issue going back years and the suggestion is to look at this parameter.

_mv_refresh_use_stats

In 11.2.0.2 where i was testing this has a value of

  1  select

  2    ksppinm,

  3    ksppstvl

  4  from

  5    x$ksppi a,

  6    x$ksppsv b

  7  where

  8    a.indx=b.indx and

  9*   ksppinm = '_mv_refresh_use_stats'

SQL> /

KSPPINM

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

KSPPSTVL

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

_mv_refresh_use_stats

FALSE

And from the name of the parameter - this implies that an mview refresh should not use stats - which seems a very odd thing for oracle to do as a default.

Lets change it and see what happens

alter session set "_mv_refresh_use_stats" = TRUE;

Then i rerun the test exact same statements as before - this time however the total elapsed time for the mview refresh is 0.02 seconds - so something has clearly changed - lets trace again and see what we find

INSERT INTO "DAILY"."RICHDEMO" SELECT /*+ NO_MERGE("JV$") */ "JV$"."TRAN_NUM",

  "JV$"."DEAL_TRACKING_NUM","JV$"."TRAN_TYPE","JV$"."TRAN_STATUS",

  "MAS$0"."TYPE_ID","MAS$0"."VALUE","MAS$0"."PERSONNEL_ID","JV$"."RID$",

  "MAS$0".ROWID FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM

  "DAILY"."AB_TRAN" "MAS$" WHERE ROWID IN (SELECT  /*+ CARDINALITY(MAS$ 100)

  NO_SEMIJOIN */  CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM

  "DAILY"."MLOG$_AB_TRAN" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :B_ST1 ))  AS OF

  SNAPSHOT(:B_SCN) "JV$", "AB_TRAN_INFO" AS OF SNAPSHOT(:B_SCN)  "MAS$0"

  WHERE "JV$"."TRAN_NUM"="MAS$0"."TRAN_NUM" AND ("JV$"."TRAN_STATUS"=2 OR

  "JV$"."TRAN_STATUS"=3)

Now we see the SQL is totally different - not just the plan - so the parameter has clearly had quite a major effect.

Not sure why this isn't the default - in our case seems we will be changing it though......