There was a performance issue encountered in our Prod due to OOTB cartesian query.
One of the users created more than 6k tasks within short time period and following query ran more than 70k times. It looks like the query is executed several times per one task creation event. Since prtask in inline query is missing join with cmn_audits table, it is executing in 0.8 sec. We believe that this query is executed when a process associated to task object is triggered, since module is bg.
Steps to Reproduce:
1. Login as an Admin
2. Add Finish Date to the Audit on the Task object.
3. Create process based on the Task with start condition ( Task Finish != Task Finish [Previous Value] )
4. Choose a project template with many tasks.
5. Create project using the template identified from Step 4.
Expected Results: it should create project and it's tasks in the reasonable time.
Actual Results: depending on the DB load at the time due to bad join in the query it may take longer time
Release : 16.0.3
table_name = :"SYS_B_0"
AND aud.attribute_code = :"SYS_B_1"
AND aud.object_code = :"SYS_B_2"
AND aud.object_id = :1
ROWNUM = :"SYS_B_3"
) AS prfinish__oldvalue,
s0.prfinish AS prfinish,
s0.prexternalid AS prexternalid
prtask s0 WHERE ( s0.prid = :2 )
DE68467, Under review by Engineering team.