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

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


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-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 trimspool on
set feedback off
set termout off
set echo off
spool /tmp/biapps-lp-session-flame.out

WITH RUN_DATA AS (
SELECT  LPI.I_LOAD_PLAN, LPI.I_LP_INST,LPI.LOAD_PLAN_NAME,
       TO_CHAR(LPR.START_DATE,'YYYY-MM-DD HH24:MI:SS') AS LPR_START_DATE, TO_CHAR(LPR.END_DATE,'YYYY-MM-DD HH24:MI:SS') AS LPR_END_DATE, LPR.NB_RUN, LPR.DURATION AS LPR_DURATION, LPR.STATUS AS LPR_STATUS,
       LPIS.I_LP_STEP,LPIS.PAR_I_LP_STEP,LPIS.LP_STEP_NAME, LPIS.SCEN_NAME, LPIS.STEP_ORDER,LPISL.SESS_NO, S.SESS_NAME ,S.SESS_DUR AS SESSION_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
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' AND A.STATUS = 'D'
  )
ORDER BY LPI.I_LOAD_PLAN, LPI.I_LP_INST, LPR.START_DATE, LPISL.START_DATE
)
SELECT DISTINCT SYS_CONNECT_BY_PATH(REPLACE(LP_STEP_NAME,' ','_'), ';') || ';'|| D.SESS_NAME || ' ' || TO_CHAR(SESSION_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