biapps-lp-session-step-flame-export.sql.txt

-- $ cat /tmp/biapps-lp-session-step-flame.out |~/git/FlameGraph/flamegraph.pl --title "BI Apps - ODI - Load Plan / Load Plan Steps / Sessions / Session Steps - @rmoff 20150330" > /tmp/biapps-lp-session-step-flame.svg
set linesize 9999
set pagesize 50000
set heading off
set trimspool on


SELECT 'sed -e ''s/^;//g'' /tmp/biapps-lp-session-step-flame.out |grep -v " 0$"|grep -v ";TASK:$"|~/FlameGraph/flamegraph.pl --title "'
      || B.LOAD_PLAN_NAME || ' / ' || TO_CHAR(MAX(A.START_DATE),'YYYY-MM-DD HH24:MI:SS') || '" > /tmp/session-step-flame.svg'
FROM SNP_LPI_RUN A INNER JOIN SNP_LP_INST B ON A.I_LP_INST = B.I_LP_INST WHERE B.LOAD_PLAN_NAME= 'EBSVISION FIN HR_21_20141021_223159' GROUP BY LOAD_PLAN_NAME;

set feedback off
set echo off
set termout off

spool /tmp/biapps-lp-session-step-flame.out

-- Recursive Load Plan -----> Session Steps
WITH RUN_DATA AS (
SELECT LPIS.I_LP_STEP,LPIS.PAR_I_LP_STEP,LPIS.LP_STEP_NAME, S.SESS_NAME , SS.STEP_NAME,SSL.STEP_DUR AS SESS_STEP_DUR
FROM SNP_LOAD_PLAN LP
INNER JOIN SNP_LP_STEP LPS ON LP.I_LOAD_PLAN = LPS.I_LOAD_PLAN
LEFT OUTER JOIN SNP_LP_INST LPI ON LP.I_LOAD_PLAN = LPI.I_LOAD_PLAN
LEFT OUTER JOIN SNP_LPI_RUN LPR ON LPI.I_LP_INST = LPR.I_LP_INST
LEFT OUTER JOIN SNP_LPI_STEP LPIS ON LPI.I_LP_INST = LPIS.I_LP_INST AND LPS.I_LP_STEP = LPIS.I_LP_STEP
LEFT OUTER JOIN SNP_LPI_STEP_LOG LPISL ON LPI.I_LP_INST = LPISL.I_LP_INST AND LPIS.I_LP_STEP = LPISL.I_LP_STEP AND LPR.NB_RUN = LPISL.NB_RUN
LEFT OUTER JOIN SNP_SESSION S ON LPISL.SESS_NO = S.SESS_NO
LEFT OUTER JOIN SNP_SESS_STEP SS ON S.SESS_NO = SS.SESS_NO
LEFT OUTER JOIN SNP_STEP_LOG SSL ON SS.SESS_NO = SSL.SESS_NO AND SS.NNO = SSL.NNO
WHERE LPI.LOAD_PLAN_NAME = 'EBSVISION FIN HR_21_20141021_223159'
AND LPR.START_DATE = (
  SELECT MAX(A.START_DATE) FROM SNP_LPI_RUN A INNER JOIN SNP_LP_INST B ON A.I_LP_INST = B.I_LP_INST WHERE B.LOAD_PLAN_NAME= 'EBSVISION FIN HR_21_20141021_223159'
  )
ORDER BY LPI.I_LOAD_PLAN, LPI.I_LP_INST, LPR.START_DATE, LPISL.START_DATE, SSL.STEP_BEG
)
SELECT DISTINCT SYS_CONNECT_BY_PATH(LP_STEP_NAME, ';') || ';'|| D.SESS_NAME || ';'|| D.STEP_NAME || ' ' || TO_CHAR(SESS_STEP_DUR)
FROM RUN_DATA D
CONNECT BY PRIOR D.I_LP_STEP = D.PAR_I_LP_STEP
START WITH D.I_LP_STEP = (SELECT I_LP_STEP  FROM SNP_LP_STEP LPS  INNER JOIN SNP_LOAD_PLAN LP  ON LPS.I_LOAD_PLAN       = LP.I_LOAD_PLAN  WHERE LPS.PAR_I_LP_STEP IS NULL
  AND LP.LOAD_PLAN_NAME    = 'EBSVISION FIN HR_21_20141021_223159'
  AND LP_STEP_TYPE         = 'SE'
  )
;

spool off
exit