This discussion is archived
11 Replies Latest reply: Jun 25, 2010 8:11 AM by 635471 RSS

FAST REFRESH ON COMMIT is too slow

user13148231 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points