We migrated OWB and the DB from 10.2 on AIX to 11.1.07 on Linux.
On our old system, starting mappings from within a process flow was okay (90% within 1", 99% within 6").
On our new system, mappings have a delay of 6-12" (for 90%) before they start.
I found this difference comparing the logs all_rt_audit_executions.created_on and all_rt_audit_map_runs.start_time:
CEIL((start_time-e.created_on)*24*60*60) AS delay_sec,
FROM all_rt_audit_executions e
LEFT OUTER JOIN all_rt_audit_map_runs r ON r.execution_audit_id = e.execution_audit_id
Looking at all_rt_audit_executions.elapse_time shows, that also simple activities like AND, OR or WHILE LOOP have an avarage elapse time of 3", WHILE LOOP - with a simple compare of a variable - even of 8".
What happen during this long time?
How can I reduce this delay?
A process flow has a loop with 5 activities (1 mapping, 2 procedures, WHILE, END).
Unfortunately this loop runs for more than 1000 times.
Don't give me hint to optimize the process flow or the mapping ...
I checked tSince we set up our DB a few weeks ago, the runtime audit log tables are +/- empty (eg 31000 records in OWB$WB_RT_AUDIT_EXECUTIONS, in old system we have 690'000 records in WB_RT_AUDIT_EXECUTIONS).
I can't believe, that after a few weeks without housekeeping job, the performance is already out of expectation ...
I will give a try today with truncating the log tables.
As far as I understood, workflow puts next "waiting" tasks in an Advanced Queue.
We didn't change the init.ora-parameter aq_tm_processes, where the default is 0 on a 11.1 DB.
On our 10.2 DB we set this aq_tm_processes = 2
Could this cause the problem?
It look's like, that for each Activity in a Process Flow, the workspace is set again.
(I can see a lot of queries against ALL_IV_WORKSPACE/WORKSPACE_ASSIGNMENT views in v$sql_area).
On 10.2, the concept of workspaces was not yet in place.
Since the duration of wb_workspace_management.set_workspace is something about 5 seconds, there is no surprise, that each activity is also that slow.