OBIA – Need To Tune Lengthy ETL – the Analyze Table ETL step is performing very slowly

1. Syntax to analyze only indexes

A: You can turn off Analyze for all tables, or turn it on for truncated tables only, in DAC UI. Refer to DAC Execute View, click on the Execution Plan , then look down at the Edit Tab, there you will see check boxes

2. Rationale when really needed and in which cases can we remove this analyze step

A: Whether or not you execute “analyze” is a decision taken based on costs and benefits of performance. The recommendation is to analyze because , the database is gathering statistics about the data. He uses the statistics to access the data more efficiently, both during ETL and during BI reporting ie when your end-users execute BI reports or dashboards. If you do less or no analysis of db statistics, then ETL and reporting performance will be degraded. How much is project dependent, but the recommendation is to do it and that is therefore the default setting.

3. How to perform the analyze step less frequently (not every day)

A: In DAC and above , in Setup View, in Physical Data Sources Tab: Analyze Frequencies Subtab
Analyze frequencies in days by table type. You can see the possible table types by looking in the Design View at the Tables Tab, each table has a type like Aggregate, Dimension…etc

You will need to rebuild your execution plan when you make changes, because this rebuild is where DAC decides which Tasks and sub-Tasks to execute

Hints: You can test using DAC Setup option: Dry Run (in your test environment). You can also use the Preview Run Details Button
The Preview Run Details Gives a summary of the details of what gets executed when the particular task runs part of the execution plan.

It includes the following:

Pre-ETL actions
Upon Failure Restart actions
Truncate table information
List of indexes to be dropped and created
Full or incremental command based on the current situation
Execution Plans Tab
Tables to be analyzed
Upon Success action
Upon Failure action

The Oracle Business Intelligence Data Warehouse Administration Console (DAC) Documentation provides the above information.

4. In the DAC Guide it says:

DAC setting : CreateQueryIndexesAtTheEnd

“… aware that when this property is set to True, tables will be analyzed twice. If any indices (indexes) are marked as Query type indices, and are used by ETL processes, it can adversely affect the performance of the ETL process.”

What is suggested, is to check the setting you have right now, and if set to TRUE you might want to check whether setting to FALSE helps in your scenario.
You are recommended to engage Oracle Advanced Consulting to help you tune your Execution Plan design.



One thought on “OBIA – Need To Tune Lengthy ETL – the Analyze Table ETL step is performing very slowly

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s