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.
The Future of Big Data
With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.
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;
