materialized view creation is fast but refresh takes hours

Question

I am using a materialized view, and I cant set it to fast refresh because some of the tables are from remote database which does not have materialized view log.

When I create the materialized view, it took like 20 30 seconds. however when I was trying to refresh it. It took more than 2 3 hours. and total number of records are only around 460,000.

Does anyone have any clue about how it would happen?

Thanks

Code looks like as following

create materialized view MY_MV1
refresh force on demand
start with to_date('20-02-2013 22:00:00', 'dd-mm-yyyy hh24:mi:ss') next trunc(sysdate)+1+22/24 
as
( SELECT Nvl(Cr.Sol_Chng_Num, ' ') AS Change_Request_Nbr,
       Nvl(Sr.Sr_Num, ' ') AS Service_Request_Nbr,
       Nvl(Sr.w_Org_Id, 0) AS Org_Id,
       Fcr.rowid,
       Cr.rowid,
       Bsr.rowid,
       Sr.rowid,
       SYSDATE
  FROM [email protected] Fcr
 INNER JOIN [email protected] Cr
    ON Fcr.w_Sol_Chng_Id = Cr.w_Sol_Chng_Id
 INNER JOIN [email protected] Bsr
    ON Fcr.w_Sol_Chng_Id = Bsr.w_Sol_Chng_Id
 INNER JOIN [email protected] Sr
    ON Sr.w_Srv_Rec_Id = Bsr.w_Srv_Rec_Id
 WHERE Sr.Sr_Num <> 'NS'
);

I have tried to use dbms_mview.refresh('MY_MATVIEW', 'C', atomic_refresh=>false) but it still took 141 mins to run... vs 159 mins without atomic_refresh=>false

Solution 1

I would personally NOT use the scheduler built into the mat view CREATE statement (start with ... next clause).

The main reason (for me) is that you cannot declare the refresh non-ATOMIC this way (at least I haven't found the syntax for this at CREATE time). Depending on your refresh requirements and size, this can save A LOT of time.

I would use dbms_mview.refresh('MY_MATVIEW', 'C', atomic_refresh=>false). This would:

1. Truncate MY_MATVIEW snapshot table
2. Insert append into MY_MATVIEW table

If you use the next clause in the create statement, it will setup an atomic refresh, meaning it will:

1. Delete * from MY_MATVIEW
2. Insert into MY_MATVIEW
3. Commit

This will be slower (sometimes much slower), but others can still query from MY_MATVIEW while the refresh is occurring. So, depends on your situation and needs.

Solution 2

You can test it. I run this manually and it works for me friend :)

BEGIN
   DBMS_REFRESH.make(
   name                 => 'DB_NAME.MINUTE_REFRESH',
   list                 => '',
   next_date            => SYSDATE,
   interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
   implicit_destroy     => FALSE,
   lax                  => FALSE,
   job                  => 0,
   rollback_seg         => NULL,
   push_deferred_rpc    => TRUE,
   refresh_after_errors => TRUE,
   purge_option         => NULL,
   parallelism          => NULL,
   heap_size            => NULL);
END;
/

BEGIN
   DBMS_REFRESH.add(
   name => 'DB_NAME.MINUTE_REFRESH',
   list => 'DB_NAME.MV_NAME',
   lax  => TRUE);

END; /

And then u can destroy it with this.

BEGIN
  DBMS_REFRESH.destroy(name => 'DB_NAME.MINUTE_REFRESH');
END;
/

You can create materialize view log.

CREATE MATERIALIZED VIEW LOG ON DB_NAME.TABLE_NAME
TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;

I hope it can help you. :)

Comments

[Yili Li]

In our prod domain, we have audit email sent out while create/drop action is happening. I would like to understand why refresh takes way longer than create...

[Yili Li]

as Wolf and you suggested... I tried to run a commend with atomic_refresh=>false... it 141 mins to refresh the view Vs 159 Mins without it... especially compares to 20 30 seconds while I created the view.. it takes too long

[tbone]

"created the view" past tense, when other stuff running or not running on the box, when data was different, buffer cache different, could be many factors, not sure. If you drop/create the mat view today (using build immediate), and then refresh it complete using dbms_mview (atomic_refresh false), see what happens

[Yili Li]

yes... that was the first thing came into my mind... I have tried in different time frame started from yesterday night till this afternoon... but whenever I dropped the materialized view and recreated it, it took no more than 30 second... And when I ran the command to refresh right after, it took hours to finish