• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1942
  • Last Modified:

Commits with On Commit Refresh Materialized view slow

Hi ,

I have create Materialized view with on commit refresh,i have created MV logs on the base tables of the MV with rowid and sequence options.

When i insert or update any of the base table the commit is taking around 2 mins,how can i speed up the commits?

CREATE MATERIALIZED VIEW XMATAPP.MV_CUST_SEARCH NOLOGGING PARALLEL 8 BUILD IMMEDIATE 
REFRESH FAST ON COMMIT  AS
     SELECT personprofilenames.pp_legalentity_number legal_entity_number,
          personprofilenames.first_name,
          personprofilenames.last_name,
          personprofilenames.middle_name,
          personprofile.dob,
          personprofilenames.name_type_id name_type,
          personprofile.eye_color_id,
          personprofilenames.suffix_value suffix_value,
          personprofile.gender_id gender1,
          personprofilenames.is_deleted ,
          refgender.description gender,
          refeyecolors.description eyecolor ,
          personprofilenames.rowid r1 ,
          refgender.rowid r2,
          refeyecolors.rowid r3,
          personprofile.rowid r4
         FROM 
         person_profile personprofile, 
         person_profile_names personprofilenames,
         ref_gender refgender,
         ref_eye_colors refeyecolors 
        WHERE personprofile.pp_legalentity_number = personprofilenames.pp_legalentity_number -- INNER JOIN 
            AND personprofile.gender_id = refgender.id(+)
            AND personprofile.eye_color_id = refeyecolors.id(+)
           AND personprofile.is_deleted   = 0
                
     
    create index xmatapp.idx_fname_mv_cust_search on xmatapp.mv_CUST_SEARCH(first_name) nologging parallel 8;
    
create index xmatapp.idx_lname_mv_cust_search on xmatapp.mv_CUST_SEARCH(last_name) nologging parallel 8;

create index xmatapp.idx_lname_dob_mv_cust_search on xmatapp.mv_CUST_SEARCH(last_name,dob) nologging parallel 8;

create index xmatapp.idx_lname_gen_mv_cust_search on xmatapp.mv_CUST_SEARCH(last_name,gender1) nologging parallel 8;




CREATE MATERIALIZED VIEW LOG ON XMATAPP.ref_gender WITH rowid,sequence;

CREATE MATERIALIZED VIEW LOG ON XMATAPP.ref_eye_colors WITH rowid,sequence;

CREATE MATERIALIZED VIEW LOG ON XMATAPP.PERSON_PROFILE_NAMES WITH rowid,sequence;

CREATE MATERIALIZED VIEW LOG ON XMATAPP.PERSON_PROFILE WITH rowid,sequence;

Open in new window

Thanks
0
monto1
Asked:
monto1
  • 2
  • 2
1 Solution
 
Helena Markováprogrammer-analystCommented:
I would change refresh option to REFRESH ON DEMAND.
http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#autoId31
0
 
Greg CloughCommented:
Can you check if the refresh it actually being done "Fast":
select OWNER, MVIEW_NAME, REFRESH_METHOD, LAST_REFRESH_TYPE
  from DBA_MVIEWS
  where OWNER = 'XMATAPP'
    and MVIEW_NAME = 'MV_CUST_SEARCH';

Open in new window

Then check the row count of any mview logs.  If these aren't zero or very small immediately after you refresh your mview, then you've got ANOTHER mview somewhere that's not being refreshed:
select 'select count(*) from ' || LOG_OWNER || '.' || LOG_TABLE || ';'
  from DBA_MVIEW_LOGS;

Open in new window

Then check the size of the mview logs, as sometimes you'll have a HUGE log with no rows in it.  Oracle will end up doing a full table scan of this empty table multiple times:
select SEGMENT_NAME, BYTES/1024/1024 MB
  from DBA_SEGMENTS ds,
        DBA_MVIEW_LOGS dml
  where ds.SEGMENT_NAME = dml.LOG_TABLE
    and ds.OWNER = dml.LOG_OWNER
  order by BYTES desc;

Open in new window

Another trick is to lock the stats WHEN THE MVIEW LOG IS EMPTY (so immediately after creation is best):
exec dbms_stats.unlock_table_stats(ownname=>'XMATAPP',tabname=>'MLOG$_PERSON_PROFILE');
--
exec dbms_stats.gather_table_stats(ownname=>'XMATAPP',tabname=>'MLOG$_PERSON_PROFILE',estimate_percent=>100);                                                                                        
--
exec dbms_stats.lock_table_stats(ownname=>'XMATAPP',tabname=>'MLOG$_PERSON_PROFILE');

Open in new window

Along with the locked stats, you may need to set this parameter... check the note to see if it applies to your version of Oracle:
--
-- Enable fast mview refreshing (Oracle Note: 420040.1)
--
alter system set "_mv_refresh_use_stats"=FALSE scope=spfile;

Open in new window

0
 
monto1Author Commented:
Thanks.I'm not able to create the MV in another database whose version is 11.2.0.3(AIX 6.1) ,same create statement.Created the MV logs in the schema since it own the base tables as well as MV.

During MV creation throwing error ORA-12018

ORA-12018: following error encountered during code generation for "XMATSBX"."MV_CUST_SEARCH"
ORA-00904: : invalid identifier



CREATE MATERIALIZED VIEW XMATSBX.MV_CUST_SEARCH NOLOGGING PARALLEL 8 BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
( SELECT personprofilenames.pp_legalentity_number legal_entity_number,
personprofilenames.first_name,
personprofilenames.last_name,
personprofilenames.middle_name,
personprofile.dob,
personprofilenames.name_type_id name_type,
personprofile.eye_color_id,
personprofilenames.suffix_value suffix_value,
personprofile.gender_id gender1,
personprofilenames.is_deleted ,
refgender.description gender,
refeyecolors.description eyecolor ,
personprofilenames.ROWID as r1 ,
refgender.ROWID as r2,
refeyecolors.ROWID as r3,
personprofile.ROWID as r4
FROM
person_profile personprofile,
person_profile_names personprofilenames,
ref_gender refgender,
ref_eye_colors refeyecolors
WHERE personprofile.pp_legalentity_number = personprofilenames.pp_legalentity_number
AND personprofile.gender_id = refgender.id(+)
AND personprofile.eye_color_id = refeyecolors.id(+) AND personprofile.is_deleted = 0 )

CREATE MATERIALIZED VIEW LOG ON XMATSBX.ref_gender WITH rowid,sequence,primary key ,commit scn INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON XMATSBX.ref_eye_colors WITH rowid,sequence,primary key ,commit scn INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON XMATSBX.PERSON_PROFILE_names WITH rowid,sequence,primary key ,commit scn INCLUDING NEW VALUES;

create MATERIALIZED VIEW LOG ON XMATSBX.PERSON_PROFILE WITH rowid,sequence,primary key ,commit scn INCLUDING NEW VALUES;


{CODE}

Privileges of the user.



USERNAME	PRIVILEGE	ADMIN_OPTION
 
XMATSBX	QUERY REWRITE	NO
XMATSBX	CREATE SESSION	NO
XMATSBX	ON COMMIT REFRESH	NO
XMATSBX	CREATE MATERIALIZED VIEW	NO
XMATSBX	FORCE ANY TRANSACTION	NO
XMATSBX	CREATE TABLE	NO
XMATSBX	CREATE PUBLIC SYNONYM	NO
XMATSBX	UNLIMITED TABLESPACE	NO
XMATSBX	CREATE TRIGGER	NO
XMATSBX	CREATE DATABASE LINK	NO
XMATSBX	CREATE SYNONYM	NO
XMATSBX	SELECT ANY TABLE	NO
XMATSBX	CREATE PROCEDURE	NO
XMATSBX	ADVISOR	NO
XMATSBX	CREATE SEQUENCE	NO
XMATSBX	DROP PUBLIC SYNONYM	NO
XMATSBX	CREATE TYPE	NO
XMATSBX	CREATE VIEW	NO

Open in new window


Not sure what prvilege am i missing here or hitting any bug?

Thanks
0
 
Greg CloughCommented:
Is that code a direct copy/paste?  I only ask, as the final line in the crate mview is missing a semicolon:
AND personprofile.eye_color_id = refeyecolors.id(+) AND personprofile.is_deleted = 0 )

Open in new window

0
 
monto1Author Commented:
it was direct copy and paste,doesn't matter if there is no semicolon if execting from TOAD.

Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now