We have a materialized view, which has undergone some tuning. When creating the new MVW, it took 15 mins.
The MVW is then refreshed using a shell in which many MVWs are refreshed in order. As part of the refresh, to make it faster, the indexes on the MVW are dropped before refresh and recreated after.
The issue is the refresh takes up to 12 hours (and mostly fails with snapshot too old).
When I look at the Insert statement that gets executed during the refresh, the Select clause is different to that of the tuned SQL. What could cause this? E.G. In the tuned Create MVW, we have moved an inner Select to make it a WITH statement and within it added the /* materialize */ hint. Then in the insert statement that gets executed via the refresh, the WITH statement is gone and is within the main SELECT again. Note - it is an atomic refresh so row by row.
Is there a setting in the refresh or create that can influence this? I thought an mvw refresh, would execute the select from the initial Create?
CREATE MATERIALIZED VIEW COSMOS_REPORTS.DIS_SHIPMENT_MED_NUMBERS_MVW (MEDICATION_NUMBER,LOT_NUMBER,SUBINVENTORY_CODE,PATIENT_NUMBER,VISIT_NUMBER,SERIAL_STATE,CONTAINER_NUMBER,DISTRIBUTION_ORDER_NO,USER_STATUS,SHIPPED_FLAG,INVENTORY_ITEM_ID,ORGANIZATION_ID,LINE_ID,HEADER_ID,TRIAL_SITE_ID,SHIP_TO_CONTACT_ID,CURRENT_ORGANIZATION_ID,STATE_CODE,TRANSACTION_ID)
CACHE
LOGGING
NOCOMPRESS
PARALLEL ( DEGREE 4 INSTANCES 1 )
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
WITH flc
AS (SELECT /*+ materialize */
TO_NUMBER (flc.lookup_code) lookup_code, flc.meaning
FROM apps.fnd_lookup_values flc
WHERE flc.view_application_id = 700
AND flc.lookup_type = 'SERIAL_NUM_STATUS')
SELECT xmnol.medication_num medication_number,
xmnol.lot_number,
xmnol.subinventory_code, etc......
INSERT /*+ BYPASS_RECURSIVE_CHECK */
INTO "COSMOS_REPORTS"."DIS_SHIPMENT_MED_NUMBERS_MVW"
(SELECT "XMNOL"."MEDICATION_NUM" "MEDICATION_NUMBER",
"XMNOL"."LOT_NUMBER" "LOT_NUMBER",
"XMNOL"."SUBINVENTORY_CODE" "SUBINVENTORY_CODE",
'N/A' "PATIENT_NUMBER",
'N/A' "VISIT_NUMBER",
"ISNV"."SERIAL_STATE" "SERIAL_STATE",
NVL ("DDDV"."CONTAINER_NAME", 'N/A') "CONTAINER_NUMBER",
"OOHA"."DISTRIBUTION_ORDER_NO" "DISTRIBUTION_ORDER_NO",
"OOHA"."USER_STATUS" "USER_STATUS",
'No' "SHIPPED_FLAG",
"XMNOL"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
"XMNOL"."ORGANIZATION_ID" "ORGANIZATION_ID",
"XMNOL"."LINE_ID" "LINE_ID",
"OOHA"."HEADER_ID" "HEADER_ID",
"OOHA"."TRIAL_SITE_ID" "TRIAL_SITE_ID",
"OOHA"."SHIP_TO_CONTACT_ID" "SHIP_TO_CONTACT_ID",
"ISNV"."CURRENT_ORGANIZATION_ID" "CURRENT_ORGANIZATION_ID",
"ISNV"."CURRENT_STATUS" "STATE_CODE",
0 "TRANSACTION_ID"
FROM "BOLINF"."XXAC_MED_NOS_ORD_LINES" "XMNOL",
(SELECT TO_CHAR ("OOHA"."ORDER_NUMBER") "DISTRIBUTION_ORDER_NO",
"OOHA"."ATTRIBUTE16" "USER_STATUS",
"OOHA"."HEADER_ID" "HEADER_ID",
"OOHA"."SHIP_TO_ORG_ID" "TRIAL_SITE_ID",
"OOHA"."SHIP_TO_CONTACT_ID" "SHIP_TO_CONTACT_ID"
FROM "APPS"."OE_ORDER_HEADERS_ALL" "OOHA"
WHERE UPPER ("OOHA"."CONTEXT") = 'DISTRIBUTION'
AND EXISTS
(SELECT 'X'
FROM "COSMOS_REPORTS"."COSMOS_REPORT_PARAMETERS" "COSMOS_REPORT_PARAMETERS"
WHERE "COSMOS_REPORT_PARAMETERS"."PARAM_CODE" =
'DIS_STATUS_ASSIGNED_MEDNO'
AND "COSMOS_REPORT_PARAMETERS"."PARAM_VALUE1" =
'NON_RESERVED'
AND "COSMOS_REPORT_PARAMETERS"."PARAM_VALUE" =
UPPER ("OOHA"."ATTRIBUTE16"))) "OOHA",
(SELECT DISTINCT
NVL ("WSH_DELIVERY_DETAILS"."CONTAINER_NAME", 'N/A')
"CONTAINER_NAME",
"WSH_DELIVERY_DETAILS"."SOURCE_HEADER_ID"
"SOURCE_HEADER_ID"
FROM "APPS"."WSH_DELIVERY_DETAILS" "WSH_DELIVERY_DETAILS"
WHERE "WSH_DELIVERY_DETAILS"."CONTAINER_NAME" IS NOT NULL
AND "WSH_DELIVERY_DETAILS"."SOURCE_HEADER_ID" IS NOT NULL) "DDDV",
(SELECT "MSN"."SERIAL_NUMBER" "SERIAL_NUMBER",
"MSN"."CURRENT_SUBINVENTORY_CODE"
"CURRENT_SUBINVENTORY_CODE",
"MSN"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
"MSN"."CURRENT_ORGANIZATION_ID" "CURRENT_ORGANIZATION_ID",
"MSN"."LOT_NUMBER" "LOT_NUMBER",
"FLC"."MEANING" "SERIAL_STATE",
"MSN"."CURRENT_STATUS" "CURRENT_STATUS"
FROM "APPS"."MTL_SERIAL_NUMBERS" "MSN",
(SELECT TO_NUMBER ("FLC"."LOOKUP_CODE") "LOOKUP_CODE",
"FLC"."MEANING" "MEANING"
FROM "APPS"."FND_LOOKUP_VALUES" "FLC"
WHERE "FLC"."VIEW_APPLICATION_ID" = 700
AND "FLC"."LOOKUP_TYPE" = 'SERIAL_NUM_STATUS') "FLC"
WHERE "MSN"."CURRENT_STATUS" = "FLC"."LOOKUP_CODE"
AND "MSN"."ATTRIBUTE1" IS NULL
AND "MSN"."ATTRIBUTE2" IS NULL) "ISNV"
WHERE UPPER ("XMNOL"."STATUS_FLAG") = 'B'
AND "OOHA"."HEADER_ID" = "XMNOL"."HEADER_ID"
AND "OOHA"."HEADER_ID" = "DDDV"."SOURCE_HEADER_ID"(+)
AND "XMNOL"."INVENTORY_ITEM_ID" = "ISNV"."INVENTORY_ITEM_ID"
AND "XMNOL"."ORGANIZATION_ID" = "ISNV"."CURRENT_ORGANIZATION_ID"
AND "XMNOL"."LOT_NUMBER" = "ISNV"."LOT_NUMBER"
AND "XMNOL"."MEDICATION_NUM" = "ISNV"."SERIAL_NUMBER")
UNION ALL
(SELECT "MUT"."SERIAL_NUMBER" "MEDICATION_NUMBER",
"MMT"."LOT_NUMBER" "LOT_NUMBER",
"MMT"."SUBINVENTORY_CODE" "SUBINVENTORY_CODE",
NVL ("ISNV"."PATIENT_NUMBER", 'N/A') "PATIENT_NUMBER",
NVL ("ISNV"."VISIT_NUMBER", 'N/A') "VISIT_NUMBER",
"ISNV"."SERIAL_STATE" "SERIAL_STATE",
NVL ("DDDV"."CONTAINER_NAME", 'N/A') "CONTAINER_NUMBER",
"DSHV"."DISTRIBUTION_ORDER_NO" "DISTRIBUTION_ORDER_NO",
"DSHV"."USER_STATUS" "USER_STATUS",
CASE
WHEN UPPER ("DDDV"."RELEASED_STATUS") = 'C' THEN 'Yes'
ELSE 'No'
END
"SHIPPED_FLAG",
"MMT"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
"MMT"."ORGANIZATION_ID" "ORGANIZATION_ID",
"DDDV"."SOURCE_LINE_ID" "LINE_ID",
"DSHV"."HEADER_ID" "HEADER_ID",
"DSHV"."TRIAL_SITE_ID" "TRIAL_SITE_ID",
"DSHV"."SHIP_TO_CONTACT_ID" "SHIP_TO_CONTACT_ID",
"ISNV"."CURRENT_ORGANIZATION_ID" "CURRENT_ORGANIZATION_ID",
"ISNV"."CURRENT_STATUS" "STATE_CODE",
"MMT"."TRANSACTION_ID" "TRANSACTION_ID"
FROM "APPS"."MTL_UNIT_TRANSACTIONS" "MUT",
(SELECT "MTLN"."LOT_NUMBER" "LOT_NUMBER",
"MMT"."SUBINVENTORY_CODE" "SUBINVENTORY_CODE",
"MMT"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
"MMT"."ORGANIZATION_ID" "ORGANIZATION_ID",
"MMT"."TRX_SOURCE_LINE_ID" "TRX_SOURCE_LINE_ID",
"MMT"."TRANSACTION_ID" "TRANSACTION_ID",
"MTLN"."SERIAL_TRANSACTION_ID" "SERIAL_TRANSACTION_ID"
FROM "APPS"."MTL_MATERIAL_TRANSACTIONS" "MMT",
"APPS"."MTL_TRANSACTION_LOT_NUMBERS" "MTLN"
WHERE "MMT"."TRANSACTION_QUANTITY" < 0
AND "MTLN"."SERIAL_TRANSACTION_ID" <> 0
AND "MMT"."TRANSACTION_ID" = "MTLN"."TRANSACTION_ID"
AND EXISTS
(SELECT 'X'
FROM "APPS"."MTL_TRANSACTION_TYPES" "MTT"
WHERE EXISTS
(SELECT 'X'
FROM "COSMOS_REPORTS"."COSMOS_REPORT_PARAMETERS" "COSMOS_REPORT_PARAMETERS"
WHERE "COSMOS_REPORT_PARAMETERS"."PARAM_CODE" =
'ASSIGNED_MEDNO_TRX_NAMES'
AND UPPER (
"MTT"."TRANSACTION_TYPE_NAME") =
"COSMOS_REPORT_PARAMETERS"."PARAM_VALUE")
AND "MTT"."TRANSACTION_TYPE_ID" =
"MMT"."TRANSACTION_TYPE_ID"
AND "MTT"."TRANSACTION_SOURCE_TYPE_ID" =
"MMT"."TRANSACTION_SOURCE_TYPE_ID")
AND EXISTS
(SELECT 'X'
FROM "APPS"."MTL_TXN_SOURCE_TYPES" "MTST"
WHERE EXISTS
(SELECT 'X'
FROM "COSMOS_REPORTS"."COSMOS_REPORT_PARAMETERS" "COSMOS_REPORT_PARAMETERS"
WHERE "COSMOS_REPORT_PARAMETERS"."PARAM_CODE" =
'ASSIGNED_MEDNO_TRX_SRC_NAME'
AND UPPER (
"MTST"."TRANSACTION_SOURCE_TYPE_NAME") =
"COSMOS_REPORT_PARAMETERS"."PARAM_VALUE")
AND "MTST"."TRANSACTION_SOURCE_TYPE_ID" =
"MMT"."TRANSACTION_SOURCE_TYPE_ID")) "MMT",
(SELECT "WDD"."SOURCE_LINE_ID" "SOURCE_LINE_ID",
"WDD"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
"WDD"."ORGANIZATION_ID" "ORGANIZATION_ID",
"WDD"."SOURCE_HEADER_ID" "SOURCE_HEADER_ID",
"WDD"."RELEASED_STATUS" "RELEASED_STATUS",
"WDD"."CONTAINER_NAME" "CONTAINER_NAME",
"WDD"."LOT_NUMBER" "LOT_NUMBER",
"WDD"."TRANSACTION_ID" "TRANSACTION_ID"
FROM "APPS"."WSH_DELIVERY_DETAILS" "WDD"
WHERE ( "WDD"."RELEASED_STATUS" = 'C'
OR "WDD"."RELEASED_STATUS" = 'Y')
AND "WDD"."SOURCE_LINE_ID" IS NOT NULL
AND "WDD"."SOURCE_HEADER_ID" IS NOT NULL
AND "WDD"."LOT_NUMBER" IS NOT NULL) "DDDV",
(SELECT "OOHA"."ATTRIBUTE16" "USER_STATUS",
TO_CHAR ("OOHA"."ORDER_NUMBER") "DISTRIBUTION_ORDER_NO",
"OOHA"."HEADER_ID" "HEADER_ID",
"OOHA"."SHIP_TO_ORG_ID" "TRIAL_SITE_ID",
"OOHA"."SHIP_TO_CONTACT_ID" "SHIP_TO_CONTACT_ID"
FROM "APPS"."OE_ORDER_HEADERS_ALL" "OOHA"
WHERE UPPER ("OOHA"."CONTEXT") = 'DISTRIBUTION'
AND EXISTS
(SELECT 'X'
FROM "COSMOS_REPORTS"."COSMOS_REPORT_PARAMETERS" "COSMOS_REPORT_PARAMETERS"
WHERE "COSMOS_REPORT_PARAMETERS"."PARAM_CODE" =
'DIS_STATUS_ASSIGNED_MEDNO'
AND "COSMOS_REPORT_PARAMETERS"."PARAM_VALUE1" =
'RESERVED'
AND "COSMOS_REPORT_PARAMETERS"."PARAM_VALUE" =
UPPER ("OOHA"."ATTRIBUTE16"))) "DSHV",
(SELECT "MSN"."SERIAL_NUMBER" "SERIAL_NUMBER",
"MSN"."CURRENT_SUBINVENTORY_CODE"
"CURRENT_SUBINVENTORY_CODE",
"MSN"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
"MSN"."CURRENT_ORGANIZATION_ID" "CURRENT_ORGANIZATION_ID",
"MSN"."LOT_NUMBER" "LOT_NUMBER",
"FLC"."MEANING" "SERIAL_STATE",
"MSN"."ATTRIBUTE1" "PATIENT_NUMBER",
"MSN"."ATTRIBUTE2" "VISIT_NUMBER",
"MSN"."CURRENT_STATUS" "CURRENT_STATUS"
FROM "APPS"."MTL_SERIAL_NUMBERS" "MSN",
(SELECT TO_NUMBER ("FLC"."LOOKUP_CODE") "LOOKUP_CODE",
"FLC"."MEANING" "MEANING"
FROM "APPS"."FND_LOOKUP_VALUES" "FLC"
WHERE "FLC"."VIEW_APPLICATION_ID" = 700
AND "FLC"."LOOKUP_TYPE" = 'SERIAL_NUM_STATUS') "FLC"
WHERE "MSN"."CURRENT_STATUS" = "FLC"."LOOKUP_CODE") "ISNV"
WHERE "MUT"."TRANSACTION_ID" = "MMT"."SERIAL_TRANSACTION_ID"
AND "MUT"."INVENTORY_ITEM_ID" = "MMT"."INVENTORY_ITEM_ID"
AND "MUT"."ORGANIZATION_ID" = "MMT"."ORGANIZATION_ID"
AND "ISNV"."INVENTORY_ITEM_ID" = "MUT"."INVENTORY_ITEM_ID"
AND "ISNV"."SERIAL_NUMBER" = "MUT"."SERIAL_NUMBER"
AND "ISNV"."LOT_NUMBER" = "MMT"."LOT_NUMBER"
AND "DDDV"."TRANSACTION_ID" = "MMT"."TRANSACTION_ID"
AND "DDDV"."INVENTORY_ITEM_ID" = "MMT"."INVENTORY_ITEM_ID"
AND "DDDV"."ORGANIZATION_ID" = "MMT"."ORGANIZATION_ID"
AND "DDDV"."LOT_NUMBER" = "MMT"."LOT_NUMBER"
AND "DSHV"."HEADER_ID" = "DDDV"."SOURCE_HEADER_ID")
PROMPT Start Dropping INDEX on dis_shipment_med_numbers_mvw materialized view
DROP INDEX COSMOS_REPORTS.IDX_SHIP_MEDNO_HEADER_LINE_ID
/
DROP INDEX COSMOS_REPORTS.IDX_SHIP_MEDNO_ITEM_ORG_ID
/
DROP INDEX COSMOS_REPORTS.IDX_SHIP_MEDNO_trialsite_ID
/
PROMPT Finished Dropping INDEX on dis_shipment_med_numbers_mvw materialized view
execute dbms_mview.refresh('DIS_SHIPMENT_MED_NUMBERS_MVW', 'C', PARALLELISM=>4, atomic_refresh=> TRUE);
PROMPT Start Creating INDEX on dis_shipment_med_numbers_mvw materialized view
CREATE INDEX COSMOS_REPORTS.IDX_SHIP_MEDNO_HEADER_LINE_ID
ON cosmos_reports.dis_shipment_med_numbers_mvw
(header_id, line_id) LOGGING TABLESPACE XBOLX NOPARALLEL
/
CREATE INDEX COSMOS_REPORTS.IDX_SHIP_MEDNO_ITEM_ORG_ID ON cosmos_reports.dis_shipment_med_numbers_mvw
(inventory_item_id, organization_id, lot_number) LOGGING TABLESPACE XBOLX NOPARALLEL
/
CREATE INDEX COSMOS_REPORTS.IDX_SHIP_MEDNO_trialsite_ID ON cosmos_reports.dis_shipment_med_numbers_mvw
(trial_site_id) LOGGING TABLESPACE XBOLX NOPARALLEL
/
PROMPT Finished Creating INDEX on dis_shipment_med_numbers_mvw materialized view
How many rows are in the resulting materialised view ? Because I suspect its your "atomic_refresh" parameter.
atomic_refresh=true
- do a DELETE of the old data
- do an insert of the new data
- because it is a single transaction, you are not going to get parallel benefits
atomic_refresh=false
- do a TRUNCATE of the data
- do an insert of the data
- because it is a load on empty, you get direct load insert plus parallel potential
You probably also want to take a look at out_of_place=>true to minimise disruption here.