November 12, 2011

Crontask analysis and optimization

An interesting feature of Maximo crontask is the capability of storing the task execution times. This feature must be enabled in each crontask clicking on 'Keep history' checkbox. This is nice feature but Maximo does not provide a graphical tool to analyze crontask performances and execution times so i have prepared a small set of SQL queries to be able to quickly gather important statistics directly from Maximo's database.
The first one extracts the start time and duration (in seconds) of each crontask execution in the last week.

select
  crontaskname,
  to_char(starttime,'YYYY-MM-DD HH24:MI:SS') as starttime,
  (endtime-starttime) as duration
from CRONTASKHISTORY
where endtime>sysdate-7
  and activity='ACTION'
order by starttime;

I typically import the output of this query into an Excel spreadsheet and analyze it sorting by whatever column I need. Furthermore, you can create a pivot table or chart to analyze the data. This is an example of what can be achieved:

If you are not familiar with Excel, here is a query that generates the same output straight from an SQL query:
select
  crontaskname,
  sum(endtime-starttime) as totalduration
from CRONTASKHISTORY
where endtime>sysdate-7
  and activity='ACTION'
group by crontaskname;