Revision as of 21:55, 14 February 2022

Manutenção da tabela CRONTASKHISTORY

Crontask/Escalation can store the task execution times. This feature is enable when mark ‘Keep history’ checkbox in crontask. This information is recorded in CRONTASKHISTORY table

I use the following table to define history records and reduce the size of CRONTASKHISTORY table:

Schedule	History Records	Description
less than 1d	1000	-
1d or greater	100	history of the last 100 days (3 months)
1w	10	history of the last 10 weeks (3 months)

Listar o total de crontasks executadas

select count(1) from crontaskhistory

Listar o total de crontasks executadas por Crontask e Instancia

select crontaskname, instancename, count(1) as cnt 
from crontaskhistory
group by crontaskname, instancename
order by cnt desc

Listar o total de crontasks executadas por Ano

select to_char(endtime,'YYYY') as yr, count(1) as cnt
from crontaskhistory
group by to_char(endtime,'YYYY')
order by to_char(endtime,'YYYY') desc

Com essa informações podemos melhorar o cleanup da tabela CRONTASKHISTORY. Basta desmarcar a opção Manter Histórico.

Detalhes

For example, if you have an escalation that runs every 5 minutes, and you specify its max history record as 100, 
and you have the mxe.crontask.historycleanuprate set to 60 minutes, then the history records will go from 

1000 to 1000+60/5=1012. 
 
If  the mxe.crontask.historycleanuprate is set to 180 minutes, the history records will go from 1000 to 100+180/5=1036. 
If you have an escalation that runs every 1 minute, and you have a 180 minute interval, 
then the history records can go as high as 1180 before cleanup.

Setting the cron task clean up interval to keep the Maximum Number of Records for Keep History

Listar as crontasks executados com maior tempo de duracao nos últimos 30 dias

select crontaskname, instancename, servername, (endtime-starttime)*24*60*60 exec_time, starttime
from crontaskhistory
where activity='ACTION'
and starttime > sysdate - 30
order by exec_time desc

Listar as crontasks executados por hora

select extract(hour from (cast(starttime as timestamp))) exec_hour, round(sum(endtime-starttime)*24*60*60,3) exec_time
from crontaskhistory
where activity='ACTION'
and starttime > sysdate - 30
group by extract(hour from (cast(starttime as timestamp)))
order by extract(hour from (cast(starttime as timestamp)))

Ver também

@@ Line 14: / Line 14: @@
 | less than 1d  || 1000 || -
 |-
-| 1d or greater || 100 || history of the last 100 days
+| 1d or greater || 100 || history of the last 100 days (3 months)
 |-
-| 1w || 100 || history of the last 100 weeks
+| 1w || 10 || history of the last 10 weeks (3 months)
 |}