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 |
|
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 #678711 is a reply to message #678708] |
Wed, 01 January 2020 06:17 |
John Watson
Messages: 8932 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 ) );
|
|
|
Goto Forum:
Current Time: Tue May 21 10:17:38 CDT 2024
|