Skip to Main Content
  • Questions

  • MV Upgrade spoiling the Execution Plan

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Krishna .

Asked: August 17, 2022 - 5:33 am UTC

Last updated: August 25, 2022 - 2:52 am UTC

Version: 19c

Viewed 10K+ times! This question is

You Asked

MV upgrade causing fine tuned Query being re-written with Bad execution plan hints and gets days for refresh activity.

Steps:

Built the query with proper hints where in inline views are being pushed and the execution time is in seconds,

Logical view Built

ad_zd_mview.upgrade being run

At this point whole query being transformed into altogether different manner and which is expected.
the underlying Views used in the Materialized view doesn't get into pushed predicate mode which is causing multiple full table scans and getting days for refresh activity.

once we take the ddl script and fine tune the generated query again, it only takes seconds to complete the refresh.

is there any way to retain the original query while mv upgrade, session parameters explored but no luck.

Any help or suggestions highly appreciated.

Thanks in advance,
Krishna Kumar


and Connor said...

Without seeing code, a test case, execution plans etc etc....I'm not entire sure how we can help you

Your best bet is probably to look at SQL Plan Management (SPM) to capture the plan for the *refresh* SQL (which you can identify via tracing) and then set that as the only accepted plan post upgrade.

Plenty of good info on SPM here from the optimizer team

https://blogs.oracle.com/optimizer/category/opt-sql-plan-management

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

The Original Query before MV Upgrade

Krishna Kumar, August 24, 2022 - 2:35 am UTC 

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "APPS"."PROMAST#" ("PRO_KEY", "PRO_FY", "PRO_MY", "PRO_AA", "PRO_PG", "PRO_UT", "PRO_NO", "PRO_SRNO", "PRO_RECT", "PRO_DT", "PRO_LED", "PRO_CUST", "PRO_MUT", "PRO_MDIV", "PRO_BD", "PRO_PROD", "PRO_RATE", "PRO_UM", "PRO_QTY", "PRO_CASE", "PRO_DIS1", "PRO_DIS2", "PRO_DIS3", "PRO_NETVL", "PRO_EXC", "PRO_EXCD", "PRO_TXCD", "PRO_DIV", "PRO_MSET", "PRO_VAL", "PRO_ZNCD", "PRO_DICD", "PRO_SLCD", "PRO_LAC", "PRO_ORDER_NUMBER", "PRO_HEADER_ID", "PRO_LINE_ID", "PRO_DELIVERY_ID", "PRO_DELIVERY_DETAIL_ID", "PRO_CUSTOMER_ID", "PRO_CUST_NAME", "PRO_PROD_DESC", "PRO_ORDER_TYPE_ID", "PRO_ORDER_TYPE_NAME", "PRO_INVENTORY_ITEM_ID", "PRO_AREA", "PRO_BV_CODE", "PRO_CUST_CLASS", "PRO_NETVL_WITH_DIS", "PRO_AREA_OLD", "PRO_QTYV", "PRO_WARRANTY_VAL") AS 
  SELECT pro_key,
           pro_fy,
           pro_my,
           pro_aa,
           pro_pg,
           pro_ut,
           pro_no,
           pro_srno,
           pro_rect,
           TRUNC (pro_dt)     pro_dt,
           pro_led,
           pro_cust,
           pro_mut,
           pro_mdiv,
           pro_bd,
           pro_prod,
           pro_rate,
           pro_um,
           pro_qty,
           pro_case,
           pro_dis1,
           pro_dis2,
           pro_dis3,
           pro_netvl,
           pro_exc,
           pro_excd,
           pro_txcd,
           pro_div,
           pro_mset,
           pro_val,
           pro_zncd,
           pro_dicd,
           pro_slcd,
           pro_lac,
           pro_order_number,
           pro_header_id,
           pro_line_id,
           pro_delivery_id,
           pro_delivery_detail_id,
           pro_customer_id,
           pro_cust_name,
           pro_prod_desc,
           pro_order_type_id,
           pro_order_type_name,
           pro_inventory_item_id,
           pro_area,
           pro_bv_code,
           pro_cust_class,
           pro_netvl_with_dis,
           pro_area_old,
           pro_qtyv,
           pro_warranty_val
      FROM (SELECT * FROM CUS.PROMAST_PRIOR_TO_GST
            UNION ALL
            SELECT *
              FROM cus.awl_gst_invoices
             WHERE pro_order_type_id NOT IN
                       (SELECT order_type_id FROM cus.awl_ordertype_exclude)
            UNION ALL
            SELECT *
              FROM cus.awl_GST_service_invoices
             WHERE pro_order_type_id NOT IN
                       (SELECT order_type_id FROM cus.awl_ordertype_exclude)
            UNION ALL
            SELECT *
              FROM cus.awl_GST_credit_memo
             WHERE pro_order_type_id NOT IN
                       (SELECT order_type_id FROM cus.awl_ordertype_exclude))

Transformed Query after MV upgrade

Krishna Kumar, August 24, 2022 - 3:25 am UTC

Transformed query after MV Upgrade, 
CREATE OR REPLACE FORCE VIEW apps.promast_kk# (
    pro_key,
    pro_fy,
    pro_my,
    pro_aa,
    pro_pg,
    pro_ut,
    pro_no,
    pro_srno,
    pro_rect,
    pro_dt,
    pro_led,
    pro_cust,
    pro_mut,
    pro_mdiv,
    pro_bd,
    pro_prod,
    pro_rate,
    pro_um,
    pro_qty,
    pro_case,
    pro_dis1,
    pro_dis2,
    pro_dis3,
    pro_netvl,
    pro_exc,
    pro_excd,
    pro_txcd,
    pro_div,
    pro_mset,
    pro_val,
    pro_zncd,
    pro_dicd,
    pro_slcd,
    pro_lac,
    pro_order_number,
    pro_header_id,
    pro_line_id,
    pro_delivery_id,
    pro_delivery_detail_id,
    pro_customer_id,
    pro_cust_name,
    pro_prod_desc,
    pro_order_type_id,
    pro_order_type_name,
    pro_inventory_item_id,
    pro_area,
    pro_bv_code,
    pro_cust_class,
    pro_netvl_with_dis,
    pro_area_old,
    pro_qtyv,
    pro_warranty_val
) AS
    SELECT /*+ PUSH_SUBQ */ 
    a1.pro_key                   pro_key,
    a1.pro_fy                    pro_fy,
    a1.pro_my                    pro_my,
    a1.pro_aa                    pro_aa,
    a1.pro_pg                    pro_pg,
    a1.pro_ut                    pro_ut,
    a1.pro_no                    pro_no,
    a1.pro_srno                  pro_srno,
    a1.pro_rect                  pro_rect,
    a1.pro_dt                    pro_dt,
    a1.pro_led                   pro_led,
    a1.pro_cust                  pro_cust,
    a1.pro_mut                   pro_mut,
    a1.pro_mdiv                  pro_mdiv,
    a1.pro_bd                    pro_bd,
    a1.pro_prod                  pro_prod,
    a1.pro_rate                  pro_rate,
    a1.pro_um                    pro_um,
    a1.pro_qty                   pro_qty,
    a1.pro_case                  pro_case,
    a1.pro_dis1                  pro_dis1,
    a1.pro_dis2                  pro_dis2,
    a1.pro_dis3                  pro_dis3,
    a1.pro_netvl                 pro_netvl,
    a1.pro_exc                   pro_exc,
    a1.pro_excd                  pro_excd,
    a1.pro_txcd                  pro_txcd,
    a1.pro_div                   pro_div,
    a1.pro_mset                  pro_mset,
    a1.pro_val                   pro_val,
    a1.pro_zncd                  pro_zncd,
    a1.pro_dicd                  pro_dicd,
    a1.pro_slcd                  pro_slcd,
    a1.pro_lac                   pro_lac,
    a1.pro_order_number          pro_order_number,
    a1.pro_header_id             pro_header_id,
    a1.pro_line_id               pro_line_id,
    a1.pro_delivery_id           pro_delivery_id,
    a1.pro_delivery_detail_id    pro_delivery_detail_id,
    a1.pro_customer_id           pro_customer_id,
    a1.pro_cust_name             pro_cust_name,
    a1.pro_prod_desc             pro_prod_desc,
    a1.pro_order_type_id         pro_order_type_id,
    a1.pro_order_type_name       pro_order_type_name,
    a1.pro_inventory_item_id     pro_inventory_item_id,
    a1.pro_area                  pro_area,
    a1.pro_bv_code               pro_bv_code,
    a1.pro_cust_class            pro_cust_class,
    a1.pro_netvl_with_dis        pro_netvl_with_dis,
    a1.pro_area_old              pro_area_old,
    a1.pro_qtyv                  pro_qtyv,
    a1.pro_warranty_val          pro_warranty_val
FROM
    (
        ( SELECT /*+ PUSH_SUBQ */ 
                                    a139.pro_key                   pro_key,
            a139.pro_fy                    pro_fy,
            a139.pro_my                    pro_my,
            a139.pro_aa                    pro_aa,
            a139.pro_pg                    pro_pg,
            a139.pro_ut                    pro_ut,
            a139.pro_no                    pro_no,
            a139.pro_srno                  pro_srno,
            a139.pro_rect                  pro_rect,
            a139.pro_dt                    pro_dt,
            a139.pro_led                   pro_led,
            a139.pro_cust                  pro_cust,
            a139.pro_mut                   pro_mut,
            a139.pro_mdiv                  pro_mdiv,
            a139.pro_bd                    pro_bd,
            a139.pro_prod                  pro_prod,
            a139.pro_rate                  pro_rate,
            a139.pro_um                    pro_um,
            a139.pro_qty                   pro_qty,
            a139.pro_case                  pro_case,
            a139.pro_dis1                  pro_dis1,
            a139.pro_dis2                  pro_dis2,
            a139.pro_dis3                  pro_dis3,
            a139.pro_netvl                 pro_netvl,
            a139.pro_exc                   pro_exc,
            a139.pro_excd                  pro_excd,
            a139.pro_txcd                  pro_txcd,
            a139.pro_div                   pro_div,
            a139.pro_mset                  pro_mset,
            a139.pro_val                   pro_val,
            a139.pro_zncd                  pro_zncd,
            a139.pro_dicd                  pro_dicd,
            a139.pro_slcd                  pro_slcd,
            a139.pro_lac                   pro_lac,
            a139.pro_order_number          pro_order_number,
            a139.pro_header_id             pro_header_id,
            a139.pro_line_id               pro_line_id,
            a139.pro_delivery_id           pro_delivery_id,
            a139.pro_delivery_detail_id    pro_delivery_detail_id,
            a139.pro_customer_id           pro_customer_id,
            a139.pro_cust_name             pro_cust_name,
            a139.pro_prod_desc             pro_prod_desc,
            a139.pro_order_type_id         pro_order_type_id,
            a139.pro_order_type_name       pro_order_type_name,
            a139.pro_inventory_item_id     pro_inventory_item_id,
            a139.pro_area                  pro_area,
            a139.pro_bv_code               pro_bv_code,
            a139.pro_cust_class            pro_cust_class,
            a139.pro_netvl_with_dis        pro_netvl_with_dis,
            a139.pro_area_old              pro_area_old,
            a139.pro_qtyv                  pro_qtyv,
            a139.pro_warranty_val          pro_warranty_val
        FROM
            cus.promast_prior_to_gst a139
        )
        UNION ALL
        ( SELECT /*+ NO_UNNEST PUSH_SUBQ push_pred */ 
            a138.pro_key                   pro_key,
            a138.pro_fy                    pro_fy,
            a138.pro_my                    pro_my,
            a138.pro_aa                    pro_aa,
            a138.pro_pg                    pro_pg,
            a138.pro_ut                    pro_ut,
            a138.pro_no                    pro_no,
            a138.pro_srno                  pro_srno,
            a138.pro_rect                  pro_rect,
            a138.pro_dt                    pro_dt,
            a138.pro_led                   pro_led,
            a138.pro_cust                  pro_cust,
            a138.pro_mut                   pro_mut,
            a138.pro_mdiv                  pro_mdiv,
            a138.pro_bd                    pro_bd,
            a138.pro_prod                  pro_prod,
            a138.pro_rate                  pro_rate,
            a138.pro_um                    pro_um,
            a138.pro_qty                   pro_qty,
            a138.pro_case                  pro_case,
            a138.pro_dis1                  pro_dis1,
            a138.pro_dis2                  pro_dis2,
            a138.pro_dis3                  pro_dis3,
            a138.pro_netvl                 pro_netvl,
            a138.pro_exc                   pro_exc,
            a138.pro_excd                  pro_excd,
            a138.pro_txcd                  pro_txcd,
            a138.pro_div                   pro_div,
            a138.pro_mset                  pro_mset,
            a138.pro_val                   pro_val,
            a138.pro_zncd                  pro_zncd,
            a138.pro_dicd                  pro_dicd,
            a138.pro_slcd                  pro_slcd,
            a138.pro_lac                   pro_lac,
            a138.pro_order_number          pro_order_number,
            a138.pro_header_id             pro_header_id,
            a138.pro_line_id               pro_line_id,
            a138.pro_delivery_id           pro_delivery_id,
            a138.pro_delivery_detail_id    pro_delivery_detail_id,
            a138.pro_customer_id           pro_customer_id,
            a138.pro_cust_name             pro_cust_name,
            a138.pro_prod_desc             pro_prod_desc,
            a138.pro_order_type_id         pro_order_type_id,
            a138.pro_order_type_name       pro_order_type_name,
            a138.pro_inventory_item_id     pro_inventory_item_id,
            a138.pro_area                  pro_area,
            a138.pro_bv_code               pro_bv_code,
            a138.pro_cust_class            pro_cust_class,
            a138.pro_netvl_with_dis        pro_netvl_with_dis,
            a138.pro_area_old              pro_area_old,
            a138.pro_qtyv                  pro_qtyv,
            a138.pro_warranty_val          pro_warranty_val
        FROM
            (
                SELECT /*+ NO_UNNEST PUSH_SUBQ */ 
                                                                        a73.pro_key                                            pro_key,
                    a73.pro_fy                                             pro_fy,
                    a73.pro_my                                             pro_my,
                    a73.pro_aa                                             pro_aa,
                    a73.pro_pg                                             pro_pg,
                    a73.pro_ut                                             pro_ut,
                    a73.tax_invoice_num                                    pro_no,
                    a73.pro_srno                                           pro_srno,
                    a73.pro_rect                                           pro_rect,
                    a73.pro_dt                                             pro_dt,
                    a73.pro_led                                            pro_led,
                    a73.pro_cust                                           pro_cust,
                    a73.pro_mut                                            pro_mut,
                    a73.pro_mdiv                                           pro_mdiv,
                    a73.pro_bd                                             pro_bd,
                    a73.pro_prod                                           pro_prod,
                    a73.pro_rate1                                          pro_rate,
                    a73.pro_um                                             pro_um,
                    a73.pro_qty                                            pro_qty,
                    a73.pro_case                                           pro_case,
                    a73.pro_dis1                                           pro_dis1,
                    a73.pro_dis2                                           pro_dis2,
                    a73.pro_dis3                                           pro_dis3,
                    a73.pro_netvl                                          pro_netvl,
                    a73.pro_exc                                            pro_exc,
                    a73.pro_excd                                           pro_excd,
                    a73.pro_txcd                                           pro_txcd,
                    a73.pro_div                                            pro_div,
                    a73.pro_mset                                           pro_mset,
                    SUM(a73.pro_val + nvl(a73.tcs_tax_val, 0))             pro_val,
                    a73.pro_zncd                                           pro_zncd,
                    a73.pro_dicd                                           pro_dicd,
                    a73.pro_slcd                                           pro_slcd,
                    a73.pro_lac                                            pro_lac,
                    a73.pro_order_number                                   pro_order_number,
                    a73.pro_header_id                                      pro_header_id,
                    a73.pro_line_id                                        pro_line_id,
                    a73.pro_delivery_id                                    pro_delivery_id,
                    a73.pro_delivery_detail_id                             pro_delivery_detail_id,
                    a73.pro_customer_id                                    pro_customer_id,
                    a73.pro_cust_name                                      pro_cust_name,
                    a73.pro_prod_desc                                      pro_prod_desc,
                    a73.pro_order_type_id                                  pro_order_type_id,
                    a73.pro_order_type_name                                pro_order_type_name,
                    a73.pro_inventory_item_id                              pro_inventory_item_id,
                    a73.pro_area                                           pro_area,
                    a73.pro_bv_code                                        pro_bv_code,
                    a73.pro_cust_class                                     pro_cust_class,
                    a73.pro_netvl_with_dis                                 pro_netvl_with_dis,
                    a73.pro_area_old                                       pro_area_old,
                    a73.pro_qtyv                                           pro_qtyv,
                    SUM(a73.warranty_qty * a73.warranty_amt)               pro_warranty_val
                FROM
                    (
                        WITH a84 AS (
                            SELECT /*+ NO_UNNEST PUSH_SUBQ push_pred*/ 
                                a2.dis1             dis1,
                                a2.dis2             dis2,
                                a2.line_id          line_id,
                                a2.header_id + 0    header_id
                            FROM
                                (
                                    SELECT /*+ NO_UNNEST PUSH_SUBQ */ 
                                        a93.org_id               org_id,
                                        a93.order_number         order_number,
                                        a93.header_id            header_id,
                                        a93.line_id              line_id,
                                        a93.inventory_item_id    inventory_item_id,
                                        a93.ordered_item         ordered_item,
                                        a93.new_price            new_price,
                                        a93.dis1                 dis1,
                                        a93.dis2                 dis2
                                    FROM
                                        (
                                            SELECT /*+ NO_UNNEST PUSH_SUBQ */ 
                                                                                                                                                                                            a94.org_id               org_id,
                                                a94.order_number         order_number,
                                                a94.header_id            header_id,
                                                a94.line_id              line_id,
                                                a94.inventory_item_id    inventory_item_id,
                                                a94.ordered_item         ordered_item,
                                                SUM(a94.new_price)       new_price,
                                                SUM(a94.dis1)            dis1,
                                                SUM(a94.dis2)            dis2
                                            FROM
                                                (
                                                    SELECT /*+ NO_UNNEST PUSH_SUBQ */ 
                                                                                                                                                                                                                            a95.org_id                   org_id,
                                                        a95.order_number             order_number,
                                                        a95.header_id                header_id,
                                                        a95.line_id                  line_id,
                                                        a95.inventory_item_id        inventory_item_id,
                                                        a95.ordered_item             ordered_item,
                                                        nvl(a95.new_price, 0)        new_price,
                                                        nvl(a95.dis1, 0)             dis1,
                                                        nvl(a95.dis2, 0)             dis2
                                                    FROM
                                                        (
                                                            ( SELECT /*+ NO_UNNEST 
                                                                         PUSH_SUBQ
                                                                         push_pred
                                                                         use_nl(a119 a118)
                                                                         use_nl(a119 a117)
                                                                     */
                                                                a119.org_id                                                          org_id,
                                                                a118.order_number                                                    order_number,
                                                                a119.header_id                                                       header_id,
                                                                a119.line_id                                                         line_id,
                                                                a119.inventory_item_id + 0                                           inventory_item_id,
                                                                a119.ordered_item                                                    ordered_item,
                                                                a117.modifier_level_code                                             modifier_level_code,
                                                                decode(a117.arithmetic_operator, 'NEWPRICE', a117.operand)           new_price,
                                                                NULL                                                                 dis1,
                                                                NULL                                                                 dis2
                                                            FROM
                                                                ont.oe_order_lines_all      a119,
                                                                ont.oe_order_headers_all    a118,
                                                                ont.oe_price_adjustments    a117
                                                            WHERE
                                                                    a118.org_id = a119.org_id
                                                                AND a118.header_id = a119.header_id + 0
                                                                AND a119.header_id + 0 = a117.header_id
                                                                AND a119.line_id + 0 = a117.line_id
                                                                AND a117.line_id IS NOT NULL
                                                                AND a117.list_line_type_code <> 'SUR'
                                                                AND a117.arithmetic_operator = 'NEWPRICE'
                                                            )
                                                            UNION ALL
                                                            ( SELECT  /*+ NO_UNNEST PUSH_SUBQ
                                                                      */
                                                                                                                                                                                                                                                            a116.
                                                                org_id                 org_id,
                                                                a116.order_number           order_number,
                                                                a116.header_id              header_id,
                                                                a116.line_id                line_id,
                                                                a116.inventory_item_id      inventory_item_id,
                                                                a116.ordered_item           ordered_item,
                                                                a116.modifier_level_code    modifier_level_code,
                                                                a116.new_price              new_price,
                                                                a116.dis1                   dis1,
                                                                a116.dis2                   dis2
                                                            FROM
                                                                (
                                                                    SELECT /*+ NO_UNNEST PUSH_SUBQ */
                                                                                                                                                                                                                                                                                            a97.
                                                                        org_id org_id,
                                                                        a97.order_number                                             order_number,
                                                                        a97.header_id                                                header_id,
                                                                        a97.line_id                                                  line_id,
                                                                        a97.inventory_item_id                                        inventory_item_id,
                                                                        a97.ordered_item                                             ordered_item,
                                                                        a97.modifier_level_code                                      modifier_level_code,
                                                                        decode(a97.arithmetic_operator, 'NEWPRICE',
                                                                               a97.operand)                                          new_price,
                                                                        decode(a97.pricing_group_sequence, 1, a97.operand)           dis1,
                                                                        decode(a97.pricing_group_sequence, 2, a97.operand)           dis2
                                                                    FROM
                                                                        (
                                                                            SELECT /*+ NO_UNNEST PUSH_SUBQ 
                                                                                       USE_NL (A98 A99) 
                                                                                    */ 
                                                                                                                                                                                                                                                                                                                            a99.
                                                                                header_id header_id,
                                                                                a99.line_id                   line_id,
                                                                                a99.org_id                    org_id,
                                                                                a99.order_number              order_number,
                                                                                a99.operand                   operand,
                                                                                a99.modifier_level_code       modifier_level_code,
                                                                                a99.arithmetic_operator       arithmetic_operator,
                                                                                a99.pricing_group_sequence    pricing_group_sequence,
                                                                                a99.pricing_phase_id          pricing_phase_id,
                                                                                a99.list_header_id            list_header_id,
                                                                                a99.inventory_item_id         inventory_item_id,
                                                                                a99.ordered_item              ordered_item,
                                                                                a98.recno                     recno
                                                                            FROM
                                                                                (
                                                                                    ( SELECT /*+ NO_UNNEST PUSH_SUBQ USE_NL (a115 A113) USE_NL ( A113 A114)  */ 
                                                                                                                                                                                                                                                                                                                                                            a115.
                                                                                        org_id org_id,
                                                                                        a114.order_number              order_number,
                                                                                        a115.header_id                 header_id,
                                                                                        a115.line_id                   line_id,
                                                                                        a115.inventory_item_id + 0     inventory_item_id,
                                                                                        a115.ordered_item              ordered_item,
                                                                                        a113.operand                   operand,
                                                                                        a113.modifier_level_code       modifier_level_code,
                                                                                        a113.arithmetic_operator       arithmetic_operator,
                                                                                        a113.pricing_group_sequence    pricing_group_sequence,
                                                                                        a113.pricing_phase_id          pricing_phase_id,
                                                                                        a113.list_header_id            list_header_id,
                                                                                        a113.list_line_id              list_line_id
                                                                                    FROM
                                                                                        ont.oe_order_lines_all      a115,
                                                                                        ont.oe_order_headers_all    a114,
                                                                                        ont.oe_price_adjustments    a113
                                                                                    WHERE
                                                                                            a114.org_id = a115.org_id
                                                                                        AND a114.header_id = a115.header_id + 0
                                                                                        AND a115.header_id + 0 = a113.header_id
                                                                                        AND a113.line_id IS NULL
                                                                                        AND a113.arithmetic_operator <> 'NEWPRICE'
                                                                                    )
                                                                                    UNION ALL
                                                                                    ( SELECT /*+ NO_UNNEST PUSH_SUBQ 
                                                                                                 USE_NL (A112 A111) USE_NL (A112 A110) */ 
                                                                                                                                                                                                                                                                                                                                                            a112.
                                                                                        org_id org_id,

Chris Saxon
August 24, 2022 - 1:14 pm UTC

What are the "good" and "bad" execution plans?

What happens if you remove all the hints?

Execution plan resulted without Push Predicate

Krishna Kumar, August 24, 2022 - 2:52 pm UTC

The Good execution plan is expected to have "View Pushed Predicate" Window happening on the inline views (With indexed scans)

But it is just converted into "View" window having full table scan on driving table (Within inline view) which is causing huge burden

Once we take out the transformed query, fine tune, drop and recreate the materialized view without using MV Upgrade, everything seems to working right and refresh happens in seconds.

Tried MV upgrade on the fine tuned transformed query but the result is same, my entire hints gone for toss. Is there any way that we could retain the Hinted query as is while MV upgrade.

The bad thing is there is a restriction of Internet access while using VPN, I'd be posting Execution plans once I generate on these queries in short while.


Without Hints the execution plan

Krishna Kumar, August 24, 2022 - 2:55 pm UTC

Dears,

By removing all the hints and MV upgrade, the transformed query forces Full table scans inline views.

Thanks!
Krishna Kumar
Connor McDonald
August 25, 2022 - 2:52 am UTC

This sounds like you might have run into a limitation (or bug) with "ad_zd_mview"

I think you'll need a to log a call with Support so that the eBiz people can take a look at it.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.