etl_perf_odi_backend_queries.txt

--List of Load Plan runs for a load plan instance id
SELECT
  LP.LOAD_PLAN_NAME,
  LPI.I_LOAD_PLAN,
  LPR.I_LP_INST,LPR.NB_RUN,
  TO_CHAR(LPR.START_DATE, 'MM-DD-YYYY HH24:MI:SS') LPRUN_START_DATE,
  TO_CHAR(LPR.END_DATE, 'MM-DD-YYYY HH24:MI:SS') LPRUN_END_DATE,
  LPR.STATUS --D    Done,E    Error,M    Warning,R    Running,W    Waiting,A    Already done in a previous run
FROM SNP_LPI_RUN LPR,SNP_LP_INST LPI, SNP_LOAD_PLAN LP
WHERE 
  LPR.I_LP_INST=&LOAD_PLAN_INSTANCE_ID
  AND LP.I_LOAD_PLAN=LPI.I_LOAD_PLAN
  AND LPI.I_LP_INST=LPR.I_LP_INST
ORDER BY LPR.START_DATE ;

--LP Instance Latest Run Step Timings
SELECT
  LP.I_LOAD_PLAN,LP.LOAD_PLAN_NAME,LPI.I_LP_INST,
  LPLOG.NB_RUN, 
  COUNT(1) OVER( ORDER BY STEP_NUM)-1 LPSTEPNUM, 
  SH.LVL ,
  LPAD(' ', 2 * SH.LVL - 1,' ') || LPS.LP_STEP_NAME STEP_HIERARCHY,    
  LPLOG.STATUS,--D    Done,E    Error,M    Warning,R    Running,W    Waiting,A    Already done in a previous run
  LPLOG.NB_ROW,
  LPLOG.SESS_NO,
  CASE WHEN LPLOG.SESS_NO IS NOT NULL THEN TRUNC((SESS.SESS_END-SESS.SESS_BEG)*24*60)||':'||LPAD(TRUNC(MOD((SESS.SESS_END-SESS.SESS_BEG)*24*60*60,60)),2,'0')
    ELSE TRUNC((LPLOG.END_DATE-LPLOG.START_DATE)*24*60)||':'||LPAD(TRUNC(MOD((LPLOG.END_DATE-LPLOG.START_DATE)*24*60*60,60)),2,'0')
  END DURATION_FORMATTED,
  CASE WHEN LPLOG.SESS_NO IS NOT NULL THEN TRUNC((SESS.SESS_END-SESS.SESS_BEG)*24*60*60)
    ELSE TRUNC((LPLOG.END_DATE-LPLOG.START_DATE)*24*60*60)
  END DURATION_SEC,
  CASE WHEN LPLOG.SESS_NO IS NOT NULL THEN TO_CHAR(SESS.SESS_BEG, 'MM-DD-YYYY HH24:MI:SS')
    ELSE TO_CHAR(LPLOG.START_DATE, 'MM-DD-YYYY HH24:MI:SS')
  END START_TIME,
  CASE WHEN LPLOG.SESS_NO IS NOT NULL THEN TO_CHAR(SESS.SESS_END, 'MM-DD-YYYY HH24:MI:SS')
    ELSE TO_CHAR(LPLOG.END_DATE, 'MM-DD-YYYY HH24:MI:SS')
  END END_TIME,
  NVL(LPS.SCEN_NAME,LPS.VAR_NAME) SCEN_VAR_NAME,
  LPS.LP_STEP_TYPE,
  CASE WHEN LPS.IND_ENABLED =1 THEN 'Y' ELSE 'N' END IS_ENABLED,
  LPS.I_LP_STEP,
  LPS.PAR_I_LP_STEP, 
  LPS.RESTART_TYPE, 
  LPS.SCEN_NAME,LPS.SCEN_VERSION,
  LPS.VAR_NAME,LPS.VAR_OP,LPS.VAR_VALUE, 
  LPS.SESS_KEYWORDS,LPS.VAR_LONG_VALUE
FROM SNP_LOAD_PLAN LP INNER JOIN SNP_LP_INST LPI
ON LPI.I_LOAD_PLAN=LP.I_LOAD_PLAN
   INNER JOIN
  (SELECT I_LOAD_PLAN,I_LP_STEP,LP_STEP_NAME,STEP_PATH,TYPE_PREF,LVL,STEP_ORDER,RANK() OVER(ORDER BY TYPE_PREF DESC,ROWNUM) STEP_NUM FROM (
    SELECT I_LOAD_PLAN,I_LP_STEP,LP_STEP_NAME,STEP_PATH,
      CASE WHEN SUBSTR(STEP_TYPE_PATH,1,4)='->EX' THEN 1 ELSE 0 END TYPE_PREF,
      LVL,STEP_ORDER FROM
    (
    SELECT * FROM (
    SELECT  I_LOAD_PLAN,I_LP_STEP,LP_STEP_NAME,SYS_CONNECT_BY_PATH(LP_STEP_NAME, '->') STEP_PATH ,
        SYS_CONNECT_BY_PATH(LP_STEP_TYPE, '->') STEP_TYPE_PATH ,LEVEL LVL, STEP_ORDER
        FROM    SNP_LP_STEP       
        START WITH
            PAR_I_LP_STEP IS NULL           
        CONNECT BY           
            I_LOAD_PLAN=PRIOR I_LOAD_PLAN AND
            PAR_I_LP_STEP = PRIOR I_LP_STEP
       ORDER SIBLINGS BY STEP_ORDER 
       )WHERE SUBSTR(STEP_TYPE_PATH,1,4)!='->EX' --Exclude Exception Steps
       ))
  ) SH
ON LP.I_LOAD_PLAN=SH.I_LOAD_PLAN
LEFT OUTER JOIN SNP_LPI_STEP LPS
  ON LPI.I_LP_INST=LPS.I_LP_INST
  AND LPS.I_LP_STEP=SH.I_LP_STEP
LEFT OUTER JOIN SNP_SCEN S
  ON LPS.SCEN_NAME=S.SCEN_NAME
LEFT OUTER JOIN SNP_LPI_STEP_LOG LPLOG
  ON LPLOG.I_LP_INST=LPS.I_LP_INST AND LPLOG.I_LP_STEP=LPS.I_LP_STEP
LEFT OUTER JOIN SNP_SESSION SESS
  ON LPLOG.SESS_NO=SESS.SESS_NO
WHERE LPI.I_LP_INST=&LOAD_PLAN_INSTANCE_ID
AND (LPLOG.NB_RUN=(SELECT DISTINCT
                    MAX(QLPR.NB_RUN) OVER() MAX_NB_RUN
                    FROM SNP_LPI_RUN QLPR,SNP_LP_INST QLPI
                    WHERE QLPR.I_LP_INST=LPI.I_LP_INST
                    AND QLPI.I_LP_INST=QLPR.I_LP_INST
                    )
    OR LPLOG.NB_RUN IS NULL)
ORDER BY LP.I_LOAD_PLAN,STEP_NUM
;