Tuesday, March 6, 2012

Job average run

The below SQL query calculates the job average run time (in our case MAKEPLAN) using the historical data stored in the DB (using ORACLE DB):
       
alter session set current_schema=maestro_DB_user;

select avg (JHR_TOTAL_ELAPSED_TIME) as average
FROM JHR_JOB_HISTORY_RUNS
where AJB_NAME like 'MAKEPLAN'

If you want to calculate the average run time only for success jobs run:
       
alter session set current_schema=maestro_DB_user;

select avg (JHR_TOTAL_ELAPSED_TIME) as average
FROM JHR_JOB_HISTORY_RUNS
where AJB_NAME like 'MAKEPLAN' and JHR_STATUS = 'S'

Hint: The historical data age is controlled by the variable statsHistory / sh, the default value is 10 days.



No comments:

Post a Comment