How to Create Gather Statistics Procedure in ODI

Gathering Statistics of a database schema before executing an ODI load plan will improve performance by helping the Optimizer to select optimal execution plan by referring to latest statistics, instead of stale statistics.

We can gather statistics of Oracle database schema at a scheduled time of day using the DBMS_SCHEDULER and DBMS_AUTO_TASK_ADMIN packages, However, if we want to gather statistics right before executing the load plan, we can create a ‘Gather Statistics’ procedure and place it as first step in the load plan.

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

To create a ‘Gather Statistics’ procedure for the whole schema use the following code:

BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS (ownname => ‘<%=odiRef.getSchemaName(“DW_BIAPPS11G”, “D”)%>’);
END;

To create a ‘Gather Statistics’ procedure for a single table use the following code:

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ‘<%=ODIREF.GETSCHEMANAME(“DW_BIAPPS11G”, “D”)%>’,TABNAME=>’W_AP_AGING_INVOICE_A’, DEGREE => 4, ESTIMATE_PERCENT=>25);
END;