11 Replies Latest reply: Jun 25, 2010 10:11 AM by 635471 RSS

    FAST REFRESH ON COMMIT is too slow

    user13148231
      I created a materialized view on Oracle 11g2 as below
      CREATE MATERIALIZED VIEW LOG ON arrest_x_offense WITH ROWID;
      ... created MATERIALIZED VIEW LOG  on every master table
       
      CREATE MATERIALIZED VIEW mv_person_arrest
      PARALLEL BUILD IMMEDIATE REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS
      select prs_sid,prs_name_last, person_id,  a.arrest_id,a.arr_atn,o.offense_id,off_modifier,
        p.rowid p_rowid,x.rowid x_rowid,a.rowid a_rowid,xo.rowid xo_rowid,o.rowid o_rowid
        from mv_person p,JPERSON_X_ARREST x,arrest a,arrest_x_offense xo,offense o
        where p.person_id=x.jperson_id and  a.arrest_id=x.arrest_id and a.arrest_id=xo.arrest_id and xo.offense_id=o.offense_id;
      I then made a few insert and commit the transaction
      insert into offense (offense_id,off_description) select '02_off_test','02_Test MV' from dual;
      insert into arrest_x_offense (offense_id,arrest_id) select '02_off_test','02_arr_id_test' from dual;
      insert into arrest (arrest_id,arr_atn) select '02_arr_id_test','02_Test MV' from dual;
      insert into JPERSON_X_ARREST (jperson_id,arrest_id) select '503ba0d527dbcbb70127e2cc2407731f','02_arr_id_test' from dual;
      The inserts take not time to complete, but the commit take about 5 minutes. I checked the database during commiting, and found that it was doing things like
      /* MV_REFRESH (INS) */ INSERT INTO "QAPF2"."MV_PERSON_ARREST" SELECT /*+ NO_MERGE("JV$") */ "MAS$4"."PRS_SID","MAS$4"."PRS_NAME_FIRST","MAS$4"."PRS_NAME_MIDDLE","MAS$4"."PRS_NAME_LAST","MAS$4"."PRS_NAME_SUFFIX","MAS$4"."***","MAS$4"."RACE","MAS$4"."PRS_BIRTH_DATE","MAS$4"."PRS_HEIGHT","MAS$4"."PRS_WEIGHT","MAS$4"."PRS_HAIR_COLOR","MAS$4"."PRS_EYE_COLOR","MAS$4"."PRS_BIRTH_PLACE","MAS$4"."PRS_FBI_ID","MAS$4"."PRS_SSN","MAS$4"."PRS_DR_LIC_NUM","MAS$4"."PRS_DR_LIC_STA","MAS$4"."PRS_DOC_ID","MAS$4"."PRS_III","MAS$4"."PERSON_ID","JV$"."ARREST_ID","JV$"."ARR_ATN","JV$"."ARR_DATE","MAS$0"."OFFENSE_ID","MAS$0"."OFF_MODIFIER","MAS$0"."OFF_STATUTE","MAS$0"."OFF_COUNTS","MAS$0"."OFF_SEVERITY","MAS$0"."OFF_DESCRIPTION","MAS$0"."OFF_EXPUNGED","MAS$0"."OFF_SEQ_NUM","MAS$4".ROWID,"MAS$3".ROWID,"JV$"."RID$","MAS$1".ROWID,"MAS$0".ROWID FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM "QAPF2"."ARREST" "MAS$" WHERE ROWID IN (SELECT  /*+ HASH_SJ */  CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM "QAPF2"."MLOG$_ARREST" "MAS$"   WHERE "MAS$".XID$$ = :1 )) "JV$", "OFFENSE" "MAS$0", "ARREST_X_OFFENSE" "MAS$1", "JPERSON_X_ARREST" "MAS$3", "MV_PERSON" AS OF SNAPSHOT(:B_SCN)  "MAS$4" WHERE "MAS$4"."PERSON_ID"="MAS$3"."JPERSON_ID" AND "JV$"."ARREST_ID"="MAS$3"."ARREST_ID" AND "JV$"."ARREST_ID"="MAS$1"."ARREST_ID" AND "MAS$1"."OFFENSE_ID"="MAS$0"."OFFENSE_ID"
      /* MV_REFRESH (DEL) */ DELETE FROM "QAPF2"."MV_PERSON_ARREST" SNA$ WHERE "A_ROWID" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ * FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM "QAPF2"."MLOG$_ARREST" "MAS$"   WHERE "MAS$".XID$$ = :1 )MAS$)
      What can one do to improve it?
        • 1. Re: FAST REFRESH ON COMMIT is too slow
          Taral
          Trace it and see where its taking time. And commit is not your culprit it could be delete and insert. Try using full refresh and see performance
          • 2. Re: FAST REFRESH ON COMMIT is too slow
            Jonathan Lewis
            user13148231 wrote:
            I created a materialized view on Oracle 11g2 as below
            CREATE MATERIALIZED VIEW LOG ON arrest_x_offense WITH ROWID;
            ... created MATERIALIZED VIEW LOG  on every master table
            
            CREATE MATERIALIZED VIEW mv_person_arrest
            PARALLEL BUILD IMMEDIATE REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS
            select prs_sid,prs_name_last, person_id,  a.arrest_id,a.arr_atn,o.offense_id,off_modifier,
            p.rowid p_rowid,x.rowid x_rowid,a.rowid a_rowid,xo.rowid xo_rowid,o.rowid o_rowid
            from mv_person p,JPERSON_X_ARREST x,arrest a,arrest_x_offense xo,offense o
            where p.person_id=x.jperson_id and  a.arrest_id=x.arrest_id and a.arrest_id=xo.arrest_id and xo.offense_id=o.offense_id;
            I then made a few insert and commit the transaction
            The inserts take not time to complete, but the commit take about 5 minutes. I checked the database during commiting, and found that it was doing things like
            Refresh on commit has to do a lot of things on commit - the code has been enhanced in 11g, but it still has to find the rows that need to be deleted or updated, and it has to use and clear the various MV logs.

            As advised by Taral, trace the process from the moment you do the commit - you will probably find that the internal code is running very inefficiently, use standard optimisation methods to make it go faster; one of the obvious examples is to check what indexes exist on the MV that allow Oracle to find the rows that need to be deleted or updated.

            Regards
            Jonathan Lewis
            • 3. Re: FAST REFRESH ON COMMIT is too slow
              Jonathan Lewis
              user13148231 wrote:
              I created a materialized view on Oracle 11g2 as below
              CREATE MATERIALIZED VIEW LOG ON arrest_x_offense WITH ROWID;
              ... created MATERIALIZED VIEW LOG  on every master table
              
              CREATE MATERIALIZED VIEW mv_person_arrest
              PARALLEL BUILD IMMEDIATE REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS
              select prs_sid,prs_name_last, person_id,  a.arrest_id,a.arr_atn,o.offense_id,off_modifier,
              p.rowid p_rowid,x.rowid x_rowid,a.rowid a_rowid,xo.rowid xo_rowid,o.rowid o_rowid
              from mv_person p,JPERSON_X_ARREST x,arrest a,arrest_x_offense xo,offense o
              where p.person_id=x.jperson_id and  a.arrest_id=x.arrest_id and a.arrest_id=xo.arrest_id and xo.offense_id=o.offense_id;
              I then made a few insert and commit the transaction
              The inserts take not time to complete, but the commit take about 5 minutes. I checked the database during commiting, and found that it was doing things like
              Refresh on commit has to do a lot of things on commit - the code has been enhanced in 11g, but it still has to find the rows that need to be deleted or updated, and it has to use and clear the various MV logs.

              As advised by Taral, trace the process from the moment you do the commit - you will probably find that the internal code is running very inefficiently, use standard optimisation methods to make it go faster; one of the obvious examples is to check what indexes exist on the MV that allow Oracle to find the rows that need to be deleted or updated.

              Regards
              Jonathan Lewis
              • 4. Re: FAST REFRESH ON COMMIT is too slow
                user13148231
                Thank you for reply to the post. I tried below to set trace
                13:49:08 SQL> update offense set off_description='02_Test_updated' where offense_id='02_off_test';
                
                1 row updated.
                13:49:20 SQL> set autotrace on
                13:49:42 SQL> commit;
                
                Commit complete.
                
                13:51:25 SQL> 
                It does not show the plan for commit. Do I need to set up trace different way?
                Also do I need to modify the materialized view log to improve performance? Say add some columns in the log?
                • 5. Re: FAST REFRESH ON COMMIT is too slow
                  660830
                  Also do I need to modify the materialized view log to improve performance? Say add some columns in the log?

                  No! You can not and not need to do that!

                  And what Jonathan was saying is that you need to trace the whole session for example setting up a trace+tkprof.

                  Bye,
                  Antonio
                  • 6. Re: FAST REFRESH ON COMMIT is too slow
                    user13148231
                    Thanks. Tried set sql_trace with SET_SQL_TRACE, get error
                    15:02:35 SQL> exec DBMS_SESSION.SET_SQL_TRACE (true);
                    BEGIN DBMS_SESSION.SET_SQL_TRACE (true); END;
                    
                    *
                    ERROR at line 1:
                    ORA-01031: insufficient privileges
                    ORA-06512: at "SYS.DBMS_SESSION", line 149
                    ORA-06512: at line 1
                    I have granted object privilege EXECUTE SYS.DBMS_SESSION to the curernt user, bt still got this error.
                    Can you help?
                    • 7. Re: FAST REFRESH ON COMMIT is too slow
                      660830
                      The problem is that the user is lacking some privileges, but not necessarily the execute on the package.

                      So, first of all, revoke the grant that you give to the user.

                      Then try to have a look here:

                      http://www.orafaq.com/wiki/SQL_Trace

                      so first try using ALTER SESSION SET sql_trace = true (and false to stop it); and see what happen.

                      The link above tells you how to name the trace file so you can find it on your Oracle server.

                      Hope this help.

                      Bye,
                      Antonio
                      • 8. Re: FAST REFRESH ON COMMIT is too slow
                        user13148231
                        this is helpful. I am almost there, but got error (with 11g2)
                        ALTER SYSTEM SET timed_statistics = true;
                        execute sys.dbms_system.set_sql_trace_in_session(378,398, true);
                        ============================
                        Error starting at line 35 in command:
                        execute sys.dbms_system.set_sql_trace_in_session(378,398, true)
                        Error report:
                        ORA-06550: line 1, column 7:
                        PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared
                        ORA-06550: line 1, column 7:
                        PL/SQL: Statement ignored
                        06550. 00000 -  "line %s, column %s:\n%s"
                        *Cause:    Usually a PL/SQL compilation error.
                        *Action:
                        I checked in DBA_OBJECTS, there is DBMS_SYSTEM, but no SET_SQL_TRACE_IN_SESSION. I checked 11g2 manual e10577, there is no DBMS_SYSTEM. Do you know is this procedure supported in 11g2.
                        • 9. Re: FAST REFRESH ON COMMIT is too slow
                          user13148231
                          It is said that Oracle does not support DBMS_SYSTEM. I used
                          exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'SYS$USERS', module_name=>'SQL*Plus'); 
                          to set up trace and it works.

                          Thanks
                          • 10. Re: FAST REFRESH ON COMMIT is too slow
                            Ospino
                            Hi user13148231,

                            Do you really need "ON COMMIT"?

                            Like Mr. Lewis said, there are a lot of SQL statement after COMMIT. For example, with one MV with three tables in SELECT statement, after COMMIT trace show me three INSERTs, two DELETEs and 10 SELECTs (do not make any relations with the number of tables and the number of statements).
                            Every COMMIT, you generate a lot of activity.
                            You could try to use DBMS_MVIEW, and REFRESH you MVIEWs "ON DEMAND".

                            This is not part of the real problem but, Does you FOREING KEY have indexs?

                            In any case, like everybody said:
                            - grant alter session to <user>
                            - connect <user>
                            - alter session set events '10046 trace name context forever, level 12' <-- is up to you the level
                            - inserts and commit
                            - alter session set events '10046 trace name context off'

                            and you can use tkprof, or go to the next bookstore buy the Cary Millsap's book and learn how to read trace files in raw or you could use this metalink note 39817.1, and find out wich operation is making thing goes bad.

                            John Ospino Rivas
                            • 11. Re: FAST REFRESH ON COMMIT is too slow
                              635471
                              user13148231 wrote:
                              I created a materialized view on Oracle 11g2 as below
                              CREATE MATERIALIZED VIEW LOG ON arrest_x_offense WITH ROWID;
                              ... created MATERIALIZED VIEW LOG  on every master table
                              
                              CREATE MATERIALIZED VIEW mv_person_arrest
                              PARALLEL BUILD IMMEDIATE REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS
                              select prs_sid,prs_name_last, person_id,  a.arrest_id,a.arr_atn,o.offense_id,off_modifier,
                              p.rowid p_rowid,x.rowid x_rowid,a.rowid a_rowid,xo.rowid xo_rowid,o.rowid o_rowid
                              from mv_person p,JPERSON_X_ARREST x,arrest a,arrest_x_offense xo,offense o
                              where p.person_id=x.jperson_id and  a.arrest_id=x.arrest_id and a.arrest_id=xo.arrest_id and xo.offense_id=o.offense_id;
                              I always look at materialised view definitions like this, with all of those joins and refresh fast on commit, with a bit of cynicism. If you think about all of the work that has to be done to maintain the MV whenever you insert or update or delete any of the base tables it can get so complex that a refresh performance problem is unsurprising.

                              For example when you perform an update on a particular column, at that point the system probably has no idea whether there are even any rows in the MV to be modified. Likewise when you delete or insert a row.

                              Let's say that you insert a row into each of the five base tables such that a single row will be inserted into the materialised view -- the row you insert into ARREST could join to a number of rows in JPERSON_X_ARREST and ARREST_X_OFFENSE as well as the one you just inserted, so you have a very complex set of queries against the base tables and the MV logs in order to make sure that the MV is accurately maintained.

                              I would guess that you have this MV in order to optimise a particular query. I'd suggest that you look at optimising that query without the use of this MV, whether it's a small query that ought to be using indexes and nested loops, or a large query that should be performing full table scans and hash joins.

                              By the way, if it's the latter and your performance problem is related to the size of the segments being scanned AND there are a lot of columns on those tables then you might help by just maintaining one MV per table with just the columns required, allowing the report query to scan smaller segments.