Home » SQL & PL/SQL » SQL & PL/SQL » WITH Clause VS INLINE View Execution (Oracle 12C)
WITH Clause VS INLINE View Execution [message #678708] Mon, 30 December 2019 23:13 Go to next message
ashrubhg@gmail.com
Messages: 1
Registered: March 2017
Junior Member
Hi All,



I am running below query to retrieve Item information by using "WITH" clause for faster execution of the query.

Below query ran well and generated output for 8 days with minimum execution time around 1 minute.

All of sudden it is running for more than 3 hours and giving Snap shot too old error.



Out Initial check on new changes and new data was not successful as there is no code fixes moved and the data got updated,

Can anybody kindly suggest how to identify if any specific newly introduced data causing this query to run very long?



The below query executed and gave results when we modified the query by replacing with clauses with INLINE views. Appreciate if anybody can help to understand why WITH clause query taking time all of sudden and what is the difference that INLINE view query able to generate results.



WITH ORG_ATRIBUTES AS

(SELECT EMSIB.INVENTORY_ITEM_ID ,

EMSIB.ORGANIZATION_ID ,

NVL(EMSIB.C_EXT_ATTR1,'N') STOCKABLE_ITEM ,

NVL(EMSIB.C_EXT_ATTR2,'N') NON_STANDARD_ITEM

FROM EGO_MTL_SY_ITEMS_EXT_B EMSIB ,

EGO_ATTR_GROUPS_V EAGV1

WHERE EAGV1.ATTR_GROUP_ID = EMSIB.ATTR_GROUP_ID

AND ATTR_GROUP_NAME = 'NUC_ITEM_ORG_ATTRIBUTES'

),

ORG_VIEW AS

(SELECT MP.ORGANIZATION_CODE ,

MP.ORGANIZATION_ID ,

TO_NUMBER(HOI.ORG_INFORMATION3) ORG_ID,

MP.CALENDAR_CODE

FROM MTL_PARAMETERS MP ,

HR_ORGANIZATION_INFORMATION HOI

WHERE HOI.ORGANIZATION_ID = MP.ORGANIZATION_ID

AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information'

),

ITEM_ATTR AS

(SELECT MICV.INVENTORY_ITEM_ID,

MICV.ORGANIZATION_ID,

FFVL.DESCRIPTION ITEM_PRODTYPE,

MICV.SEGMENT2 ITEM_SHAPE,

MICV.SEGMENT3 ITEM_GRADE,

MICV.SEGMENT4 ITEM_LENGTH,

MICV.SEGMENT19 ITEM_SIZE

FROM MTL_ITEM_CATEGORIES_V MICV ,

EGO_MTL_SY_ITEMS_EXT_B EMSIEB ,

MTL_ITEM_CATALOG_GROUPS_V MICGV ,

EGO_DATA_LEVEL_B EDLB ,

EGO_ATTR_GROUPS_V EAGV ,

EGO_ATTRS_V EAV ,

FND_FLEX_VALUES_VL FFVL ,

FND_FLEX_VALUE_SETS FFVS

WHERE EMSIEB.ATTR_GROUP_ID = EAGV.ATTR_GROUP_ID

AND EMSIEB.DATA_LEVEL_ID = EDLB.DATA_LEVEL_ID

AND EDLB.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'

AND EDLB.DATA_LEVEL_NAME = 'ITEM_LEVEL'

AND EAGV.ATTR_GROUP_NAME = EAV.ATTR_GROUP_NAME

AND EAV.ATTR_NAME = 'PRODTYPE'

AND EAV.VALUE_SET_NAME <>'EGO_YES_NO'

AND MICV.INVENTORY_ITEM_ID = EMSIEB.INVENTORY_ITEM_ID

AND EMSIEB.ITEM_CATALOG_GROUP_ID = MICGV.ITEM_CATALOG_GROUP_ID

AND MICV.CATEGORY_SET_NAME = 'NUC Item Categories'

AND FFVS.FLEX_VALUE_SET_NAME = EAV.VALUE_SET_NAME

AND FFVS.FLEX_VALUE_SET_ID = FFVL.FLEX_VALUE_SET_ID

AND MICV.SEGMENT1 = FFVL.FLEX_VALUE

AND FFVL.ENABLED_FLAG = 'Y'

)

(SELECT ORG_VIEW.ORGANIZATION_CODE,

ORG_VIEW.ORGANIZATION_ID,

ORG_VIEW.CALENDAR_CODE,

ORG_VIEW.ORG_ID,

MSIB.INVENTORY_ITEM_ID,

MSIB.SEGMENT1,

MSIB.DESCRIPTION,

MSIB.PRIMARY_UOM_CODE,

MSIB.PRIMARY_UNIT_OF_MEASURE,

MSIB.INVENTORY_ITEM_STATUS_CODE,

MSIB.ITEM_TYPE,

MSIB.SECONDARY_UOM_CODE,

MSIB.STOCK_ENABLED_FLAG,

NVL(MOIV.TOTAL_QOH,0) ONHAND_QTY,

ITEM_ATTR.ITEM_PRODTYPE,

ITEM_ATTR.ITEM_SHAPE,

ITEM_ATTR.ITEM_GRADE,

ITEM_ATTR.ITEM_LENGTH,

ITEM_ATTR.ITEM_SIZE,

ORG_ATRIBUTES.STOCKABLE_ITEM STOCKABLE_ITEM,

ORG_ATRIBUTES.NON_STANDARD_ITEM NON_STANDARD_ITEM,

-1,

SYSDATE,

-1,

SYSDATE

FROM MTL_SYSTEM_ITEMS_B MSIB,

MTL_ONHAND_ITEMS_V MOIV,

ORG_ATRIBUTES,

ORG_VIEW,

ITEM_ATTR

WHERE MSIB.ORGANIZATION_ID = ORG_VIEW.ORGANIZATION_ID

AND MSIB.INVENTORY_ITEM_ID = MOIV.INVENTORY_ITEM_ID

AND MSIB.ORGANIZATION_ID = MOIV.ORGANIZATION_ID

AND MSIB.INVENTORY_ITEM_ID = ORG_ATRIBUTES.INVENTORY_ITEM_ID(+)

AND MSIB.ORGANIZATION_ID = ORG_ATRIBUTES.ORGANIZATION_ID(+)

AND MSIB.INVENTORY_ITEM_ID = ITEM_ATTR.INVENTORY_ITEM_ID

AND MSIB.ORGANIZATION_ID = ITEM_ATTR.ORGANIZATION_ID

AND MSIB.ORGANIZATION_ID <> 81

UNION

SELECT ORG_VIEW.ORGANIZATION_CODE,

ORG_VIEW.ORGANIZATION_ID,

ORG_VIEW.CALENDAR_CODE,

ORG_VIEW.ORG_ID,

MSIB.INVENTORY_ITEM_ID,

MSIB.SEGMENT1,

MSIB.DESCRIPTION,

MSIB.PRIMARY_UOM_CODE,

MSIB.PRIMARY_UNIT_OF_MEASURE,

MSIB.INVENTORY_ITEM_STATUS_CODE,

MSIB.ITEM_TYPE,

MSIB.SECONDARY_UOM_CODE,

MSIB.STOCK_ENABLED_FLAG,

0 ONHAND_QTY,

ITEM_ATTR.ITEM_PRODTYPE,

ITEM_ATTR.ITEM_SHAPE,

ITEM_ATTR.ITEM_GRADE,

ITEM_ATTR.ITEM_LENGTH,

ITEM_ATTR.ITEM_SIZE,

ORG_ATRIBUTES.STOCKABLE_ITEM STOCKABLE_ITEM,

ORG_ATRIBUTES.NON_STANDARD_ITEM NON_STANDARD_ITEM,

-1,

SYSDATE,

-1,

SYSDATE

FROM MTL_SYSTEM_ITEMS_B MSIB,

ORG_ATRIBUTES,

ORG_VIEW,

ITEM_ATTR

WHERE MSIB.ORGANIZATION_ID = ORG_VIEW.ORGANIZATION_ID

AND MSIB.INVENTORY_ITEM_ID = ORG_ATRIBUTES.INVENTORY_ITEM_ID

AND MSIB.ORGANIZATION_ID = ORG_ATRIBUTES.ORGANIZATION_ID

AND MSIB.INVENTORY_ITEM_ID = ITEM_ATTR.INVENTORY_ITEM_ID

AND MSIB.ORGANIZATION_ID = ITEM_ATTR.ORGANIZATION_ID

AND MSIB.ORGANIZATION_ID <> 81

AND NOT EXISTS (SELECT 1 FROM MTL_ONHAND_ITEMS_V MOIV

WHERE MSIB.INVENTORY_ITEM_ID = MOIV.INVENTORY_ITEM_ID

AND MSIB.ORGANIZATION_ID = MOIV.ORGANIZATION_ID

)

);
Re: WITH Clause VS INLINE View Execution [message #678710 is a reply to message #678708] Tue, 31 December 2019 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

For any performances question, please read http://www.orafaq.com/forum/index.php/mv/msg/206002/433888/#msg_433888 and post the required information.

Re: WITH Clause VS INLINE View Execution [message #678711 is a reply to message #678708] Wed, 01 January 2020 06:17 Go to previous message
John Watson
Messages: 8318
Registered: January 2010
Location: Global Village
Senior Member
I've formatted your code for you, but you'll have to do everything else (starting with the exec plans, perhaps) yourself if you want any assistance:

WITH org_atributes AS
(
       SELECT emsib.inventory_item_id ,
              emsib.organization_id ,
              nvl(emsib.c_ext_attr1,'N') stockable_item ,
              nvl(emsib.c_ext_attr2,'N') non_standard_item
       FROM   ego_mtl_sy_items_ext_b emsib ,
              ego_attr_groups_v eagv1
       WHERE  eagv1.attr_group_id = emsib.attr_group_id
       AND    attr_group_name = 'NUC_ITEM_ORG_ATTRIBUTES' ), org_view AS
(
       SELECT mp.organization_code ,
              mp.organization_id ,
              to_number(hoi.org_information3) org_id,
              mp.calendar_code
       FROM   mtl_parameters mp ,
              hr_organization_information hoi
       WHERE  hoi.organization_id = mp.organization_id
       AND    hoi.org_information_context = 'Accounting Information' ), item_attr AS
(
       SELECT micv.inventory_item_id,
              micv.organization_id,
              ffvl.description item_prodtype,
              micv.segment2    item_shape,
              micv.segment3    item_grade,
              micv.segment4    item_length,
              micv.segment19   item_size
       FROM   mtl_item_categories_v micv ,
              ego_mtl_sy_items_ext_b emsieb ,
              mtl_item_catalog_groups_v micgv ,
              ego_data_level_b edlb ,
              ego_attr_groups_v eagv ,
              ego_attrs_v eav ,
              fnd_flex_values_vl ffvl ,
              fnd_flex_value_sets ffvs
       WHERE  emsieb.attr_group_id = eagv.attr_group_id
       AND    emsieb.data_level_id = edlb.data_level_id
       AND    edlb.attr_group_type = 'EGO_ITEMMGMT_GROUP'
       AND    edlb.data_level_name = 'ITEM_LEVEL'
       AND    eagv.attr_group_name = eav.attr_group_name
       AND    eav.attr_name = 'PRODTYPE'
       AND    eav.value_set_name <>'EGO_YES_NO'
       AND    micv.inventory_item_id = emsieb.inventory_item_id
       AND    emsieb.item_catalog_group_id = micgv.item_catalog_group_id
       AND    micv.category_set_name = 'NUC Item Categories'
       AND    ffvs.flex_value_set_name = eav.value_set_name
       AND    ffvs.flex_value_set_id = ffvl.flex_value_set_id
       AND    micv.segment1 = ffvl.flex_value
       AND    ffvl.enabled_flag = 'Y' )
(
       SELECT org_view.organization_code,
              org_view.organization_id,
              org_view.calendar_code,
              org_view.org_id,
              msib.inventory_item_id,
              msib.segment1,
              msib.description,
              msib.primary_uom_code,
              msib.primary_unit_of_measure,
              msib.inventory_item_status_code,
              msib.item_type,
              msib.secondary_uom_code,
              msib.stock_enabled_flag,
              nvl(moiv.total_qoh,0) onhand_qty,
              item_attr.item_prodtype,
              item_attr.item_shape,
              item_attr.item_grade,
              item_attr.item_length,
              item_attr.item_size,
              org_atributes.stockable_item    stockable_item,
              org_atributes.non_standard_item non_standard_item,
              -1,
              SYSDATE,
              -1,
              SYSDATE
       FROM   mtl_system_items_b msib,
              mtl_onhand_items_v moiv,
              org_atributes,
              org_view,
              item_attr
       WHERE  msib.organization_id = org_view.organization_id
       AND    msib.inventory_item_id = moiv.inventory_item_id
       AND    msib.organization_id = moiv.organization_id
       AND    msib.inventory_item_id = org_atributes.inventory_item_id(+)
       AND    msib.organization_id = org_atributes.organization_id(+)
       AND    msib.inventory_item_id = item_attr.inventory_item_id
       AND    msib.organization_id = item_attr.organization_id
       AND    msib.organization_id <> 81
       UNION
       SELECT org_view.organization_code,
              org_view.organization_id,
              org_view.calendar_code,
              org_view.org_id,
              msib.inventory_item_id,
              msib.segment1,
              msib.description,
              msib.primary_uom_code,
              msib.primary_unit_of_measure,
              msib.inventory_item_status_code,
              msib.item_type,
              msib.secondary_uom_code,
              msib.stock_enabled_flag,
              0 onhand_qty,
              item_attr.item_prodtype,
              item_attr.item_shape,
              item_attr.item_grade,
              item_attr.item_length,
              item_attr.item_size,
              org_atributes.stockable_item    stockable_item,
              org_atributes.non_standard_item non_standard_item,
              -1,
              SYSDATE,
              -1,
              SYSDATE
       FROM   mtl_system_items_b msib,
              org_atributes,
              org_view,
              item_attr
       WHERE  msib.organization_id = org_view.organization_id
       AND    msib.inventory_item_id = org_atributes.inventory_item_id
       AND    msib.organization_id = org_atributes.organization_id
       AND    msib.inventory_item_id = item_attr.inventory_item_id
       AND    msib.organization_id = item_attr.organization_id
       AND    msib.organization_id <> 81
       AND    NOT EXISTS
              (
                     SELECT 1
                     FROM   mtl_onhand_items_v moiv
                     WHERE  msib.inventory_item_id = moiv.inventory_item_id
                     AND    msib.organization_id = moiv.organization_id ) );
Previous Topic: Pan card insert time validation (3 merged)
Next Topic: Error performing insert
Goto Forum:
  


Current Time: Fri Aug 07 23:22:20 CDT 2020