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......




1 comments:

  1. It's a mess of a feature... makes you think that it can't be that widely used but surely not?

    Also see below (from about halfway)
    https://orastory.wordpress.com/2014/11/27/the-mess-that-is-fast-refresh-join-only-materialized-views/

    ReplyDelete