Data & Intelligence

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.

Data Intelligence - The Future of Big Data
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.

Get the Guide

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

About the Author

10 years of experience doing analysis, design, development and implementation of Oracle Business Intelligence applications and ETL solutions using Informatica, DAC, ODI. Worked extensively on Oracle BIAPPS Implementations, SQL querying and Stored Procedures using PL/SQL. Worked on Functional Areas - HR, Finance, Service and Marketing Analytics, Procurement and Spend Analytics, Supply Chain and Order Management Analytics, Enterprise Healthcare Analytics (EHA) and OHADI (Oracle Healthcare Analytics Data Integration)

More from this Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up