Hi all,
My need is to find information about what can cause slow refresh of mview.
dbms_refresh.refresh('FLATBED_DW.SNAP_AP_INVOICES');
Now it works 15.913 sec, but somethimes it can take hour or more. DDL is shown below. What else should I post here to get help?
Any idea is appreciated.
--------------------------------------------------------
-- DDL for Materialized View SNAP_AP_INVOICES
--------------------------------------------------------
CREATE MATERIALIZED VIEW "FLATBED_DW"."SNAP_AP_INVOICES"
ORGANIZATION HEAP PCTFREE 30 PCTUSED 50 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 10485760 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "FINDATA"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 10485760 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "FININDX"
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 1
WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT
INVOICE_ID,
INVOICE_NUM,
INVOICE_DATE,
INVOICE_AMOUNT,
AMOUNT_PAID,
TERMS_DATE,
INVOICE_TYPE_LOOKUP_CODE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE_CATEGORY,
GL_DATE,
VENDOR_SITE_ID,
ORG_ID,
SOURCE,
LAST_UPDATE_DATE,
TERMS_ID,
PAY_GROUP_LOOKUP_CODE,
ACCTS_PAY_CODE_COMBINATION_ID,
PAYMENT_STATUS_FLAG,
CREATION_DATE
FROM AP.AP_INVOICES_ALL@PRODFIN;
CREATE UNIQUE INDEX "FLATBED_DW"."I_SNAP$_SNAP_AP_INVOICES" ON "FLATBED_DW"."SNAP_AP_INVOICES" ("M_ROW$$")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "FININDX" ;
CREATE INDEX "FLATBED_DW"."SNAP_AP_INV_GL_DATE_IDX" ON "FLATBED_DW"."SNAP_AP_INVOICES" ("GL_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "EFLDWINDX" ;
CREATE INDEX "FLATBED_DW"."SNP_AP_INV_BOL_IDX" ON "FLATBED_DW"."SNAP_AP_INVOICES" ("ATTRIBUTE11", "ATTRIBUTE6")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "EFLDWINDX" ;
CREATE INDEX "FLATBED_DW"."SNP_AP_INV_COMP_I1" ON "FLATBED_DW"."SNAP_AP_INVOICES" ("GL_DATE", "INVOICE_ID", "VENDOR_SITE_ID", "ATTRIBUTE6")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "EFLDWINDX" ;
CREATE INDEX "FLATBED_DW"."SNP_AP_INV_INUM_IDX" ON "FLATBED_DW"."SNAP_AP_INVOICES" ("INVOICE_NUM", "ATTRIBUTE6")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "EFLDWINDX" ;
CREATE INDEX "FLATBED_DW"."SNP_AP_INV_PK_I" ON "FLATBED_DW"."SNAP_AP_INVOICES" ("INVOICE_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 40001536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "FININDX" ;
CREATE INDEX "FLATBED_DW"."SNP_AP_INV_UPD_I" ON "FLATBED_DW"."SNAP_AP_INVOICES" ("LAST_UPDATE_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "FININDX" ;
COMMENT ON MATERIALIZED VIEW "FLATBED_DW"."SNAP_AP_INVOICES" IS 'snapshot table for snapshot FLATBED_DW.SNAP_AP_INVOICES';