Period Close Reconciliation Report (CSTRPCRE) Performance Is Not Completing Even After Running For Days (Doc ID 2112149.1)

Last updated on MAY 05, 2021

Applies to:

Symptoms

On : 12.2.4 version, Performance

ACTUAL BEHAVIOR
---------------
Running the Period Close Reconciliation Report yields slow performance

A review of traces has indicated following query at fault;

SELECT CCG . cost_group cg_name , CCG . description cg_description ,
  MSI.CONCATENATED_SEGMENTS cg_item , round ( SUM ( CPCS . accounted_value )
  * : P_EXCHANGE_RATE / : P_ROUND_UNIT ) * : P_ROUND_UNIT cg_acc_val , round
  ( SUM ( CPCS . rollback_value ) * : P_EXCHANGE_RATE / : P_ROUND_UNIT ) * :
  P_ROUND_UNIT cg_onh_val , round ( SUM ( CPCS . rollback_value - CPCS .
  accounted_value ) * : P_EXCHANGE_RATE / : P_ROUND_UNIT ) * : P_ROUND_UNIT
  cg_adj_val
FROM
 cst_cost_groups CCG , mtl_system_items_b_kfv MSI , cst_period_close_summary
  CPCS WHERE CPCS.cost_group_id = CCG.cost_group_id (+) AND
  CPCS.inventory_item_id = MSI.inventory_item_id AND MSI.organization_id = :
  p_org_id and 1 = 1 AND NVL ( CCG.cost_group , - 1 ) BETWEEN NVL ( :
  P_CG_FROM , NVL ( CCG.cost_group , - 1 ) ) AND NVL ( : P_CG_TO , NVL (
  CCG.cost_group , - 1 ) ) AND CPCS.organization_id = : p_org_id AND
  CPCS.acct_period_id = : p_period_id GROUP BY CCG.cost_group ,
  CCG.description , MSI.CONCATENATED_SEGMENTS ORDER BY 1 ASC,2 ASC , cg_name ,
  cg_item


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 21619 17.04 595.00 89866 918367 0 172948
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21621 17.04 595.00 89866 918367 0 172948

EXPECTED BEHAVIOR
-----------------------
More reasonable response

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.