Sunday, August 1, 2010

Difference between DB and BW statistics

Terms like database (DB) and BW statistics are often referred in SAP documentation, Notes or even in SDN forums. We always hear that it is important to maintain statistics in order to achieve good performance of SAP systems. Especially in BW area performance topic is very important. Here it is often discussed that we must keep the DB statistics all the time for SAP BW. Then we hear that BW statistics mull be keep up to date as well. You can get easily confused with mixing a meaning of those two terms.

So basically what DB statistics is all about? Underlying database systems are using sophisticated algorithms to determine “a path” how to execute SQL statements. In detail a path is comprised of type of DB access (index access, full table scan, join types, memory requirements, table stats, index stat, column stat, histogram, partitioning, parallelism, clustering, etc.) how particular SQL statement is performed. In order to determine it DB engine is evaluating DB statistics which is basically about evaluation of how many I/O DB operations would take to perform DB statement with different DB access. The DB access with the fewest I/O DB operations is then executed to fulfill SQL statement. DB statistics is depended on type of used DB platform. E.g. in ORACLE there is a component of DB engine called Cost-Based SQL optimizer (CBO). Therefore sometime we call this type of statistics as an Optimizer statistics. This tool is in charge of SQL statement execution governing. This component is evaluating DB statistics. On other DB platform such a component is commonly called Query optimizer.

Now the questions would be how can we build DB stat? DB statistics can be build from BW perspectives within process chains via process called “Construct Database Statistics”. From basis point of view most common is to use BRTOOLs to build this statistics in case of ORACLE DB. Another option is to use transaction DB20 or report RSANAORA.

For more information about DB statistics see SAP Note 588668 - FAQ: Database statistics.

What about other term - BW statistics? It is a part of technical Business Content which helps to evaluate performance of SAP BW system. There are two main areas of performance evaluation: OLAP processor and Warehouse Management (WM).

Within OLAP processor gives an information how BW reporting is performing. A way how statistics data is collected If is like following. Every navigational step within the reporting that requests data from the database is saved in transparent table in BW backend (tables RSDDSTATHEADER, RSDDSTATINFO, RSDDSTATEVDATA). This includes characteristics, navigation attributes and hierarchies were involved. Here an event concept is used. Event is uniquely identifying each step in the process. From DB tables statistics data are loaded into BW statistics InfoCubes via technical process chains.

In case of WM statistics or Data Load statistics it is determining performance of loading, transforming the data and all the others staging operations. In principle system is gathering the data about process chains, DTPs and InfoPackages. Here e.g. time of execution is stored along other data load statistics data. Table where data is written is RSDDSTATWHM. WM statistics is customized via transaction RSDDSTAT.

BW statistics is a part of BI Administration Cockpit. See 934848 - Collective note: (FAQ) BI Administration Cockpit, for BW 3.x see: 309955 - BW statistics - Questions, answers and errors.

Notice that BW statistics was redesigned in BW version 7.x and it does use different tables comparing to BW 3.x.

More information:

588668 - FAQ: Database statistics

555030 - Deactivating BW-initiated DB statistics

1013912 - FAQ: Oracle BW performance

No comments: