Displaying Query Runtimes

Use

In the BW workload monitor, you can display the workload in different analysis views which highlight the workload from a particular perspective. One of these aspects is the question of which reporting queries have the longest runtimes. You can use the workload overview to answer the following questions, among others:

·        Which queries for the relevant InfoCube have the longest runtimes?

·        What proportion of the total runtime is taken up with database and OLAP analysis for each InfoCube?

·        For which query is most data usually selected and transferred to the front end?

·        Does the same reporting request always have high OLAP initialization times?

·        How often was a particular query read from the OLAP cache?

 

The Query Runtimes analysis view was completely revised with SAP Note 840763. This means that certain functions described here are not available with earlier versions.

Procedure

...

       1.      Start the Workload Monitor by calling transaction ST03.

       2.      Choose the Expert or Service Engineer user mode.

       3.      Choose the period to be analyzed in the BW Workload tree.

       4.      Under Analysis Views, choose the profile Reporting, and then the Query Runtimes option.

       5.      If you are only interested in queries that were started on particular front ends, choose the more detailed front end-specific analysis view.

       6.      The system generally displays the usual output area, in which the requested workload is displayed in an ALV Grid Control.

       7.         Within the profile, you have the following navigation options:

Ў        Change the level of aggregation

Ў        Select the OLAP cache utilization

Ў        Display specific statistics data on corresponding tab pages

Ў        Double-click a row to display details for this row

Result

The output area contains two aggregation levels with the following characteristics:

Aggregation Level

Description

Display After a Double-Click

InfoCube

Display of the workload data, aggregated to InfoCube level

Display of aggregated statistics of all queries for the relevant InfoCube

Query

Totaled display of the statistics data for each query

Display of all query navigation steps for the selected query

By default, if you double-click at query level, additional information from the query statistics table RSDDSTAT is displayed.

To obtain all details, you can use the Standard Info/RSDDSTAT Info button to display the complete statistics information. This is only possible on the All Data tab page after double-clicking a row. Note that displaying a large number of individual record details can lead to long runtimes for programming reasons.

The output area also contains four different options for the OLAP cache with the following characteristics:

OLAP Cache

Meaning

All Data – Totaled

No differentiation of where the data came from

Both – Cache-Independent

Navigation steps that read the data from the database or from the OLAP cache are totaled separately

No OLAP Cache

Only navigation steps for the queries that read data directly from the database are aggregated

Only OLAP Cache

Only navigation steps for the queries that read data directly from the OLAP cache are totaled

A navigation step is classified as using the OLAP cache if all three of the following conditions are met:

Condition

Meaning of the Fields

QTIMEDB = 0

Time on the database to select the transaction data and transfer it to the application server

QDBSEL = 0

Number of records read on the database

QDBTRANS = 0

Number of records transferred from the database to the server

All checked fields are read from the reporting statistics table RSDDSTAT.

The output area also contains three tab pages in each case with the following characteristics:

Tab Page

Description

Share of Runtime

Overview of the runtimes as a percentage share of the total runtime

Average Times (AVG)

Average runtimes per navigation step

All Data

Display of all available data

If a query name or InfoCube name cannot be determined, the placeholder <unknown> is inserted in the relevant column instead.

  Workload Monitor start page, Analysis Views of the BW Workload Monitor