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
SELECT
s0.prid odf_pk,
(
SELECT
*
FROM
(
SELECT
raw_value_before
FROM
cmn_audits aud,
prtask src
WHERE
table_name = :"SYS_B_0"
AND aud.attribute_code = :"SYS_B_1"
AND aud.object_code = :"SYS_B_2"
AND aud.object_id = :1
ORDER BY
aud.created_date DESC
)
WHERE
ROWNUM = :"SYS_B_3"
) AS prfinish__oldvalue,
s0.prfinish AS prfinish,
s0.prexternalid AS prexternalid
FROM
prtask s0 WHERE ( s0.prid = :2 )
DE68467, Under review by Engineering team.