22 June 2017

Cloud Control 13c - graph your job history

I wanted to see all my Cloud Control 13c jobs that ran during the last day.
How long did they take, did they run parallel ?

The SYSMAN.MGMT$JOB_EXECUTION_HISTORY contains all info needed, but it may be hard to interpret an draw your conclusions.
If you like to see when job overlap (and possible a struggle for CPU or I/O), you will need a graph.

With the following query you will get a (text-based, very eighties) graph from SQL.

Hope it helps in your environment.

 set lines 999 trimspool on pages 999  
 col job_name format a25  
 col status format a11  
 col mins format 9999  
 col graph format a80  
 SELECT distinct job_name,  
     to_char(CAST((FROM_TZ(CAST(START_TIME AS TIMESTAMP),'+00:00') AT TIME ZONE 'Europe/Brussels') AS DATE),'YYYY.MM.DD-HH24:MI:SS') "Start time",  
     to_char(CAST((FROM_TZ(CAST( END_TIME AS TIMESTAMP),'+00:00') AT TIME ZONE 'Europe/Brussels') AS DATE),'YYYY.MM.DD-HH24:MI:SS') "End time",  
     STATUS,  
     round((end_time - start_time) * 24 * 60 ) "Mins",  
     rpad('-',round(80*(start_time - (sysdate -1))),'-') ||  
     rpad('X',round(80*(end_time - start_time  )),'X') ||  
     rpad('X',  
     80-(round(80*(start_time-(sysdate-1))))  
      -(round(80*( end_time-start_time)))  
     ,'-') "graph"  
 FROM sysman.MGMT$JOB_EXECUTION_HISTORY  
 where start_time > sysdate - 1  
 and status not in ('Waiting','Scheduled')  
 and (end_time - start_time) * 24 * 60  > 10  
 order by 2 ;  



It shows jobs running longer than 10 minutes only. The graph will be exactly 80 char width, you may change that to your liking.
Due to the rounding, it may be that there seems no overlap, but it can be overlapping a few minutes. Bear this in mind.

Sample Output:

 
 JOB_NAME                 Start time           End time            STATUS       Mins graph  
 ------------------------- ------------------- ------------------- ----------- ----- --------------------------------------------------------------------------------  
 BACKUP_DATABASE01        2017.06.21-14:40:03 2017.06.21-14:55:09 Succeeded       15 ---------XX---------------------------------------------------------------------  
 CLEANUP_JOB_DAILY        2017.06.21-19:00:05 2017.06.21-19:20:54 Succeeded       21 -----------------------XX-------------------------------------------------------  
 DEMO_BATCH_PROCES        2017.06.21-20:00:05 2017.06.22-00:02:13 Succeeded      242 ---------------------------XXXXXXXXXXXXXX---------------------------------------  
 BACKUP_DATABASE02_INCR   2017.06.21-22:00:05 2017.06.22-01:02:19 Succeeded      182 ---------------------------------XXXXXXXXXXX------------------------------------  
 JUST_ANOTHER_BACKUP      2017.06.21-23:00:02 2017.06.22-05:10:00 Succeeded      370 -------------------------------------XXXXXXXXXXXXXXXXXXXXXX---------------------  
 SMALL_REPORT_DAILY       2017.06.22-01:30:02 2017.06.22-01:49:44 Succeeded       20 ---------------------------------------------XX---------------------------------  
 RUN_REPORT_JOB           2017.06.22-02:00:05 2017.06.22-03:10:25 Succeeded       70 -----------------------------------------------XXXXX----------------------------  
 CREATE_MONTHLY_OVERVIEW  2017.06.22-04:00:01 2017.06.22-04:13:07 Succeeded       13 -----------------------------------------------------XX-------------------------