由于物化视图定义为on commit导致update更新基表慢的解决方案(二)
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语句的时间，从而优化过程完成。