Skip to Main Content
  • Questions
  • Create MVW takes 15 mins. Refresh of same MVW takes 12 hours (or mostly failes)

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Jason.

Asked: May 25, 2020 - 9:03 am UTC

Answered by: Connor McDonald - Last updated: May 30, 2020 - 7:58 am UTC

Category: SQL - Version: Oracle RDBMS 12.1.0.2

Viewed 100+ times

You Asked

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

and we said...

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.

and you rated our response

  (3 ratings)

Reviews

But why does the WITH statement move?

May 26, 2020 - 10:57 am UTC

Reviewer: Jason from Ireland

Thanks Connor.

So if I understand what you are saying correctly..

(1) Create a new MVW based on the original select (with parallel on)
(2) Refresh it with ATOMIC_REFRESH=FALSE
(3) Create an MVW of the same name as the original MVW but as a straight select * from the MVW in step 1.
(4) Refresh it with ATOMIC_REFRESH=TRUE

Then update the refresh sql files as applicable?

Still doesn't explain how / why the WITH statement moved?

Connor McDonald

Followup  

May 27, 2020 - 1:06 am UTC

No :-)

I'm saying test your *current* MV with ATOMIC_REFRESH=FALSE

If that is fast, then your issue is mostly like the cost of the delete and the conventional mode insert.

May 26, 2020 - 11:18 am UTC

Reviewer: pmdba from Dayton, OH USA

Oracle only uses the query that you write during the MVW creation. After During refreshes it automatically rewrites the SQL using its own optimization algorithm. You have no control over that process at all.

Switched to atomic_refresh=false

May 28, 2020 - 2:26 pm UTC

Reviewer: Jason from Ireland

Hi Conor

Switched to atomic_refresh=false but 3 1/2 hours later I received a snapshot too old.

Like the atomic_refresh=true refresh, the query is different to that of the create mvw in that the with statement is gone (along with the materialize hint within).
Is there something that would cause that?

At this point, it does seem that a drop and create followed by a refresh as a select * from is the only way forward? Is there any point in tuning a select statement when the refresh doesn't adopt it?

Any guidance greatly appreciated.

Thanks

Jason
Connor McDonald

Followup  

May 30, 2020 - 7:58 am UTC

As a workaround, maybe look at the PREBUILT table syntax, so you take control of the data population, the MV will just sit on top of the table.

I'll investigate more ... but to be a little brutal about it :-) the materialize hint *is* undocumented

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.