Symptom
- A BW query has very long runtime, you want to improve its performance.
- When you run this BW query in transaction RSRT, the bad performance can be observed.
Environment
- BW system
- Release independent
Reproducing the Issue
1. In transaction RSRT, enter the query name and execute it.
2. If the query needs variable input, provide the necessary values in the variable screen. Then click 'Execute' button on variable screen to run the query.
3. It takes quite long time to get the query result displayed.
2. If the query needs variable input, provide the necessary values in the variable screen. Then click 'Execute' button on variable screen to run the query.
3. It takes quite long time to get the query result displayed.
Resolution
1. Execute the query again in transaction RSRT. This time use 'Execute and Debug' button instead.
2. In the debug options popup box, switch on 'Display Statistics Data'. You may also switch on 'Do Not Use Cache'. This will make the query skip OLAP Cache. In this way, you can analyze the query performance with database access or BWA access.
3. If the query needs variable input, provide the necessary values in the variable screen. Then click 'Execute' button on variable screen to run the query.
4. After you get the query result displayed, click the 'Back' Button.
5. You will get the statistics data displayed with different tabs. The first tab refers to 'Frontend/Calculation Layer' data. In the column 'Duration' the runtime for each event is given in seconds. You can sort this column descending to find out the top time consuming events.
The most important BEX OLAP Statistics Events are:
In the column 'Viewed at' you can see how long it took.
In column 'InfoProvider', 'Basic InfoProvider', 'Aggregate' and 'Table Type', you can see exactly where the data comes from. For a query built on a multiprovider, the column 'Basic InfoProvider' shows its part providers. In column 'Aggregate', you will see a 6 digit aggregate name if the data is from a database aggregate, or you will see a BWA index naming as <cubename>$X if the data is from BWA. Table Type E means, the data is from an E table. E tables contain compressed data of a cube. Table Type F means, the data is from an F table. F tables contain uncompressed data.
In Column 'Records, Selected' and 'Records, Transported', you will see exactly how many records are read and transported from each source.
For the example given here, we can conclude the following facts:
Query is built on multiprovider GL_MP2. It selected 7 records and transported 7 records from E table of aggregate 100993 for part provider GLCUBE2. It selected 1000 records and transported 588 records directly from F table of part provider GLCUBE3. It reads 26 records and transported 12 records from BWA index GLCUBE$X for part provider GLCUBE.
If you identify the performance bottleneck is the long time spent on a certain part provider, you may perform SQL statement analysis as per Note 1681396 andNote 1707301.
7. The third tab 'BWA Access' will show up if the query gets data from BWA. For this example, the time statistics for access GLCUBE$X is listed here.
2. In the debug options popup box, switch on 'Display Statistics Data'. You may also switch on 'Do Not Use Cache'. This will make the query skip OLAP Cache. In this way, you can analyze the query performance with database access or BWA access.
3. If the query needs variable input, provide the necessary values in the variable screen. Then click 'Execute' button on variable screen to run the query.
4. After you get the query result displayed, click the 'Back' Button.
5. You will get the statistics data displayed with different tabs. The first tab refers to 'Frontend/Calculation Layer' data. In the column 'Duration' the runtime for each event is given in seconds. You can sort this column descending to find out the top time consuming events.
The most important BEX OLAP Statistics Events are:
- 3010 'OLAP: Query Gen.': This event measures the time spent to check the query definition and to generate the query if necessary.
- 4500 Authorization Check Selection: This event measures the time taken to carry out the OLAP authorization check.
- 9000 Data Manager: This event measures the time spent in the data manager, you can find more detailed data on tab Aggregation Layer.
- 9010 Total DBTRANS: Total Number of Transported Records
- 9011 Total DBSEL: Total Number of Read Records
- 3110 'OLAP: Data Selection(OLAP_BACK)': This event measures the time taken to sort the data read from Data Manager according to structure element selections or restricted key figures. The calculation of virtual key figures happens here as well.
- 3200 'OLAP: Data Transfer(RRK_DATA_GET)': In this part, many OLAP features are processed, e.g. exception aggregations, formulas calculation, and the correct number of decimal places for the data cells is determined.
- 3900 OLAP: Read Texts: Texts are read to sort the data. This event measures the time taken to call the text read.
In the column 'Viewed at' you can see how long it took.
In column 'InfoProvider', 'Basic InfoProvider', 'Aggregate' and 'Table Type', you can see exactly where the data comes from. For a query built on a multiprovider, the column 'Basic InfoProvider' shows its part providers. In column 'Aggregate', you will see a 6 digit aggregate name if the data is from a database aggregate, or you will see a BWA index naming as <cubename>$X if the data is from BWA. Table Type E means, the data is from an E table. E tables contain compressed data of a cube. Table Type F means, the data is from an F table. F tables contain uncompressed data.
In Column 'Records, Selected' and 'Records, Transported', you will see exactly how many records are read and transported from each source.
For the example given here, we can conclude the following facts:
Query is built on multiprovider GL_MP2. It selected 7 records and transported 7 records from E table of aggregate 100993 for part provider GLCUBE2. It selected 1000 records and transported 588 records directly from F table of part provider GLCUBE3. It reads 26 records and transported 12 records from BWA index GLCUBE$X for part provider GLCUBE.
If you identify the performance bottleneck is the long time spent on a certain part provider, you may perform SQL statement analysis as per Note 1681396 andNote 1707301.
7. The third tab 'BWA Access' will show up if the query gets data from BWA. For this example, the time statistics for access GLCUBE$X is listed here.
See Also
Note 1681396 Query Performance
Note 1151957 Interesting facts about the OLAP Processor/Analytic Engine
Note 1548125 Interesting facts about Inventory Cubes
Note 1475193 Warnings in transaction RSRT -> Technical Info
Note 1151957 Interesting facts about the OLAP Processor/Analytic Engine
Note 1548125 Interesting facts about Inventory Cubes
Note 1475193 Warnings in transaction RSRT -> Technical Info
Keywords
performance, long run time, query, OLAP, DM, Data Manager, bad performance, time consuming, hanging, stuck, slow.
Header Data
| Released On | 05.06.2012 06:24:28 | ||
| Release Status | Released to Customer | ||
| Component | BW-BEX-OT-OLAP Analyzing Data | ||
| Other Components |
| ||
| Priority | Normal | ||
| Category | How To |
Product
|
References
This document refers to:
SAP Knowledge Base Articles| 1707301 | Query Performance Tuning with SQL Statement Analysis in RSRT |
| 1548125 | Interesting facts about Inventory Cubes | |
| 1681396 | Query Performance | |
| 1475193 | Warnings in transaction RSRT -> Technical Info | |
| 1151957 | Interesting facts about the OLAP Processor/Analytic Engine |
3 comments:
Wooh this is very informative article. Thank you so much
best online MSBI training
online abinitio training in usa,uk,india,canada
selenium training
best R programming online training
SAP HANA online training in india
Usually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job Man learn Oracle SOA Online Training
power bi training institute
tableau training
hyperion online training
Post a Comment