OBIA 11g : Analyzing ODI ETL Load Performance

When you run an ODI ETL Load plan, it takes certain time. At times, customers would like to understand a split of the time taken by the load plan and analyze the top running maps to see if they can be optimized to run faster.

In the first part in this series, we help you understand how you can easily analyze the time taken by the load plan and identify the top running maps.

Using ODI Studio, a customer can load at the Load Plan logs and see the time taken by each phase or if required, each step in the load plan. However since there is no easy option to sort by the time taken, it can sometimes be painful to look at the complete logs from ODI Studio to easily identify the top running maps.

The following backend queries(to be run against the ODI repository schema) can help you see the same details that you see in ODI studio and yet analyze them better.

Query to get the overall load plan run timings for an instance

--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 ;

The query will prompt you for the Load Plan Instance id. You can easily get it from ODI studio for the load plan run you want to analyze. Below screenshot shows sample output of the above query.

Next step is to analyze the individual step timings of a single load plan instance.The below query provides the timings at each load plan step.Use the LVL Column in query below to filter and see the timings at higher levels in the load plan (see examples below). (Download the queries here ODI ETL Performance Backend Queries)

--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
;

How to Export query output from SQL Developer

Run the query from SQL developer. Then right click on the output and select export. Specify xlsx format and then click on next to finish the export. Screenshots below:

Once you have the excel sheet, you can easily analyze the timings as shown in examples below.

Phase Wise Summary

Use the LVL column to filter on top levels (level 1,2,3). See screenshot below.

Top Running Maps:

In the excel sheet, sort on DURATION_SEC and filter on STEP_TYPE=RS i.e only Scenario steps, to get the top running scenarios. The session numbers are also present in the output.

Now you can analyze the individual top-running sessions further by looking at the sessions in ODI studio and checking which particular step in the session is taking most of the time and what is the SQL for that particular step. Obtain the AWR report and SQL advisor report for that query and see how that query can be tuned.

Here below, we list certain parameter/settings at ODI/Database/Network level that can influence the overall load plan timings.

ODI Settings

1) Check the number of parallel sessions configured against the ODI agent. Recommendation is to have NOT more than 5 ODI sessions configured to run in parallel. If your server is not sized up properly, this may have to be reduced further. You can find this setting against the definition of the ODI Physical  agent  - Maximum number of parallel sessions property (in ODI studio).

2) Confirm if memory settings for the ODI Managed server are as per the BIAPPS technote. Refer to the following doc id in My Oracle Support website which specifies the recommended memory settings. Oracle Business Intelligence Applications Version 11g Performance Recommendations (Doc ID 1963225.1)

3) Ensure that you are on the relevant ODI patches. Refer to the BIAPPS documentation/certification matrix for your BIAPPS release to get the exact ODI patch to be on.

3) Check the WLS settings for Connection pool of ODI repository in WLS. Below are the recommended WLS settings.

1. Initial Capacity - The number of connection that will be opened when WLS starts. Set to 5 for frequent used ones like ODI and BIACOMP.
2. Minimum Capacity - The minimum number of physical connections that this connection pool will always contain after it is initialized. Set to 5 for all repositories with high load (E.g. ODI, SDS, BIACOMP)
3. Maximum Capacity - The maximum number of physical connections that this connection pool can contain.
   1. Master, Work, BIACOMP and SDS repositories should be set to 100.
   2. This parameter can be modified based on the customer hardware configuration, which might help improve the overall LP (if the customer finds UNKNOWN Gaps in LP). It is found that CPU utilization increases when this parameter is higher.
4. Shrink Frequency - The number of seconds to wait before shrinking a connection pool that has incrementally increased to meet demand. For all connections this should be 300. This will then match the TestFrequency and ensure that the connections are released if not used. 
5. Seconds to Trust an Idle Pool Connection - The number of seconds within the last usage of a connection, that WebLogic Server trusts that the connection is still viable and will skip the connection test, either before delivering it to an application or during the periodic connection testing process.Should be 10 for all repositories to prevent unnecessary tests. 10 is weblogic default and recommendation for production environments

5) If the SDE mappings are taking long, the customer can experiment with the Array Fetch Size against the ODI physical connections. The array fetch size against the source/target connection is known to impact the load duration.

6) Ensure the ODI repository database schema has been analyzed. If you have done a lot of load plan runs, consider purging old ODI session logs to reduce the size of the ODI repository database.

Database Settings

1) Check the number of sessions and processes setup at your database. Set processes parameter to atleast 500 and sessions parameter to atleast 3000 for the database. Run the below query with a DBA user to check the current setting.
SELECT NAME,VALUE FROM V$PARAMETER WHERE name in ('processes','sessions');

2) For Full load , you could consider keeping the database in NOARCHIVELOG mode with forcelogging turned off. Refer to BIAPPS Performance tech note Oracle Business Intelligence Applications Version 11g Performance Recommendations (Doc ID 1963225.1). Note: NOARCHIVELOG mode can impact the ability to restore the database in case of a crash. So please talk to the DBA before deciding to use this option.

3) Ensure redo logs are adequate. The default of 3 redo logs, each of 50MB size is not adequate for BIAPPS loads especially when the volumes are high. Refer to tech note above.

4) Ensure SDS/DW/ODI/BIACM schemas have adequate free tablespace size. Also if the tablespaces files are set to autoextend, ensure that the increment by is set adequately high (Especially for DW and SDS schemas). Consider setting incrementby from 500MG to 1GB for DW/SDS tablespace files. Before full load, estimate the size of your database and then ensure that the initial size of the tablespace is big enough to hold the data.

5) Run AWR reports and use SQL advisor periodically to fine tune long running queries.

6) Partition large fact tables. Tables are not partitioned Out of the box.

Network Settings

1) Ensure that ODI agent is as close as possible to the Database. They should be on the same LAN at a minimum. 

2) Ensure there are no timeouts set at either the database or the network especially for the connection used by the ODI agent. Agent connections can tend to remain inactive for a long time (more than an hour for long running load plans). If Sessions remain in a particular state and nothing is happening for long and you don't notice any activity in the database, then agent might have lost connection with the ODI repository. Refer to the ODI Documentation on Stale Sessions for more details. Typically an agent restart should fix the problem. However check where the timeouts are present and then relax/remove them for ODI connections.