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.
To create a ‘Gather Statistics’ procedure for the whole schema use the following code:
DBMS_STATS.GATHER_SCHEMA_STATS (ownname => ‘<%=odiRef.getSchemaName(“DW_BIAPPS11G”, “D”)%>’);
To create a ‘Gather Statistics’ procedure for a single table use the following code:
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ‘<%=ODIREF.GETSCHEMANAME(“DW_BIAPPS11G”, “D”)%>’,TABNAME=>’W_AP_AGING_INVOICE_A’, DEGREE => 4, ESTIMATE_PERCENT=>25);