Potential performance issue on the large number of Tasks created due to cartesian join for an OOTB query

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

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

Environment

Release : 16.0.3

Cause

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  )

Resolution

DE68467, Under review by Engineering team.