One of the sql main filter condition is causing performance issue .When i comment out that filter part query return first 1000 record in 3 min because of that ooh.booked_date
AND ( ----booked in the selected period
( TRUNC (
NVL (line_dates.booked_date, ooh.booked_date)) >=
gl_period.start_date
AND TRUNC (
NVL (line_dates.booked_date, ooh.booked_date)) <
gl_period.end_date + 1
AND ( ----not cancelled
TRUNC (line_dates.cancelled_date) IS NULL
OR ---- cancelled during or after the selected period
(TRUNC (line_dates.cancelled_date) >=
gl_period.start_date)))
OR ---- booked previously
( TRUNC (
NVL (line_dates.booked_date, ooh.booked_date)) <
gl_period.start_date
---- cancelled in the selected period
AND TRUNC (line_dates.cancelled_date) >=
gl_period.start_date
AND TRUNC (line_dates.cancelled_date) <
gl_period.end_date + 1))
Full sql is
SELECT 1
FROM APPS.mtl_categories_b soldrmt_c,
APPS.mtl_item_categories soldrmt_ic,
APPS.mtl_categories_b solddivarg_c,
APPS.mtl_item_categories solddivarg_ic,
APPS.mtl_categories_b c3,
APPS.mtl_item_categories ic3,
APPS.mtl_categories_b starrmt_c,
APPS.mtl_item_categories starrmt_ic,
APPS.mtl_categories_b stardivarg_c,
APPS.mtl_item_categories stardivarg_ic,
APPS.mtl_system_items_b cto_item,
APPS.oe_order_lines_all cto_ol,
APPS.oe_order_lines_all ool,
APPS.xxom_3lp_sym_ora_order_lines lp_ool,
APPS.oe_order_headers_all ooh,
APPS.xxom_3lp_sym_ora_order_hdr lp_orderheader,
APPS.xxont_order_line_wf_dates line_dates -- ,ar.ra_customer_trx_lines_all ctl
-- ,ar.ra_customer_trx_all ct
,
APPS.gl_periods gl_period
WHERE 1 = 1
AND soldrmt_c.category_id(+) = soldrmt_ic.category_id
AND soldrmt_ic.inventory_item_id(+) = ool.inventory_item_id
AND soldrmt_ic.organization_id(+) = ool.ship_from_org_id
AND soldrmt_ic.category_set_id(+) = 1100000683 -- EMR PRODUCT LINE RMT
AND solddivarg_c.category_id(+) = solddivarg_ic.category_id
AND solddivarg_ic.inventory_item_id(+) = ool.inventory_item_id
AND solddivarg_ic.organization_id(+) = ool.ship_from_org_id
AND solddivarg_ic.category_set_id(+) = 1100000041 -- EMR DIVISION ARG
AND starrmt_c.category_id(+) = starrmt_ic.category_id
AND starrmt_ic.inventory_item_id(+) = cto_ol.inventory_item_id
AND starrmt_ic.organization_id(+) = cto_ol.ship_from_org_id
AND starrmt_ic.category_set_id(+) = 1100000683 -- EMR PRODUCT LINE RMT
AND stardivarg_c.category_id(+) = stardivarg_ic.category_id
AND stardivarg_ic.inventory_item_id(+) =
cto_ol.inventory_item_id
AND stardivarg_ic.organization_id(+) = cto_ol.ship_from_org_id
AND stardivarg_ic.category_set_id(+) = 1100000041 -- EMR DIVISION ARG
AND c3.category_id(+) = ic3.category_id
AND ic3.inventory_item_id(+) = ool.inventory_item_id
AND ic3.organization_id(+) = ool.ship_from_org_id
AND ic3.category_set_id(+) = 1100000680 -- EMR MAJOR MODEL RMT
AND cto_item.organization_id(+) = cto_ol.ship_from_org_id
AND cto_item.inventory_item_id(+) = cto_ol.inventory_item_id
AND cto_ol.item_type_code(+) = 'CONFIG'
AND cto_ol.top_model_line_id(+) = ool.line_id
AND ( EXISTS
(SELECT 'X'
FROM APPS.xxom_3lp_sym_ora_order_lines LP_LINE
WHERE LP_LINE.HEADER_ID = OOL.HEADER_ID
AND LP_LINE.PARENTLINENBR =
OOL.LINE_NUMBER
AND EXISTS
(SELECT 1
FROM APPS.OE_ORDER_LINES_ALL
LINES
WHERE LINES.LINE_ID =
LP_LINE.LINE_ID)
AND ( ( LP_LINE.MODEL_STRING LIKE
'1410%'
OR LP_LINE.MODEL_STRING LIKE
'1420%'
OR LP_LINE.MODEL_STRING LIKE
'701P%'
OR LP_LINE.MODEL_STRING LIKE
'WPG%'
OR LP_LINE.MODEL_STRING LIKE
'628%'
OR LP_LINE.MODEL_STRING LIKE
'7001X%'
OR LP_LINE.MODEL_STRING LIKE
'1552WU%')
OR ( LP_LINE.MODEL_STRING LIKE
'%WA3%'
AND ( LP_LINE.MODEL_STRING LIKE
'781%'
OR LP_LINE.MODEL_STRING LIKE
'702%'
OR LP_LINE.MODEL_STRING LIKE
'705%'
OR LP_LINE.MODEL_STRING LIKE
'708%'
OR LP_LINE.MODEL_STRING LIKE
'775%'
OR LP_LINE.MODEL_STRING LIKE
'248%'
OR LP_LINE.MODEL_STRING LIKE
'648%'
OR LP_LINE.MODEL_STRING LIKE
'848%'
OR LP_LINE.MODEL_STRING LIKE
'2051C%'
OR LP_LINE.MODEL_STRING LIKE
'2051L%'
OR LP_LINE.MODEL_STRING LIKE
'2051T%'
OR LP_LINE.MODEL_STRING LIKE
'3051C%'
OR LP_LINE.MODEL_STRING LIKE
'3051L%'
OR LP_LINE.MODEL_STRING LIKE
'3051SAL%'
OR LP_LINE.MODEL_STRING LIKE
'3051SC%'
OR LP_LINE.MODEL_STRING LIKE
'3051SHP%'
OR LP_LINE.MODEL_STRING LIKE
'3051ST%'
OR LP_LINE.MODEL_STRING LIKE
'3051T%'
OR LP_LINE.MODEL_STRING LIKE
'2051CFA%'
OR LP_LINE.MODEL_STRING LIKE
'2051CFC%'
OR LP_LINE.MODEL_STRING LIKE
'2051CFP%'
OR LP_LINE.MODEL_STRING LIKE
'3051CFA%'
OR LP_LINE.MODEL_STRING LIKE
'3051CFC%'
OR LP_LINE.MODEL_STRING LIKE
'3051CFP%'
OR LP_LINE.MODEL_STRING LIKE
'3051SFA%'
OR LP_LINE.MODEL_STRING LIKE
'3051SFC%'
OR LP_LINE.MODEL_STRING LIKE
'3051SFP%'
OR LP_LINE.MODEL_STRING LIKE
'3051SMV%'
OR LP_LINE.MODEL_STRING LIKE
'2160%'
OR LP_LINE.MODEL_STRING LIKE
'3308A%'
OR LP_LINE.MODEL_STRING LIKE
'928%'))))
OR EXISTS
(SELECT 'X'
FROM APPS.oe_order_lines_all l,
APPS.mtl_system_items_b msi
WHERE ool.header_id = l.header_id
AND ool.line_number = l.line_number
AND l.inventory_item_id =
msi.inventory_item_id
AND l.ship_from_org_id =
msi.organization_id
AND msi.item_type = 'WIRE') -- OR nvl(lp_ool.model_string, ool.ordered_item) IN (
-- '701PBKKF'
-- ,'701PGNKF'
-- )
OR NVL (starrmt_c.segment1, soldrmt_c.segment1) IN
('JWA',
'JWB',
'JWC',
'JWD',
'JWE',
'JKC',
'JKE',
'JKG',
'JWF',
'JWG',
'JWH',
'JWJ',
'JWK')
OR NVL (stardivarg_c.segment2, solddivarg_c.segment2) IN
('JWA',
'JWB',
'JWC',
'JWD',
'JWE',
'JKC',
'JKE',
'JKG',
'JWF',
'JWG',
'JWH',
'JWJ',
'JWK')
OR ool.ordered_item LIKE 'A9530V%')
AND (:base_model = 'All' OR ool.ordered_item LIKE :base_model)
AND ( 'All' IN (:shipped_from)
OR ool.ship_from_org_id IN (:shipped_from))
AND lp_orderheader.header_id = ooh.header_id
AND lp_ool.line_id = ool.line_id
-- AND ct.customer_trx_id(+) = ctl.customer_trx_id
-- AND ctl.interface_line_attribute6(+) = to_char(ool.line_id)
-- AND (
-- ct.interface_header_context = 'ORDER ENTRY'
-- OR ct.interface_header_context = 'INTERCOMPANY'
-- OR ct.interface_header_context IS NULL
-- )
-- and ct.org_id in (:OrgId)
AND line_dates.line_id(+) =
NVL (ool.top_model_line_id, ool.line_id)
-- AND ool.link_to_line_id IS NULL
AND ( ool.link_to_line_id IS NULL
OR ool.link_to_line_id = ool.top_model_line_id)
AND ool.header_id = ooh.header_id
-- AND ooh.demand_class_code <> 'INTERNAL_RMT'
AND ooh.org_id IN (:OrgId)
AND OOL.org_id IN (:OrgId)
AND ( ----booked in the selected period
( TRUNC (
NVL (line_dates.booked_date, ooh.booked_date)) >=
gl_period.start_date
AND TRUNC (
NVL (line_dates.booked_date, ooh.booked_date)) <
gl_period.end_date + 1
AND ( ----not cancelled
TRUNC (line_dates.cancelled_date) IS NULL
OR ---- cancelled during or after the selected period
(TRUNC (line_dates.cancelled_date) >=
gl_period.start_date))
)
OR ---- booked previously
( TRUNC (
NVL (line_dates.booked_date, ooh.booked_date)) <
gl_period.start_date
---- cancelled in the selected period
AND TRUNC (line_dates.cancelled_date) >=
gl_period.start_date
AND TRUNC (line_dates.cancelled_date) <
gl_period.end_date + 1))
AND gl_period.period_num IN (:Period)
AND gl_period.period_year IN (:Year)
AND gl_period.period_type = '1' -- Fiscal Monthly
AND gl_period.period_set_name = 'Fiscal 4-4-5'
AND ool.ordered_item NOT LIKE 'OC-%'