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 10.1.3.4.1 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:
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
“…..be 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.