Materialized View Refresh is Slow

Many a times i had seen my DBA friends asking this question. There
could be lot of reasons whenever something is slow….but in case of
materialized view, we can use below checks to get confirmed where is the
problem


Troubleshoot the problem in following steps


1) check the network connectivity using ping command. you should able to see no time gap between packets transfer


2) check if tnsping command is taking time


3) check the size of MV log. it should be truncated after every refresh


Note: It may be a surprise for many people if i say MV log will get
truncated after every refresh. But this is the actual thing that
happens. how oracle will manage refresh in such case…lets discuss in
another post



4) check the size of original table. if MV log size is more than original table, then its clear that problem is with MV log


The following is the reason for increment in size of MV log than table


1) Additional MV’s are created on the same tables, but had stopped refreshing now.
 

2) If the snapshot becomes invalid or lost and was not dropped formally.


Solution:

The below would be quite good solutions to apply
 

1) Drop the MV which are not using from long time.


2) drop the materialized view log and re-create (but this
will require a complete refresh and the table will not be accessible
during this refresh to the users)



again, can we drop MV log alone without dropping MV? answer is YES and below is sample command for the same


DROP MATERIALIZED VIEW LOG ON VLRAPP.CL_ACC_HOLD_TRANS;


You can create MV log again using below command 


CREATE MATERIALIZED VIEW LOG ON “VLRAPP”.”CL_ACC_HOLD_TRANS”
 PCTFREE 60 PCTUSED 30 INITRANS 1 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE “DATA”
  WITH PRIMARY KEY EXCLUDING NEW VALUES;



For commands, please use DBMS_METADATA.GET_DDL package (for syntax, just google it)

  • July 15, 2017 | 15 views
  • Comments