由於物化檢視定義為on commit導致update更新基錶慢的解決方案

由於物化檢視定義為on commit導致update更新基錶慢的解決方案
以下是模擬和解決測試過程：


(模擬update慢的過程)
1、首先基於基表建立物化檢視日誌:
create materialized view log on scott.emp with rowid;


2、首先基於scott使用者下emp建立物化檢視：


create materialized view mv_emp


REFRESH FAST on commit


as


select * from scott.emp;


3、通過oracle 10046 檢視update語句執行過程:
(1)sql>alter session set sql_trace=true;
(2)sql>alter session set tracefile_identifier='lzq';
(3)sql>alter session set events '10046 trace name context forever, level 1';
(4)sql>update scott.emp set sal=1450 where empno=7934;
(5)sql>alter session set events '10046 trace name context off';
(6)sql>show parameter user_dump_dest
(7)cd 到user_dump_dest檢視trace檔案被標識為lzq的trace 檔案.
(8)格式化trace檔案方便檢視，tkprof prod2_ora_8623_lzq.trc prod2_ora_8623_lzq.out
(9)檢視生成的prod2_ora_8623_lzq.out檔案並查詢跟物化檢視mv_emp執行相關的資訊見如下：
(生產環境中update一條語句真正執行的時間為1分多，而其中此處merger into "SCOTT"."MV_EMP" 佔了1分左右
，而物化檢視真正是給日報、月報、年報來用的跟客戶溝通不用更新基表的時候就立即更新物化檢視，物化檢視
可以拋棄on commit時候就重新整理，可以採取定時更新即可，從而可以避免update更新基錶慢的問題。)


MERGE INTO "SCOTT"."MV_EMP" "SNA$" USING (SELECT CURRENT$."EMPNO",
  CURRENT$."ENAME",CURRENT$."JOB",CURRENT$."MGR",CURRENT$."HIREDATE",
  CURRENT$."SAL",CURRENT$."COMM",CURRENT$."DEPTNO" FROM (SELECT "EMP"."EMPNO" 
  "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR",
  "EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM",
  "EMP"."DEPTNO" "DEPTNO" FROM "EMP" "EMP") CURRENT$, (SELECT DISTINCT 
  MLOG$."EMPNO" FROM "SCOTT"."MLOG$_EMP" MLOG$ WHERE "XID$$" = :1      AND 
  ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."EMPNO" = LOG$."EMPNO")"AV$" ON 
  ("SNA$"."EMPNO" = "AV$"."EMPNO") WHEN MATCHED THEN UPDATE  SET 
  "SNA$"."EMPNO" = "AV$"."EMPNO","SNA$"."ENAME" = "AV$"."ENAME","SNA$"."JOB" =
   "AV$"."JOB","SNA$"."MGR" = "AV$"."MGR","SNA$"."HIREDATE" = 
  "AV$"."HIREDATE","SNA$"."SAL" = "AV$"."SAL","SNA$"."COMM" = "AV$"."COMM",
  "SNA$"."DEPTNO" = "AV$"."DEPTNO" WHEN NOT MATCHED THEN INSERT  
  (SNA$."EMPNO",SNA$."ENAME",SNA$."JOB",SNA$."MGR",SNA$."HIREDATE",SNA$."SAL",
  SNA$."COMM",SNA$."DEPTNO") VALUES (AV$."EMPNO",AV$."ENAME",AV$."JOB",
  AV$."MGR",AV$."HIREDATE",AV$."SAL",AV$."COMM",AV$."DEPTNO")
   


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.08          2         20          5           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.08          2         20          5           1


(模擬update慢的解決方案：)
1、首先刪除之前建立的物化檢視(先做個備份)
drop  MATERIALIZED VIEW mv_emp;


2、建立物化檢視
CREATE MATERIALIZED VIEW mv_emp 
  AS SELECT * FROM scott.emp;


3、建立一個用來重新整理物化檢視的儲存過程:


CREATE OR REPLACE PROCEDURE auto_refresh_mview_job_proc 
AS 
BEGIN 
  dbms_mview.REFRESH('mv_emp'); 
END; 


4、用ORACLE 10g的scheduler每天12:00和19:00定期重新整理物化檢視(時間可以根據需求定義)


BEGIN 
DBMS_SCHEDULER.CREATE_JOB( 
job_name => 'auot_refresh_mview_job', 
job_type => 'STORED_PROCEDURE', 
job_action => 'scott.auto_refresh_mview_job_proc', 
start_date => SYSDATE, 
repeat_interval => 'FREQ=DAILY; BYHOUR=12,19', 
enabled => TRUE, 
comments => 'Refresh materialized view mv_emp' 
); 
END; 


5、通過oracle 10046 檢視update語句執行過程:
(1)sql>alter session set sql_trace=true;
(2)sql>alter session set tracefile_identifier='lzq';
(3)sql>alter session set events '10046 trace name context forever, level 1';
(4)sql>update scott.emp set sal=1450 where empno=7934;
(5)sql>alter session set events '10046 trace name context off';
(6)sql>show parameter user_dump_dest
(7)cd 到user_dump_dest檢視trace檔案被標識為lzq的trace 檔案.
(8)格式化trace檔案方便檢視，tkprof prod2_ora_8623_lzq.trc prod2_ora_8623_lzq.out
(9)檢視生成的prod2_ora_8623_lzq.out檔案並查詢報告中是否還存在MERGE INTO "SCOTT"."MV_EMP"更新物化檢視的資訊，
此時因為已經定時為定時重新整理，從而可以提高update語句的時間，從而優化過程完成。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29429468/viewspace-1067873/，如需轉載，請註明出處，否則將追究法律責任。