Ramaswamy Ganduri
asked on
Materialized View is Generating Excess Redo
Hi
One of the Database is generating excessive Redo. From AWR report I identified the following MV is generating excessive Redo. Can you please give me some Recommendations in fine tuning the MV so that it stops Generating Excess redo.
It is Generating almost 530 GB of Redo per day
MV DDL is as follows
CREATE MATERIALIZED VIEW "REPOSITORY"."MEDIAINTERAC
"CREATETIME_GMT", "CREATETIME", "DURATION", "CHAT_ID", "DATA_WAKE_ID", "ANI", "DNIS", "ANIMATCHFLAG", "SENDER",
"RECIPIENT", "SUBJECT", "ABANDONED_FLAG", "ABANDON_TIME", "TASK_TYPE", "USERNAME", "MSGID", "PARENTMSGID", "TRACKINGID",
"LANGUAGECODE", "PROTOCOL", "INITIATION", "EMAILTYPE", "EMAILSPECIALSTATUS", "EMAILCURRENTSTATUS", "CUSTOMER_KEY",
"ORGANIZATION_KEY", "OUTBOUNDCALLNUMBER", "EMAILTYPECODE")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "IC_REPOSITORY"
CACHE
BUILD DEFERRED
USING INDEX
REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 15/1440
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS ENABLE QUERY REWRITE
AS SELECT MI.PKEY,
MI.CONTACT_KEY,
MI.TYPE,
MI.DIRECTION,
TC.TIMEZONE,
MI.CREATETIME CREATETIME_GMT,
CASE
WHEN MI.CREATETIME BETWEEN TC.DST_START AND TC.DST_END
THEN (MI.CREATETIME + (TC.DT_OFFSET/24))
ELSE (MI.CREATETIME + (TC.ST_OFFSET/24))
END CREATETIME,
MI.DURATION,
MI.CHAT_ID,
MI.DATA_WAKE_ID,
MI.ANI,
MI.DNIS,
MI.ANIMATCHFLAG,
MI.SENDER,
MI.RECIPIENT,
MI.SUBJECT,
MI.ABANDONED_FLAG,
MI.ABANDON_TIME,
MI.TASK_TYPE,
MI.USERNAME,
MI.MSGID,
MI.PARENTMSGID,
MI.TRACKINGID,
MI.LANGUAGECODE,
MI.PROTOCOL,
MI.INITIATION,
MI.EMAILTYPE,
MI.EMAILSPECIALSTATUS,
MI.EMAILCURRENTSTATUS,
MI.CUSTOMER_KEY,
MI.ORGANIZATION_KEY,
MI.OUTBOUNDCALLNUMBER,
MI.EMAILTYPECODE
FROM REPOSITORY.MEDIAINTERACTIO
INNER JOIN REPOSITORY.TIME_CONVERSION
ON TO_NUMBER(TO_CHAR(MI.CREAT
One of the Database is generating excessive Redo. From AWR report I identified the following MV is generating excessive Redo. Can you please give me some Recommendations in fine tuning the MV so that it stops Generating Excess redo.
It is Generating almost 530 GB of Redo per day
MV DDL is as follows
CREATE MATERIALIZED VIEW "REPOSITORY"."MEDIAINTERAC
"CREATETIME_GMT", "CREATETIME", "DURATION", "CHAT_ID", "DATA_WAKE_ID", "ANI", "DNIS", "ANIMATCHFLAG", "SENDER",
"RECIPIENT", "SUBJECT", "ABANDONED_FLAG", "ABANDON_TIME", "TASK_TYPE", "USERNAME", "MSGID", "PARENTMSGID", "TRACKINGID",
"LANGUAGECODE", "PROTOCOL", "INITIATION", "EMAILTYPE", "EMAILSPECIALSTATUS", "EMAILCURRENTSTATUS", "CUSTOMER_KEY",
"ORGANIZATION_KEY", "OUTBOUNDCALLNUMBER", "EMAILTYPECODE")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "IC_REPOSITORY"
CACHE
BUILD DEFERRED
USING INDEX
REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 15/1440
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS ENABLE QUERY REWRITE
AS SELECT MI.PKEY,
MI.CONTACT_KEY,
MI.TYPE,
MI.DIRECTION,
TC.TIMEZONE,
MI.CREATETIME CREATETIME_GMT,
CASE
WHEN MI.CREATETIME BETWEEN TC.DST_START AND TC.DST_END
THEN (MI.CREATETIME + (TC.DT_OFFSET/24))
ELSE (MI.CREATETIME + (TC.ST_OFFSET/24))
END CREATETIME,
MI.DURATION,
MI.CHAT_ID,
MI.DATA_WAKE_ID,
MI.ANI,
MI.DNIS,
MI.ANIMATCHFLAG,
MI.SENDER,
MI.RECIPIENT,
MI.SUBJECT,
MI.ABANDONED_FLAG,
MI.ABANDON_TIME,
MI.TASK_TYPE,
MI.USERNAME,
MI.MSGID,
MI.PARENTMSGID,
MI.TRACKINGID,
MI.LANGUAGECODE,
MI.PROTOCOL,
MI.INITIATION,
MI.EMAILTYPE,
MI.EMAILSPECIALSTATUS,
MI.EMAILCURRENTSTATUS,
MI.CUSTOMER_KEY,
MI.ORGANIZATION_KEY,
MI.OUTBOUNDCALLNUMBER,
MI.EMAILTYPECODE
FROM REPOSITORY.MEDIAINTERACTIO
INNER JOIN REPOSITORY.TIME_CONVERSION
ON TO_NUMBER(TO_CHAR(MI.CREAT
How much data is changing on the base tables? Does 530 GB seem overly excessive for the amount of changes on the base tables?
nologging might reduce the amount of redo generated but it will also make the view unrecoverable in the event of a disaster.
Have you created MV logs on the base tables so you can do a FAST refresh?
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6003.htm#SQLRF01303
nologging might reduce the amount of redo generated but it will also make the view unrecoverable in the event of a disaster.
Have you created MV logs on the base tables so you can do a FAST refresh?
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6003.htm#SQLRF01303
ASKER
Nologigng is already enabled on the base table. Not sure about FAST Refresh and MV logs
Does you do manual hot backups of the database
After you put the tablespaces in hot backup mode:
Alter database begin backup;
Select * from v$backup;
you have also to cancel the hot backup mode:
Alter database end backup;
After you put the tablespaces in hot backup mode:
Alter database begin backup;
Select * from v$backup;
you have also to cancel the hot backup mode:
Alter database end backup;
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
alter table "REPOSITORY"."MEDIAINTERAC
Please note that indexes will still be logged