Data Miner’s Framework for Polling to Obtain Workflow Status Is Very Slow
(Doc ID 2052248.1)
Last updated on NOVEMBER 15, 2019
Applies to:
Oracle Data Mining - Version 12.1.0.2 and laterInformation in this document applies to any platform.
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |