REM ReUseCand.sql
REM (c)Go-Faster Consultancy Ltd. 2014
COLUMN processes       HEADING 'Num|Process|Instances'
COLUMN process_name    HEADING 'Process|Name'
COLUMN detail_id       FORMAT a32
COLUMN step_time       HEADING 'AE|Step|Secs' FORMAT 999990 
COLUMN compile_count   HEADING 'AE|Compile|Count'
COLUMN execute_count   HEADING 'AE|Execute|Count'
SPOOL ReUseCand
WITH x AS (
SELECT l.process_instance, l.process_name
,      l.time_elapsed/1000 time_elapsed
,      l.enddttm-l.begindttm diffdttm
,      d.bat_program_name||'.'||d.detail_id detail_id
,      d.compile_count, d.compile_time/1000 compile_time
,      d.execute_time/1000 execute_time
FROM   ps_bat_Timings_dtl d
,      ps_bat_timings_log l
WHERE  d.process_instance = l.process_instance
AND    d.compile_count = d.execute_count 
AND    d.compile_count > 1 
AND    l.enddttm > SYSDATE-7
), y as (
SELECT x.*
,      GREATEST(0,60*(60*(24*EXTRACT(day FROM diffdttm)
                            +EXTRACT(hour FROM diffdttm))
                            +EXTRACT(minute FROM diffdttm))
                            +EXTRACT(second FROM diffdttm)-x.time_elapsed) delta 
FROM x
), z AS (
SELECT process_instance, process_name, detail_id
,      CASE WHEN time_elapsed < 0 THEN time_elapsed+delta  
            ELSE time_elapsed END time_elapsed
,      compile_count
,      CASE WHEN compile_time < 0 THEN compile_time+delta
            ELSE compile_time END AS compile_time
,      CASE WHEN execute_time < 0 THEN execute_time+delta
            ELSE execute_time END AS execute_time
FROM y
), a AS (
SELECT process_name, detail_id
,      SUM(compile_time+execute_time) step_time
,      SUM(compile_count) compile_count
,      COUNT(DISTINCT process_instance) processes
FROM   z
GROUP BY process_name, detail_id)
SELECT * FROM a
WHERE  compile_count >= 10000 
ORDER BY step_time desc
/
SPOOL OFF
