PAXAUGLT AUD: Cost Audit Report Performance Issues (Doc ID 2759079.1)

Last updated on MARCH 04, 2024

Applies to:

Symptoms

Performance issues with AUD: Cost Audit Report.

Navigate: Other -> Requests -> Run
Select and run AUD: Cost Audit Report

Report is taking over six hours to complete successfully.
Other times the report completes in error - 'snapshot too old'.

Problem SQL:

SELECT *
FROM
 ( SELECT /*+ push_pred(cst) use_nl(cc,cst) */ cst.code_combination_id ,
  project_number , task_number , SUBSTR ( expenditure_type , 1 , 22 )
  expenditure_type , expenditure_item_date , SUBSTR ( emp_or_org_name , 1 ,
  32 ) emp_or_org_name , period_name , transferred_date , gl_dr_amount ,
  gl_cr_amount , cr_code_combination_id FROM PA_GL_COST_XFER_AUDIT_V cst
  WHERE : cp_gl_start is NULL AND TRUNC ( transferred_date ) BETWEEN nvl (
  TRUNC ( : cf_from_date ) , TRUNC ( transferred_date ) ) AND nvl ( TRUNC ( :
  cf_to_date ) , TRUNC ( transferred_date ) ) AND EXISTS ( SELECT 1 FROM
  gl_code_combinations CC WHERE CC.SEGMENT1 BETWEEN '0000000000' AND
  'ZZZZZZZZZZ' AND CC.SEGMENT2 BETWEEN '0000' AND 'ZZZZ' AND CC.SEGMENT3
  BETWEEN '0000000000' AND 'ZZZZZZZZZZ' AND CC.SEGMENT4 BETWEEN '0000000000'
  AND 'ZZZZZZZZZZ' AND CC.SEGMENT5 BETWEEN '00000' AND 'ZZZZZ' AND
  CC.SEGMENT6 = '61003600' AND CC.SEGMENT7 BETWEEN '0000000000' AND
  'ZZZZZZZZZZ' AND CC.SEGMENT8 BETWEEN '0000000000' AND 'ZZZZZZZZZZ' AND
  CC.SEGMENT9 BETWEEN '0000000000' AND 'ZZZZZZZZZZ' AND CC.SEGMENT10 BETWEEN
  '0000000000' AND 'ZZZZZZZZZZ' and CC.code_combination_id =
  cst.code_combination_id ) UNION ALL SELECT /*+ push_pred(cst) use_nl(cc,cst)
   */ cst.code_combination_id , project_number , task_number , SUBSTR (
  expenditure_type , 1 , 22 ) expenditure_type , expenditure_item_date ,
  SUBSTR ( emp_or_org_name , 1 , 32 ) emp_or_org_name , period_name ,
  transferred_date , gl_dr_amount , gl_cr_amount , cr_code_combination_id
  FROM PA_GL_COST_XFER_AUDIT_V cst WHERE : cp_gl_start is NOT NULL AND
  gl_date BETWEEN : cp_gl_start AND : cp_gl_end AND TRUNC ( transferred_date )
   BETWEEN nvl ( TRUNC ( : cf_from_date ) , TRUNC ( transferred_date ) ) AND
  nvl ( TRUNC ( : cf_to_date ) , TRUNC ( transferred_date ) ) AND EXISTS (
  SELECT 1 FROM gl_code_combinations CC WHERE CC.SEGMENT1 BETWEEN
  '0000000000' AND 'ZZZZZZZZZZ' AND CC.SEGMENT2 BETWEEN '0000' AND 'ZZZZ' AND
  CC.SEGMENT3 BETWEEN '0000000000' AND 'ZZZZZZZZZZ' AND CC.SEGMENT4 BETWEEN
  '0000000000' AND 'ZZZZZZZZZZ' AND CC.SEGMENT5 BETWEEN '00000' AND 'ZZZZZ'
  AND CC.SEGMENT6 = '61003600' AND CC.SEGMENT7 BETWEEN '0000000000' AND
  'ZZZZZZZZZZ' AND CC.SEGMENT8 BETWEEN '0000000000' AND 'ZZZZZZZZZZ' AND
  CC.SEGMENT9 BETWEEN '0000000000' AND 'ZZZZZZZZZZ' AND CC.SEGMENT10 BETWEEN
  '0000000000' AND 'ZZZZZZZZZZ' and CC.code_combination_id =
  cst.code_combination_id ) )  ORDER BY 1 ASC , code_combination_id ,
  project_number , task_number , expenditure_type , expenditure_item_date

Changes

 Upgraded form 12.2.6 to 12.2.9.

Cause

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