5

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 Dwadmin.f_S_Change@DateWarehouse.World Fcr
 INNER JOIN Dwadmin.d_S_Change@DateWarehouse.World Cr
    ON Fcr.w_Sol_Chng_Id = Cr.w_Sol_Chng_Id
 INNER JOIN Dwadmin.b_S_Change_Obl@DateWarehouse.World Bsr
    ON Fcr.w_Sol_Chng_Id = Bsr.w_Sol_Chng_Id
 INNER JOIN Dwadmin.d_S_Rec@DateWarehouse.World 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

Yili Li
  • 131
  • 1
  • 1
  • 4
  • You should include your MV definition if possible. – WoMo Feb 20 '13 at 16:21
  • I have upload my code... Thanks – Yili Li Feb 20 '13 at 16:25
  • 1
    When you refresh on demand, you might consider passing in `atomic_refresh => false` as a parameter to force the table to be truncated and then populated. See this [great answer](http://stackoverflow.com/questions/6349274/oracle-what-happens-when-refreshing-a-refresh-force-on-demand-view-with-dbms). – WoMo Feb 20 '13 at 16:46
  • It makes sense.. I am trying this command... it is stilling running right now (6mins).. Original command took 160 mins... I will keep updated on the performance gain from this command... thanks so much.. – Yili Li Feb 20 '13 at 17:22
  • It seems it was not the root cause of my issue... it is still running.... over 40 mins now – Yili Li Feb 20 '13 at 17:58
  • it's important to state that when using `atomic_refresh => false` you wont be able to rollback ! – haki Feb 21 '13 at 17:12

4 Answers4

4

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.

tbone
  • 15,107
  • 3
  • 33
  • 40
  • 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 – Yili Li Feb 20 '13 at 21:00
  • "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 – tbone Feb 20 '13 at 22:04
  • 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 – Yili Li Feb 20 '13 at 22:19
1

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

Anonymous
  • 152
  • 1
  • 1
  • 11
0

if it only takes 20-30 seconds to create why not just drop and recreate the materialized view instead of refreshing it?

northpole
  • 10,244
  • 7
  • 35
  • 58
  • 1
    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 Feb 20 '13 at 15:49
0

I am guessing:

Create Table doesn't need to write into the transaction log, as it is a new table. atomic_refresh => false means there is a truncate on the delete side (bypassing logging), but you then still have the INSERT side to deal with, which likely means you get a lot of transaction logging.

smackenzie
  • 2,880
  • 7
  • 46
  • 99