One our customers had a request on how to take action, such as sending a notification, on Jobs that have been running longer than twice it's average runtime. After some investigation, I was able to do this in a condition by doing the following:
*Requirements*
The average runtime value we are using is stored in each Jobs' definition which is populated by the Job CALC_AVG_RUN. This Job is required to run before average runtime values are stored. More information on running CALC_AVG_RUN at the documentation link below:
Once Jobs have their average runtime populated, create a subvar called #twice_avg_run_time or whatever name you want with the following sql:
SELECT TO_CHAR(so_avg_runtime+so_avg_runtime, 'fm00G00G00',
'NLS_NUMERIC_CHARACTERS=''.:''') from so_job_table where so_module = '{task}'
The first part of the sql (so_avg_runtime+so_avg_runtime) is average runtime added to average runtime or 2x average runtime.
The second part of the sql ('fm00G00G00', 'NLS_NUMERIC_CHARACTERS=''.:''') converts the number in seconds to a compatible time format that we can use (HH:MMSS).
After your Subvar has been created, setup the Condition as follows:
If done properly, if a Job whose average runtime is 1 hour, then a notification will be sent out if the Job is still running after 2 hours.