[Oracle][Performance]善用Materialized View提高查詢效能#4 測試
這篇小弟將簡單測試MV兩個應用方向:提高查詢效能和資料複寫、同步
利用MV提高查詢效能測試
| Total count | Explan cost | Explan cost(MV) |
| 19309372 | 227284 | 21438 |
原本查詢計畫(Full table scan)
利用MV來優化SQL statement
1.先查詢sql statement 是否有query rewrite的限制
begin
dbms_mview.explain_mview("CREATE MATERIALIZED VIEW .....");
commit;
end;
/
2. 建立materialized view log
Create materialized view log on yourTable with rowid;
3.建立materialized view(建議獨力Tablespace)
CREATE MATERIALIZED VIEW .....;
測試同樣SQL查詢(優化器 query rewrite MV，cost大幅降低查詢時間少了快200s)
結論:針對以上實做測試，對於常用的大查詢(或複雜的sql)
可以個別建立MV然後再結合相關MV來提高查詢反應效率，雖然過程較麻煩也較浪費空間
但還是有一定的效率提升。在建立materialized view也應該避開DB忙碌時段
以免IO和CPU使用過大導致影響DB線上效能。
資料複製、同步測試
| Materialized (rowid ) | 優缺點 |
| Refresh fast on commit | 優:MV資料保持最新,oracle首選 缺:commit需花費較多時間在維護MVL |
| Refresh fast on demand | 優:使用排成refresh MV以不影響線上DB效能。 缺:資料無法即時同步(需人工介入) |
Refresh complte on commit | 不建議使用,浪費空間及系統資源 |
On Demand模式
1. Source db建立materialized view log
2.target db建立materialized view
create materialized view ....
確認source db和target db筆數應該相同
row1:使用oracle db link查詢source db table count
row2:查詢target db MV count
刪除source db table 10筆資料
這時source db和target db 資料相差10筆
手動refresh materialized(<1 sec)
begin
dbms_mview.refresh('mv_synpodt', 'fast');
end;
/
再度查詢target db MV以和source db table資料已同步成功
On commit 模式
1.Create materialized view log
2.create materialized view
確認table和MV資料筆數
Delete 測試
MV自動同步資料(<1s)
當然相關的DML操作都可以達到on commit自動同步資料(同ODI CDC功能)
結論:雖然前置步驟繁多，但對於重要table的備份或更新工作來看
後期資料管理同步上卻省了不少麻煩，相對的on commit也需要浪費較多的時間來維護MVL的記錄
之間需取得一定的平衡。
就這樣小弟體驗oracle11g MV也告一段落了，自己覺得如果使用企業級資料庫但不使用企業級DB技術
如同小孩開跑車，無法發揮跑車應有性能，所以到底是資料庫軟體設計太差
還是自己本身資料庫知識及技術不足，值得好好想想。