Skip to Main Content

mview refresh takes too long sometimes

2792584Nov 18 2014 — edited Nov 19 2014

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';

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Dec 17 2014
Added on Nov 18 2014
1 comment
539 views