《Materialized Views 專案實作經驗》

2012-10-04

摘要:《Materialized Views 專案實作經驗》

2010/2/17

這邊講述專案實作的經驗。

狀況描述:

此專案要在一個接近4000萬筆資料、5.5 G左右的資料表，尋找特定的資料 (約莫 55萬筆資料，占1.3%資料量)，此專案會經常性存取

(尖峰時間估計一分鐘內會有30次查詢)

這些特定資料作為業務上判斷需要，如果條件範圍較大時，因為索引及其它參考資料的存取，從原始資料取得必會耗費較多資源，

在經常性存取的情形下如同雪上加霜。

評估階段:

一開始評估時，直接將SQL寫好(串了許多資料表，還有union all)建置mview，後來受限於多個資料表的關係，mview類型屬於

4.Complex Materialized Views，

此種mview 在refresh時會重新執行SQL重新建置mview的資料，撈資料過於費時且占用許多buffer cache，雖然是離峰時間作業，

也不想在半夜把資料庫弄垮吧。

原本一度要放棄 mview 的作法，改採用程式運作，定時同步資料。

但在同事的建議下，重新評估了一次，這次對主要的資料表分別作 mview，mview 類型屬於 3.ROWID Materialized Views ，

可以加上 Materialized View log，加快refresh的速度，測試結果每天同步一次約 2 分鐘即可完成，再用 view 將其它資料表串起來。

因為主要資料表每天約有數十萬筆資料，考慮到資料庫的資源耗用情形，若即時同步資料，每一筆資料在DML時，除了要維護索引，

還要維護 mview，對資料庫資源也是不小的支出，且專案也不需要到即時資料，故在專案一開始即已表明，每天僅在半夜兩點時同步一次。

DDL 指令如下:

create materialized view log on mview_test tablespace mvdat with rowid;

create materialized view mview_test_mv tablespace mvdat REFRESH fast with rowid START WITH TRUNC(SYSDATE)+1/12 NEXT  

RUNC(SYSDATE + 1)+1/12  as

select * from mview_test where F1 in ('A','C','D','Z');

實驗數據:

同樣結果下，直接存取來源資料表統計值如下:

統計值

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

3967 recursive calls

0 db block gets

11685 consistent gets

373 physical reads

0 redo size

1474 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

15 rows processed

同樣結果下，直接存取篩選過的mview資料表統計值如下:

統計值

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

181 recursive calls

0 db block gets

1245 consistent gets

85 physical reads

0 redo size

1847 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

15 rows processed

很明顯 physical reads 的數量減少許多，差異近300個data block，約莫1.2MB (範例資料庫為 db_block_size=4096)；

此為單一案例，同上所述若經常性查詢，這差異就十分顯著。

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

2012-04-11 更新

ps. 如果來源資料表欄位長度變更, 會造成 MATERIALIZED VIEW 無法更新同步

在 DBA_MVIEWS.STATUS 會出現 COMPLICATION_ERROR.

最後解決方式是重建 MVIEW

(

DROP MATERIALIZED VIEW KFSYSCC.MEDBROW_MV;

DROP MATERIALIZED VIEW LOG ON KFSYSCC.MEDBROW;

create materialized view log on kfsyscc.MEDBROW tablespace mvdat with rowid;

CREATE MATERIALIZED VIEW "KFSYSCC"."MEDBROW_MV" TABLESPACE "MVDAT" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING USING INDEX TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) REFRESH FORCE WITH ROWID ON COMMIT AS SELECT "MEDBROW"."CHART_NO" "CHART_NO","MEDBROW"."CHART_TYPE" "CHART_TYPE","MEDBROW"."VOL_NO" "VOL_NO","MEDBROW"."REASON" "REASON","MEDBROW"."LOCATION" "LOCATION","MEDBROW"."APL_EMPID" "APL_EMPID","MEDBROW"."RTN_DATE" "RTN_DATE","MEDBROW"."APL_OP_EMPID" "APL_OP_EMPID","MEDBROW"."APL_DATE" "APL_DATE","MEDBROW"."APL_TIME" "APL_TIME","MEDBROW"."OUT_EMPID" "OUT_EMPID","MEDBROW"."OUT_DATE" "OUT_DATE","MEDBROW"."OUT_TIME" "OUT_TIME","MEDBROW"."IN_EMPID" "IN_EMPID","MEDBROW"."IN_DATE" "IN_DATE","MEDBROW"."IN_TIME" "IN_TIME","MEDBROW"."SHELF_EMPID" "SHELF_EMPID","MEDBROW"."SHELF_DATE" "SHELF_DATE","MEDBROW"."SHELF_TIME" "SHELF_TIME" FROM "MEDBROW" "MEDBROW" WHERE "MEDBROW"."IN_DATE" IS NULL;

)

回首頁