创建物化视图commit |
您所在的位置:网站首页 › 物化视图创建很慢 › 创建物化视图commit |
由于物化视图定义为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语句的时间,从而优化过程完成,由于物化视图定义为on commit导致update更新基表慢的解决方案》(https://www.unjs.com)。 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |