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.
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)
WITH PRIMARY KEY EXCLUDING NEW VALUES;
For commands, please use DBMS_METADATA.GET_DDL package (for syntax, just google it)