Our inventory query returns the data after too much of processing time (7 hours). It is a straight forward approach at the query level, we have used two aggregate functions, and less than 3 subqueries in our entire report. The catch here is that the output contains huge bucket of data (Around 40Cr+).
Is there any method in addressing to reduce the amount of processing time of the report to fetch the data.
Requiring few opinions and alternative method to increase the performance and reduce the processing time?
Attaching the query below, kindly help with solving the issue that is present here.
select unit_of_measure1,sum(QTY1) QTY1,sum(unit_cost1) unit_cost1,val_unit_code1,item_code1,item_description1,ORGANIZATION_CODE1,SUBINVENTORY_CODE1,:P_SEGMENT1 ACCT1
from (select (select unit_of_measure from inv_units_of_measure_vl where uom_code=a_UOM_code) unit_of_measure1,
b_total_quantity QTY1,
ROUND(nvl( A_unit_cost,0)*b_total_quantity,2) unit_cost1,
-- ROUND(nvl(decode (A_cost_method_code, 'ACTUAL', A_onhand_value / (decode(c_vu_quantity, 0, decode(b_total_quantity, 0, 1, b_total_quantity), c_vu_quantity)), A_unit_cost),0)* b_total_quantity,2) unit_cost1,
(SELECT VAL_UNIT_CODE FROM CST_VAL_UNITS_B WHERE VAL_UNIT_ID = a_VAL_UNIT_ID) val_unit_code1,
( select item_number from EGP_SYSTEM_ITEMS_VL where ORGANIZATION_ID=inv_org_id and INVENTORY_ITEM_ID=inv_item_id) AS item_code1,
( select description from EGP_SYSTEM_ITEMS_VL where ORGANIZATION_ID=inv_org_id and INVENTORY_ITEM_ID=inv_item_id) AS item_description1,
(SELECT ORGANIZATION_CODE FROM CST_ORGANIZATION_DEFINITIONS_V WHERE ORGANIZATION_ID = inv_org_id) ORGANIZATION_CODE1,
b_SUBINVENTORY_CODE SUBINVENTORY_CODE1,
:P_SEGMENT1 ACCT1
from (select dist.*,xal.SUPP_REF_VALUES,xal.SR6 inv_org_id,xal.SR3 inv_item_id,
clc.val_unit_id a_val_unit_id,
--clc.cost_method_code a_cost_method_code,
clc.uom_code a_UOM_code,
clc.onhand_value a_onhand_value,
clc.unit_cost a_unit_cost,
clc.QUANTITY b_total_quantity,
CIT.SUBINVENTORY_CODE b_SUBINVENTORY_CODE,
(select quantity from fusion.cst_costed_vu_onhand_v where
cost_org_id=:P_CST_ORG
and cost_book_id=:P_CST_BOOK and inventory_item_id=xal.SR3
and cd.EFF_DATE BETWEEN snapshot_date AND eff_to_date and val_unit_id=ct.val_unit_id and rownum=1) c_vu_quantity,cd.eff_date,cit.subinventory_code,cit.locator_id
from gl_code_combinations gcc
--xla_ae_headers xah
,xla_ae_lines xal
,CST_COST_DISTRIBUTIONS cd
,CST_COST_DISTRIBUTION_LINES dist
,CST_TRANSACTIONS CT
,CST_INV_TRANSACTIONS CIT
,cst_layer_costs clc
where 1=1
/*and d.SR4=ct.cost_org_id
and d.SR1=ct.cost_book_id
and d.SR3=ct.inventory_item_id
and d.SR6=ct.inventory_org_id
and d.code_combination_id=gcc.code_combination_id
and d.PERIOD_NAME=gp.period_name
and ct.cost_date between gp.START_DATE and gp.end_date*/
and dist.sla_code_combination_id=gcc.code_combination_id
and gcc.segment2=:P_SEGMENT1
--and xah.application_id = 707
--AND xah.ae_header_id = xal.ae_header_id
-- AND xah.application_id = xal.application_id
-- AND xah.accounting_entry_status_code = 'F'
--AND xal.analytical_balance_flag = 'Y'
AND dist.ae_header_id = xal.ae_header_id
AND dist.ae_line_num = xal.ae_line_num
and dist.distribution_id=cd.distribution_id
and xal.SR4=:P_CST_ORG
and xal.SR1=:P_CST_BOOK
and xal.ACCOUNTING_DATE <=nvl(:p_as_of_date, SYSDATE)
and cd.transaction_id=CT.transaction_id
AND CT.CST_INV_TRANSACTION_ID=CIT.CST_INV_TRANSACTION_ID
and clc.LAYER_COST_ID=dist.cost_id
and NVL(clc.QUANTITY,0)<>0
))
where 1=1
having sum(unit_cost1) <>0
group by unit_of_measure1,val_unit_code1,item_code1,item_description1,ORGANIZATION_CODE1,SUBINVENTORY_CODE1