Last updated on SEPTEMBER 03, 2015

Applies to:

Symptoms

This query that all ODM connections run multiple times every minute, run for 5-20 seconds:

SELECT jobs.WORKFLOW_CHAIN_NAME, jobs.WORKFLOW_NAME, jobs.WORKFLOW_ID
     , jobs.WORKFLOW_STATUS, jobs.PROJECT_NAME, jobs.PROJECT_ID
     , jobs.WORKFLOW_JOB_NAME, steps.NODE_ID, steps.NODE_STATUS
     , steps.NODE_START_TIME, steps.NODE_RUN_TIME, steps.subnode_id
     , steps.subnode_status, (steps.NODE_START_TIME + steps.NODE_RUN_TIME) finish_job_time, jobs.JOB_CREATION_TIME
     , CURRENT_TIMESTAMP
  FROM odmr_user_workflow_jobs jobs, odmr_user_workflow_all steps
 WHERE jobs.workflow_job_name IN (SELECT workflow_job_name
                                    FROM (SELECT t.*
                                                 , ROW_NUMBER () OVER (PARTITION BY workflow_id ORDER BY job_creation_time DESC, workflow_chain_name DESC) rid
                                            FROM odmr_user_workflow_jobs t)
                                   WHERE rid = 1)
   AND steps.wf_job_name = jobs.workflow_job_name
   AND ( ( (jobs.JOB_CREATION_TIME > (CURRENT_TIMESTAMP - INTERVAL '24' HOUR))
       OR (WORKFLOW_STATUS IN ('ACTIVE', 'QUEUED', 'STOPPING')))
     OR ( (steps.NODE_START_TIME + steps.NODE_RUN_TIME) > (CURRENT_TIMESTAMP - INTERVAL '1' MINUTE)))
ORDER BY node_start_time DESC;

So if we have 5 users connected, database load will be 5 active sessions. This query is always running and running very slow.

Changes

 Upgraded to 12C from 11G

Cause