We are experiencing performance issues with job cost calculation. We would be interested to know if we are unique in having the Job Cost Calculation take hours to process 20,000 - 30,000 transactions and if RnD could consider addressing the issue identified with the specific query described below.
Detailed logging in a development environment has pin-pointed that half the time taken by the scheduled job opening this cursor:
Job_Cost_Calculation_Util_API.Get_Emp_Std_Agreements___"get_rec"
This cursor joins to 2 views (rate_agreement_detail_uiv and job_transaction_parameter_uiv) which both do expensive PIVOT WITHIN GROUP operations pivoting data from child parameter tables. Then this query takes the results of those pivoted views and tries to join to them in a complex filter. The job_transaction_parameter_uiv join is efficient because it uses transaction_id directly pushing the predicate into the view. However, the rate_agreement_detail_uiv join does full table scans to create a data set of ALL rate agreements before hash joining and filtering results. Despite the data sets being relatively small (2776 rate_agreement_detail_tab joined to 13,880 rate_agreement_parameter_tab), the PIVOT LISTAGG operation is expensive - 0.3 sec per call - if there are 20,000 - 30,000 transactions to be processed this consumes hours of processing time.