When you are migrating to SAP HANA, certain tools can quickly tell you what programs need to be adapted to SAP HANA standards. In the second part of my series on SAP HANA, I cover the main tools provided in the newest releases and how to use them to identify spots in custom SAP ERP HCM programs that are potential candidates for improvement for the SAP HANA platform.
Included are the three most important tools for improving performance for SAP HANA: Code Inspector for SAP HANA, the SQL Monitor (transaction code SQLM), and the Performance Tuning Worklist (transaction code SWLT). You see examples of problematic statements and how they are identified by these tools.
Introduction to Optimization Tools
Rewriting the SAP ERP HCM program code according to the recommendation shown in the first part of this series is not very difficult. The harder, trickier part is to find the code portion that is to be changed, as there could be many programs involved. Some of the tools that SAP uses to improve its own standard code are now available for users and are very important when migrating to SAP HANA, as they allow you to identify improvement areas pertaining to correctness and performance.
Before you migrate to an SAP HANA system, it is essential to test the performance (runtime) of programs and check whether any of the statements take too much time or have any room for improvement. For example, you might identify SQL statements that are inefficient. The tools you can use to do this may be static-check based—they scan the code of the program and do not check its execution, such as the Code Inspector—or they may rely on the actual execution of a program or set of programs for their analysis, such as SQL Trace and SQL Monitor. The SWLT transaction code combines the results of the Code Inspector and the SQL Monitor to determine the statements in the program that might need performance improvement.
This SWLT tool combines static checks, such as those from the Code Inspector or the ABAP Test Cockpit, with run-time data, such that gathered by the SQL Monitor. The static check is performed in the DEV or QAS systems, whereas the run-time data (SQLM) may come from a production system or the QAS system. This gives you a list of performance-improvement SQL statements in order of priority.
The most important tool is the ABAP Code Inspector. Again, this is a static check tool; it scans the source code of programs, but does not execute them. The various checks that can be carried out are logically grouped into categories. These may or may not be performance related.
Via the Code Inspector, you create an inspection by executing transaction code SCI (Figure 1).
The Code Inspector initial screen
The Code Inspector has several check categories. You can choose the checks and group them into new variants or use standard SAP system variants that allow you to specify the aspects you want to inspect. In Code Inspector, the following categories are relevant for the functional corrections mentioned in part one of this series:
- Critical statements: Native SQL and database hints
- Use of the ADBC interface: Native SQL- and database-related administrative statements
- SELECT/OPEN CURSOR without ORDER BY: This helps identify statements in which data is read from database tables without an ORDER BY clause, or where there is no SORT statement used before the READ OR DELETE statement
- Search ABAP statement patterns: This allows developers to look for index-specific coding, including use of DD_INDEX_GET and the function module discussed in the previous article
There are other performance-related checks and categories that may also be used. In addition to this, there are many other checks and variants that allow you to pinpoint areas in SAP ERP HCM programs that do not comply with performance-optimization guidelines.
The variant FUNCTIONAL_DB, provided by SAP, allows you to check for the problems mentioned in part one of the article and the points mentioned above. The variant is shown in Figure 2.
The use of database hints and Native SQL are within the Critical Statements check.
(Note: There is coding that cannot be analyzed via Code Inspector. You need to manually check such code to make sure that it is in line with the recommendations for SAP HANA. A good example of this is the code written in the program line elements within SAP Smart Forms or SAPscripts.)
Another important category within the variant FUNCTIONAL_DB is Search Functs., as shown in Figure 3.
The Find ABAP Statement Patterns within the Search function allows you to search for CALL FUNCTION statements that call the function modules DB_EXISTS_INDEX and DD_INDEX_NAME, as shown in Figure 4.
CALL FUNCTION statements
Let’s look at how to use the Code Inspector to create an inspection based on the variant FUNCTIONAL_DB. There are three main parts of this screen: Inspection, Object Set, and Check Variant.
Every static check or inspection that is carried out is stored under a unique name, such as ZINSPECTION1, ZINSPECTION2, and so on. You can refer to this inspection later by using this name. Next, you have the object set criteria. It may be possible to use the Code Inspector to inspect a single program, function module, function group, a number of programs, or all objects with a particular transport request. The Check variant has already been discussed in the earlier part of this section.
(Note: The FUNCTIONAL_DB standard variant allows you to check areas relevant to SAP HANA correctness. Alternately, you can copy a standard variant and make changes instead of making your own check variant from scratch.)
Let’s now carry out the inspection. Enter a suitable name in the Inspection field. Then click the create icon . (Do not enter anything in the Object Set field or the Check Variant field in Figure 1 as these will be entered in the next screen.) This action takes you to the screen in Figure 5.
Place the variant FUNCTIONAL_DB in the variant field and make sure you select the global icon next to the FUNCTIONAL_DB field. Either use an Object Set or use the Single radio button to specify a single program.
For the Object Set, you can either create a new Object Set or specify an existing one. For creating a new Object Set, execute transaction code SCI, enter a suitable name, and click the create icon. In the Programs and Requests tab, enter program YTEST_ORDER_BY as the program to be inspected. You can also use Y* to inspect all programs to be inspected that have names beginning with the letter Y. Click the Save button, and click the back icon to return to the main screen of transaction code SCI.
The variant is decided and the Object Set is created, so now you simply create an inspection. Enter a name in the Inspection field and click the create icon.
Once the inspection is carried out, a green symbol appears denoting its success, as shown in Figure 6.
Code Inspector upon completion of the inspection
(Note: Transaction code ATC, the ABAP Test Cockpit, combines the popular quality-checking tools, such as the extended program check, ABAP unit, and the Code Inspector checks, to improve the quality of the code.)
The SQL Monitor allows you to trace and log all database-related SQL statements within an executed ABAP program or programs and to pinpoint statements that are potential candidates for improvements. You access the SQL Monitor via transaction code SQLM. To use the SQL Monitor, follow these steps:
Execute transaction code SQLM, the SQL Monitor (Figure 7).
Make sure that the monitoring is active by clicking the All Servers button under Activation/Deactivation. The program asks for an activation expiration based on the date or for a limit to the number of records to be generated in the monitoring file. A pop-up appears (not shown) with the expiration date field that is to be filled.
This sets the Activation State to Globally Active, as shown in Figure 8. Click the Create/Manage Snapshot button (not shown).
SQL Monitor activated
The screen shown in Figure 9 appears.
The snapshot creation screen
This is the main snapshot management screen in which you need to enter a suitable description. You can specify the type or name of the various objects in which you are interested. Then select the Local System radio button from the Data Source block and provide the Package details. Keep the settings at the defaults. Then click the Create Snapshot button (not shown). Alternately, press the F8 key.
A message Snapshot successfully created appears, as shown in Figure 10.
Message of snapshot creation
Next, execute the SAP ERP HCM programs or reports that you want to analyze. You run these programs via transaction code SE38 or from the HCM program transaction code. Since the SQL Monitor is switched on, it records the SQL statements of the program you execute.
Once this is done, execute the RSQLM_UPDATE_DATA program. Execute transaction code SE38, enter the name of the program in the Program field, and execute the RSQLM_UPDATE_DATA program. A screen appears as shown in Figure 11. Make sure that all the check boxes under the Options section are checked.
The next step is to make this SQL Monitor snapshot available to transaction code SWLT for the subsequent step. Click the Export Data button in the main screen of transaction code SQLM to display the screen shown in Figure 12.
Export SQL Monitor to the Data File
In this screen, click the Export button. A message appears denoting the success of the export. The result file can then be downloaded into a zip file on a local PC.
Transaction Code SWLT
Now that the SQLM snapshot has been created, use the SQL Performance Tuning tool to combine the results of the Code Inspector and the SQL Monitor. Execute transaction code SWLT. A screen appears as shown in Figure 13. There are four tabs in this screen: General, Static Checks, SQL Monitor, and Coverage Analyzer.
Transaction code SWLT (General tab)
The General tab allows you to restrict the set of objects for which the SWLT is to be run. In the General tab, you can set a filter. For example, you can restrict the monitoring to a given object, such as ZHR* for SAP ERP HCM reports. In this tab, there is a check box that determines whether you see all the results from the various data sources or the intersection of the different sources. By intersection I mean the statements that occur in both the Code Inspector results as well as SQLM SQL monitor results.
(Note: If you are not using the SQL Monitor data, you need the Coverage Analyzer, which I am not using in my example.)
Next, go the Static Checks tab. In this tab, you specify the data source of the static check. This can include the Code Inspector or the ABAP Test Cockpit. In my example, I use the Code Inspector. Select the Use Static Check Data check box. Selecting this check box enables the Data Source block fields, as shown in Figure 14.
Select the Use Static Check Data check box
I assume that the static run has been performed before using this transaction. Click the Select Inspection Result button. A dialog box appears (Figure 15).
Select the inspection
As you will see, the inspection that I carried out is listed. Select the YINSPECTION and press Enter. Once you have chosen an appropriate inspection result, you see the tab is now green, meaning it is enabled.
Next, go to the SQL Monitor tab. Click the Create Manage Snapshot button. Here you can import the existing zip file that was imported from a local file from the SQL Monitor. Alternately you can generate a new snapshot from a local system or via a Remote Function Call (RFC) destination using a different system. Once this is done, click the Select Snapshot button. You see that the SQL Monitor tab is green, showing that the relevant input has been provided. You do not need to make any entries in the Coverage Analyzer tab.
Once the entries are made, click the execute icon. The results are displayed as shown in Figure 16.
SWLT output list
Next you analyze the results of transaction code SWLT. The result is in a combined form; each row in the result represents a static check residing in the Code Inspection results along with the data from the SQL Monitor.
Here are some of the important fields in the output result set:
- DB Executions – The total number of database executions
- Total Execution Time – The time needed for all the database calls
- Tables – The tables that are read as a result of the statement
- DB Mean Time – The average time per statement
- ABAP Source Code Fragment – The code under consideration
- Program Name – The name of the program in consideration
- Check Message – Shows the relevant check message generated as a result of the Code Inspection
The output of transaction code SWLT shows the time taken by the statement and the various checks involved. For simplicity, I have only shown one program and its execution details. In an actual system, you could have a number of programs and statements from which to choose for optimization.
The output has three portions. The upper part shows the complete result set. The lower portion on the left is the SQL Monitor detail section, whereas the detail section of the Code Inspector (Static Check) is on the right. From the result rows, it is also possible to go the relevant program row or table details that generated the entry.
Double-clicking a particular line displays the details as shown in Figure 17.
SQL Monitor result detail
If relevant, selecting a line on the result set shows the detail of the Code Inspector check in the lower right portion. Relevance depends on the scenario and type of statement in question. If you choose the Intersection of the Result Set on the General tab of the SQL Performance Tuning Worklist tool transaction, the output shows only those entries that exist in the SQL Monitor result (i.e., database-related statements). The output contains a column Check Message in which the Code Inspection check message is shown, if applicable.