Troubleshooting Workflow Performance

Have you ever had some stuff that was taking a long-time to run, and wanted to figure out where it was spending it's time. These SQL statements may help you figure it out. You need to change the value of the date string to the point in time you want to start tracking...

-- ----------------------------------------------------------------------
-- Lists all workflows instances that have fired since a given date and
-- time, in the order they fired along with their status
-- ----------------------------------------------------------------------
select wf_name, bo_type_name, bo_event_name,
case
when status_id = 1 then 'In Progress'
when status_id = 40 then 'Running'
when status_id = 90 then 'Stopped'
when status_id = 100 then 'Completed'
else 'Unknown'
end as status
,start_date, end_date
from wf_template
where template_flag not in (10,20,25,30) -- 10,20,25,30 are templates, everything else is an instance
--and status_id <> 100 -- 100 are completed, so uncomment this to see only those that haven't finished running
and created_date > to_date('01/29/2008 01:23:45 PM', 'MM/DD/YYYY HH:MI:SS AM')
order by created_date asc

-- ----------------------------------------------------------------------
-- Lists workflows instances that have fired since a given time, along
-- with how long it took for them to process
-- ----------------------------------------------------------------------
select wf_name, bo_type_name, bo_event_name, trunc(86400*(end_date-start_date))-60*(trunc((86400*(end_date-start_date))/60)) as processing_time_secs

from wf_template
where template_flag not in (10,20,25,30) -- 10,20,25,30 are templates
and created_date > to_date('01/29/2008 01:23:45 PM', 'MM/DD/YYYY HH:MI:SS AM')
order by 4 desc

-- ----------------------------------------------------------------------
-- Gives a average processing time and number of times a given workflow
-- (by name) has fired since a specified date and time
-- ----------------------------------------------------------------------
select wf_name, count(*), avg(trunc(86400*(end_date-start_date))-60*(trunc((86400*(end_date-start_date))/60))) as processing_time_secs

from wf_template
where template_flag not in (10,20,25,30) -- 10,20,25,30 are templates
and created_date > to_date('01/29/2008 01:23:45 PM', 'MM/DD/YYYY HH:MI:SS AM')
group by wf_name
order by 3 desc