Basis and others: 1707239 - Analyze Query Performance with Statistics Data in RSRT

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.

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:

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.

6. The second tab is about the 'Aggregation Layer'. It gives a list of all SQL/BIA accesses that were executed.

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.

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

1707239 - Analyze Query Performance with Statistics Data in RSRT

3 comments: