Summary
Symptom
You want to create DB statistics on a BW-system or an APO or SEM system with an underlying (local) BW. The respective BW runs on an Oracle database platform.
Other terms
OLAP, Business Information Warehouse, InfoCube, Report, Query, Performance, Oracle, Cost-Based Optimizer, Statistics, DB statistics
Reason and Prerequisites
The BW query performance relies on Oracle's cost based optimizer choosing (near-) optimal execution plans. To that end, the cost-based optimizer requires statistical information on content of the tables that are involved in a query. If no such information exists or if it is out-dated then this might cause performance problems. Furthermore Oracle's cost-based optimizer works best for BW if it can rely on statistics with histograms, respectively statistics for columns.
SAPDBA, as a frequently used tool, had problems to create such statistics for various reasons: it lacked the ability to create statistics with histograms and it could not cope with partitioned tables. SAPDBA has been taken out of maintenance.
With SAP base release 6.10, BRCONNECT has taken over SAPDBA's statistics gathering functions. For details see http://service.sap.com/dbaora.
The following section describes the proposed way to gather statistics for the various releases of BW. This refers to the regular process for gathering those statistics. Independently, BW might still analyse tables in appropriate situations such as after data has been loaded, during an aggregates roll-up or a change run. These BW-triggered statistics runs can be switched off. See OSS note 555030 for details.
SolutionSAPDBA, as a frequently used tool, had problems to create such statistics for various reasons: it lacked the ability to create statistics with histograms and it could not cope with partitioned tables. SAPDBA has been taken out of maintenance.
With SAP base release 6.10, BRCONNECT has taken over SAPDBA's statistics gathering functions. For details see http://service.sap.com/dbaora.
The following section describes the proposed way to gather statistics for the various releases of BW. This refers to the regular process for gathering those statistics. Independently, BW might still analyse tables in appropriate situations such as after data has been loaded, during an aggregates roll-up or a change run. These BW-triggered statistics runs can be switched off. See OSS note 555030 for details.
- BW 2.x, BW 3.0A systems
We recommend to run the report SAP_ANALYZE_ALL_INFOCUBES once per week as a batch job, e.g. at night (see transaction SE38, menu "Program" -> "Execute" -> "Background"). It can run before or after SAPDBA if you use SAPDBA version 4.6D or higher. You should also avoid to run SAP_ANALYZE_ALL_INFOCUBES during data uploads.
Depending on the sizes of your infocubes the report might run between a few minutes and several hours. Using a 100% sample size, an infocube with a 20-million-rows fact table takes around 2 hours to be analyzed (depending also on your hardware)!
We suggest that you schedule a weekly job with the report and an initial sample size of 10%. The resulting run time of the job will give you an idea whether you can increase the sample size. This obviously depends also on the time you want to allow the job to run.
Due to the nature of block-sampling, sample sizes beyond 20% should result in analysis times that are as long as doing a complete analysis (100%). Therefore, we recommend sample sizes of 100% rather than than sizes like 25%, 30%, 40%, ...
Unfortunately, there is no general (or easy) rule to predict the run time of the report. Therefore we recommend that you experiment in the way described above. In any case, running the report cannot damage anything but increases the BW query performance.
- BW 3.0B and higher
We recommend to use BRCONNECT (release 6. 20 or higher) for gathering all DB statistics for BW tables. The proposed way to call BRCONNECT is
brconnect -u / -c -f stats -t all
If your BW system had been upgraded from a BW 2.x or BW 3. 0A system then you have to run the report SAP_DBSTATC_CLEANUP once. This report will clean up the table DBSTATC which was used in the earlier releases to separate the tables that had to be analysed by SAPDBA from those that had to be analysed by the report SAP_ANALYZE_ALL_INFOCUBES. Alternatively, you can initialize the table DBSTATC as described in OSS note 403704. If DBSTATC is not cleaned up then BRCONNECT might not gather all necessary statistics!
- Remark
Previously, many customers used the following two calls of BRCONNECT to gather statistics in any of the above BW releases:
brconnect -u / -c -f stats -t all -e all_part,info_cubes
brconnect-u / -c -f stats -t all_part,info_cubes -f allsel
The first call deals with non-infocube related tables, the second one with infocube related tables, thereby discarding DBSTATC entries. Because this is not the officially supported strategy we recommend to use the aproach described above.
The first call deals with non-infocube related tables, the second one with infocube related tables, thereby discarding DBSTATC entries. Because this is not the officially supported strategy we recommend to use the aproach described above.
Header Data
| Release Status: | Released for Customer |
| Released on: | 06.08.2003 15:39:45 |
| Master Language: | English |
| Priority: | Recommendations/additional info |
| Category: | Performance |
| Primary Component: | BW-SYS-DB-ORA BW ORACLE |
| Secondary Components: | SCM-APO-FCS Demand Planning |
| BW-SYS-DB BW Database Platforms |
Affected Releases
|
Correction delivered in Support Package
|
Related Notes
1 comment:
The post is really informative, you have discussed the primary things that one should kept in mind There are numerous ways you have stuffed here and share your awesome information. SAP APO Online Training
Post a Comment