SWLT: SQL Performance Tuning Worklist tool

The SQL Performance Tuning Worklist tool (transaction SWLT) enables you to find ABAP SQL code that has potential for performance improvement in productive business processes. This tool combines new ABAP code scans (ABAP Test Cockpit or Code Inspector) with monitoring and analysis utilities (SQL Monitor and Coverage Analyzer), and automatically creates a condensed worklist. The resulting findings allow you to rank the worklist according to specific performance issues and your business relevance.

Prerequisites

• To use the complete functionality of this transaction, you need the following authorizations in the current ABAP system:
  • As a minimum, the display authorization profile for object S_DEVELOP (activity: 03).
  • The authorization profile for object S_ADMI_FCD with the value SQMA for administration activities and the value SQMD for data read access.
• As a minimum, one of the following versions of the target ABAP system (in the case of remote access):
  • SAP NetWeaver 7.40 SP02, or higher
  • SAP NetWeaver 7.31/7.03 SP09, or higher
  • SAP NetWeaver 7.02 SP14, or higher.

Recommendation

For the execution of static checks (ABAP Test Cockpit or Code Inspector), we recommend using the pre-defined Code Inspector variant PERFORMANCE_DB.

In addition, if you are only interested in static check results for SQL statements that have been recorded by the SQL Monitor, we advise you to use the Code Inspector object collector CL_CI_COLLECTOR_SQLM_SNAPSHOT (“Objects of SQLM-Snapshot”).

This object collector allows you to limit the inspection’s object set to those objects that are contained in a previously recorded SQL Monitor snapshot. This, in turn, helps to optimize the performance and memory consumption of the scan and analysis tools used.

Features

Options for Object Selection

• On the General tab, you can select development objects that are suitable for analysis using various criteria. For example, you can limit the objects to a particular set of packages or a particular object type.

Options for Static Checks

• On the Static Checks tab, you can make adjustments for the selection and display of results of static checks (ATC or Code Inspector).

Options for SQL Monitoring

• You can either use snapshots that already exist in the system for SQL monitoring data or, if required, generate new ones.

Managing Snapshots of SQL Monitor Data

• You can initiate snapshot management either from the SQL Monitor tab using the Manage/Create Snapshots button or by executing the report SWLT_SQLM_CREATE_SNAPSHOT. You can generate new snapshots directly from the local system or by specifying the RFC destination. Alternatively, you can also upload a snapshot file previously created with the report RSQLM_DOWNLOAD_DATA.

Integration of Code Coverage

• If no SQL data is available in the system, you can evaluate the data from the Coverage Analyzer.

Compressed Overview Display

• In accordance with your selection options, you will get a list of results (findings) that, in the standard version, are sorted according to the number of executions or the execution time. From here you already have the possibility to detect the performance hotspots. From the Overview display, you can double-click the line in question and request the details view for runtime data and data from the respective static check.

Detailed View with SQL Monitoring Data

• Using the SQL Monitor Results display function, you can perform a more advanced breakdown or analysis of the runtime data; in particular, you can determine the entry point of the processes or DB operations.

Detailed View with Results from Static Checks

• Using another detailed view, you can analyze the findings from the static check with regard to their relevance for possible code optimization. Here, the statements on priority, severity, and estimated work effort are of particular relevance.

Activities

The typical procedure for working with SWLT transaction involves the following steps:

1. Launch transaction SWLT.
2. On the General tab, select the development objects that are suitable for analysis.
3. Activate the data source option in question, and specify the corresponding settings for the SQL monitoring data and for the static check findings.
4. Choose Execute (F8).
5. On the Result Overview screen, identify and analyze the performance hotspots.
6. On the Result Overview screen, double-click the corresponding table row to open the detailed view.
7. Analyze the details for the runtime data and the static check findings.