Hi,

I would like to make some reporting about BPM failed processes.
The reporting must show the process instance failed, the reason why the instance is failed and if it exists the step where the problem occurred.

So I made some sql query to get these information.
But I m facing performance issue with this query .
It looks like:

SELECT STEPS.INSTANCEID, STEPS.STEPID, ERR.ERRORMESSAGE
FROM
(SELECT DISTINCT STEP.INSTANCEID, STEP.STEPID, STEP.CONTEXTID
FROM WMPROCESSRECENT PRREC INNER JOIN WMPROCESSSTEP STEP
ON PRREC.INSTANCEID = STEP.INSTANCEID
WHERE PRREC.AUDITTIMESTAMP BETWEEN TO_TIMESTAMP('01/04/2016 22:00', 'DD/MM/YYYY HH24:MI') AND TO_TIMESTAMP('02/04/2016 21:59', 'DD/MM/YYYY HH24:MI')
AND PRREC.STATUS = 4
AND PRREC.PROCESSKEY IN ('BPM_SalesProcess_V2_0/BPM_SalesProcess_V2_0')
AND STEP.AUDITTIMESTAMP BETWEEN PRREC.AUDITTIMESTAMP - INTERVAL '1' MINUTE AND PRREC.AUDITTIMESTAMP
) STEPS
INNER JOIN WMERROR ERR ON STEPS.CONTEXTID = ERR.CONTEXTID

So how to do to get all these information in a fast way ?
I m new with it and I only know about the main tables (wmprocess, wmprocessstep, wmprocessrecent, wmerror…)

Thanks

Please add some DB hints, try changing memory settings in terms of DB then you will able to get it.

Thanks,

Hi Kim,

I would highly recommend you to use public services from WmMonitor to achieve the same. For instance, the data you seek can be obtained by invoking below services

1. Invoke “pub.monitor.process.instance:getInstanceList” with status → 4, fromdate, todate etc…
2. Then, invoke “pub.monitor.process.instance:getInstanceErrors” to get ErrorMessage and Stepid etc…

I believe this would retrieve results faster than nested sql query with joins on the table.

Give a try and do the performance comparison.

Hi Kim,

I agree with Prasad.

Can you check your statement in a SQL Tool (like Oracle SQL Developer) and capture the Execution Plan?

You will find out there are a lot of full table scans to occur.
This is due to the fact that the columns used for joining do not have indices on them…
By adding indices to these there should be an increase in performance

Please provide your wM version, as some of the tables have changed their names recently (9.6 and above).

Regards,
Holger