Solution to slow update of base table because materialized views are defined as on commit

Solution to slow update of base table because materialized views are defined as on commit. The solution to slow update of base table because materialized views are defined as on commit is as follows: 1. create a materialized view log based on the base table: create materialized view log on scott. emp with rowid; 2. create a materialized view based on emp under scott: create materialized view mv_emp refresh fast on commit as select * from scott. emp; 3. view the update statement execution process through oracle 10046: (1) SQL> alter session set SQL _trace = true; (2) SQL> alter session set tracefile_identifier = 'lz Q'; (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 to user_dump_dest to view trace files identified as lzq. (8) format the trace file for easy viewing. Check the generated prod2_ora_8623_lzq.trc prod2_ora_8623_lzq.out (9) file and find the file related to the execution of the materialized view mv_emp. For details, see the following figure: (the actual execution time of an update statement in the production environment is more than one minute, and here merger into "SCOTT ". "MV_EMP" accounts for about 1 point, while the materialized view is used to communicate with the customer in daily, monthly, and annual reports. The materialized view is updated immediately without updating the base table, materialized views can be refreshed when on commit is discarded. Regular updates can be used to avoid the slow update of base tables .) 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 $, (SE Lect 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 2 20 5 1 Fetch 0 0.08 0 0 0 0 0 0 ------- ------ -------- ---------- total 2 0.01 0.08 2 20 5 1 (simulate slow update solution :) 1. First, delete the materialized view created earlier (create a backup first) drop materialized view mv_emp; 2. create materialized view mv_emp as select * FROM scott. emp; 3. CREATE a stored PROCEDURE to refresh the materialized view: create or replace procedure auto_refresh_mview_job_proc as begin dbms_mview.REFRESH ('mv _ emp'); END; 4. Use ORACLE's 10 Gb scheduler at and 19 every day: 00 regularly refresh the materialized view (time can be defined as needed) BEGIN DBMS_SCHEDULER.CREATE_JOB (job_name => 'auot _ refresh_mview_job ', job_type => 'stored _ PROCEDURE', job_action => 'Scott. comment ', start_date => SYSDATE, repeat_interval => 'freq = DAILY; BYHOUR = 12,19', enabled => TRUE, comments => 'refreshmaterialized view mv_emp'); END; 5. view the update statement execution process in oracle 10046: (1) SQL> alter session set SQL _trace = true; (2) SQL> al Ter 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 to user_dump_dest to view trace files identified as lzq. (8) format the trace file for easy viewing. Check the generated pro in tkprof prod2_ora_8623_lzq.trc prod2_ora_8623_lzq.out (9 ). D2_ora_8623_lzq.out file and check whether merge into "SCOTT" still exists in the report ". "MV_EMP" updates the information of the materialized view. At this time, the update statement time can be improved because it has been regularly refreshed, and the optimization process is completed.