Skip to main content

Posts

Showing posts from June, 2013

Analysing ODI batch performance

I’ve been involved with some performance work around an ODI DWH load batch. The batch comprises well over 1000 tasks in ODI, and whilst the Operator console is not a bad interface, it’s not very easy to spot the areas consuming the most runtime. Here’s a set of SQL statements to run against the ODI work repository tables to help you methodically find the steps of most interest for tuning efforts. odi_04.sql — Session runtimes, including child sessions First off is the most fancy – using hierarchical SQL, it returns all sessions and child sessions: -- odi_04.sql -- -- ODI sessions and child sessions - runtimes -- -- select --level,      --, parent_sess_no      sess_no,     --Following column can be included if you want to see the root parent session name      --CONNECT_BY_ROOT sess_name "Root session",    --    -- Remove the lpad if you don't want child sessions indented in the results      lpad('> ',3*(level-1),'