We have already established that having too many Wait activities (waiting for 1+ hour) in multiple workflows can leave the underlying runwf process running unnecessarily on the server thus taking a hit on performance. With that in mind, I'm trying to search for workflows that ran for too long, say 4 hours eg: it started at say 10:00 and finished at 14:00. The workflow may have waited (Wait activity) for 3.5 hours and so have actually taken just 0.5 hour to finish. What's the best possible way to search for such workflows?
I think we have a OOTB query monitoring jssp page (if you have installed the acx package).
which will give a update of the workflows that are currently running with the PID attached for the same.
Hope this helps.
We have that package installed. However, it only provides you with what is currently running on your DB server (innit?). Moreover, it won't list if your workflow is waiting (via Wait activity) and with every refresh, it'll flush out those that have completed and list down only the ones currently running.
Just keep in mind that if a workflow is in "stand by" (wait activity), even if it is marked as "running", it doesn't mean that a server process (runwf) is running ! A waiting workflow with a scheduler or wait activity is just awake when necessary.
You can verify this with one particular attribute of the xkt:workflow schema : the @processId. This is the identifier of "runwf" process on the machine (the one you could use to kill a proces, with "kill -9 processid" command for example). If the worklfow has this attribute with 0, it means that no process is runing. You can check the list of your running workflow, displaying this attribute value to see wich ones are really running a process.
Also, if you run a V7 ( at least build 18.11) you can see on the Home page of the console the workflow heatmap, a very usefull view of the real load of the instance : https://experienceleague.adobe.com/docs/campaign-classic-learn/tutorials/monitoring/workflow-heatmap...
Otherwise, if you really need to check deeper, I suggest you to look at the xtk:workflowTask schema : those are the instances of each activity in each workflow that have been executed. You will find several infos like start time, end time etc.