7.5System-Wide Analyses

System-wide SQL analyses help you identify expensive SQL statements in the entire system. At first, you don’t require any information about the application. During the optimization process, however, you may find such information helpful or even necessary. In this section, we’ll show you how to conduct such analyses in the DBA Cockpit. You’ll also learn about the SQL monitor for system-wide SQL analysis and the runtime check monitor, which are both available as of AS ABAP 7.4.

7.5.1Database Administration Cockpit

The DBA Cockpit contains all the functions needed for database monitoring and database administration. Here, you find an overview of the current database status as well as error messages and warnings. Functions are also available for performance analysis, configuration, database jobs, diagnostics, and system information. These are a subset of the functions available in SAP HANA Studio for analyzing the SAP HANA database. The following sections present the most critical functions of the DBA Cockpit.

[»]Note

SAP HANA Studio and other tools for system administration are presented in SAP HANA Administration by Richard Bremer and Lars Breddemann (SAP PRESS, 2014).

Under Current Status • Overview, you can find information about the current database status. For example, you’ll see the current CPU and memory consumption in the database. Current warnings are displayed on the Alerts screen.

The Performance area provides various monitors for performance analysis. Under Threads, you see those threads that are currently active on the database. Jobs provides which database jobs are currently active and their current processing status. The Expensive Statements view contains a list of SQL statements if this particular trace is activated. This trace records SQL statements that exceed a specific runtime specified by the database administrator. The SQL Plan Cache area displays aggregated information about the SQL statements that have been executed. This information is taken from the SQL cache in the database. All executed SQL statements are stored in the SQL cache, and the runtime data associated with these statements are entered there. If, however, some data is displaced due to a lack of space or because new SQL statements are created, the data may be incomplete. In other words, only some of the execution data created since the database started is available. We’ll take a closer look at these two functions. The System Load History view enables you to display different KPIs graphically, such as CPU or memory consumption of the database. You can choose different periods of time and display the data of previous hours or days.

The Configuration area contains information about the Hosts of the database, the Services available, trace configurations, and configuration files.

The Jobs area contains information about jobs for database administration. There is as central calendar and a DBA Scheduling Calendar. The results of these jobs are also available in the DBA Logs.

The Diagnostics area contains a range of expert functions. For example, the SQL Editor can be used to execute read-only SQL statements. Queries in relation to monitoring views and application tables can be executed in this way if the relevant authorization exists. The Tables/Views area contains the definition and runtime information for database objects. Here, you can use the Procedures function to view the database procedures available. The Diagnosis Files and Merged Diagnosis Files areas enable you to view important trace and diagnosis files in the database and to merge them together to arrange information from different files in chronological order. You can use the Backup Catalog function to view information about database backups. You’ll also find various pieces of information about locks and other different trace CE plan operators, which we won’t describe in greater detail here.

The Lock subfolder provides different views of database locks. The following monitors are available, for example: Blocked Transactions, Table Locks, and Record Locks.

In the System Information area, you can query different monitoring views. Information about Connections, Connection Statistics, Transactions, Caches, Large Tables, SQL Workload, License, and Data Browser for System Tables is available here.

The System Landscape area provides information on the system configuration and the database connections.

Now we’ll show you how you can use the overview, threads, SQL cache, and the expensive statement trace in the DBA Cockpit to analyze the load on the SAP HANA database.

The upper area of the overview (General System Information), which you call using Transaction ST04, shows whether all of the database services are active and when they were started, as well as whether they concern a distributed system. Furthermore, you obtain information about the database version and operating system. The upper-right area shows any current alerts. If there are, you can click the information displayed here to navigate directly to the alerts.

The middle and lower areas of the overview contain information about the current load on the main memory and CPU (on the basis of the database and the host on which the database is running). These areas also contain information about the hard drive or data, log, and trace areas (Figure 7.31). All of this information is based on the time you called the overview or chose Update.

Transaction ST04: Overview

Figure 7.31Transaction ST04: Overview

In the Threads area, you see which threads are active in the database. This area also contains information about the service, type, and method executed. The recently executed SQL statement, previous runtime, caller, and the name of the user who executed the statement are displayed. An example of a thread is shown in Figure 7.32.

Transaction ST04: Threads

Figure 7.32Transaction ST04: Threads

In the upper area of the SQL cache, you can specify filters for the SQL statements to be displayed (see Figure 7.33).

SQL Cache

Figure 7.33SQL Cache

You can execute the following functions for each SQL statement:

The SQL cache contains an entry for each unique SQL string. Therefore, different call points within ABAP programs can be aggregated into one entry if they concern exactly the same SQL statement. A large amount of information can be retrieved for each entry (e.g., the number of executions, the execution times, the number of data records transferred, the time when the last execution was performed, and the times relating to database locks).

The Expensive Statements view contains similar functions to the SQL cache, but it works according to the trace principle. In other words, you must configure which SQL statements you want to record (e.g., all SQL statements that take longer than three seconds to execute). Such statements are written to a restricted memory area within the database. Whenever this area is full, old entries are simply overwritten, so space is always available. This particular function has the advantage over the SQL cache in that individual statements that satisfy the configuration criteria are recorded without needing to be aggregated. Therefore, information about the application user (the user in the SAP system) is also available here.

7.5.2SQL Monitor

SQL Monitor is a new development that is available as of AS ABAP 7.4 and was ported up to Release 7.00 (see SAP Note 1885926). SQL Monitor basically collects, aggregates, and persists runtime information about SQL statements in the database interface (DBI). The SQL cache in the database provides database-specific information about the SQL statement (e.g., the number of pages read or the I/O and CPU times required), but information about the ABAP program and the call context in which the statement was executed is available in the SQL Monitor. Consequently, these two data sources complement each other and provide specific additional information about SQL statements. In this section, we’ll show you how to activate the SQL Monitor and explain which data is available for analysis.

Recording

To launch the SQL Monitor, call Transaction SQLM. Here, you can activate the SQL Monitor on every application server or on specific application servers only. You can also define both the period in which the recording will take place and an upper limit for the number of records. Data recording stops as soon as the date or number of records has been reached. Figure 7.34 shows the initial screen of the SQL Monitor after it has been activated.

SQL Monitor: Activation

Figure 7.34SQL Monitor: Activation

Once activated, data are collected and aggregated for each SQL statement executed (Open SQL, Native SQL, database procedures such as AMDP, EXPORT and IMPORT statements, and system activities such as loading table buffers). The data are collected in the main memory and written asynchronously to a database table. The data are available for analysis in Transaction SQLM about an hour after recording. The data are also provided in a background job for analysis to make a minimal impact on the runtime.

Analysis

To analyze the data, choose Display Data. The selection screen shown in Figure 7.35 appears.

SQL Monitor Analysis

Figure 7.35SQL Monitor Analysis

In the results area, you can set a time restriction:

In the areas below, you can filter data according to the following information:

You can choose from the following settings in the Aggregation area:

Finally, you can also sort by various criteria and restrict the number of data records displayed. Display Technical Records enables you to specify whether system activities, such as loading table buffers, are displayed.

The results list provides different information depending on the aggregation you selected. We’ll describe the most critical columns of each list.

The following columns are particularly important for the aggregation by request (see Figure 7.36):

Transaction SQLM: Results List by Request

Figure 7.36Transaction SQLM: Results List by Request

The other columns show the number of SQL statements as well as various statistics (minimum, mean, and maximum values as well as standard deviation for various columns).

If you double-click a row in this view, the system takes you directly to the nonaggregated list of SQL statements for this request. The columns of this list are presented in the following.

The following columns are particularly important for aggregation by source code position or if no aggregation takes place (see Figure 7.37):

Figure 7.37 shows a sample result list in the SQL Monitor.

Transaction SQLM Results List by Call Position

Figure 7.37Transaction SQLM Results List by Call Position

The columns include information about the program, package, and the modularization unit as well as various statistics (minimum, mean, and maximum values as well as standard deviation for various columns).

[»]Time Series

If you selected a specific time window (By Time Interval in the selection screen), the Display Time Series function is available (see Figure 7.37), which allows you to view the most critical KPIs of an SQL statement aggregated by hours. This function is very helpful to evaluate whether an SQL statement always behaves the same or is subject to changes in the timeline. This function isn’t available if you analyze the current measurement only.

The integration of the SQL trace (Transaction ST05) is a very powerful function of the SQL Monitor. A prerequisite for this function is that you have the authorization (create, activate, etc.) for logpoints (S_DYNLGPTS authorization object). This function permits the activation of an SQL trace for a specific call point of the SQL statement. The distinguishing feature is that you can now activate several SQL traces for each application server. Only the SQL statements activated in the SQL Monitor are written to the SQL trace. However, the limits of Transaction ST05 still apply, which means that the trace file can still be overwritten if a high number of trace records exists. We’ll show you how to activate and analyze the SQL trace for a specific SQL statement from SQL Monitor.

To activate an SQL trace, click on the Activate/Deactivate SQL Trace button (refer to Figure 7.37). Note that this button is only available if you have the authorization for logpoints. In the following dialog window (see Figure 7.38), you can restrict the trace to users or application servers and specify a time for trace deactivation. You can also determine whether the SQL trace will be recorded with a call stack. The Filter by Request setting defines that the selected SQL statement is recorded at the selected entry point in the SQL trace. With Maximum Executions per Session, you can specify that the system records only a specific number of executions of the SQL statement. Note that this isn’t a global counter per application server but only a counter per session. Consequently, many sessions will record the number of executions specified here. This is important, for example, if the SQL statement to be recorded is located in an RFC function module that is called frequently. Click on Save to activate the SQL trace.

Activating the SQL Trace

Figure 7.38Activating the SQL Trace

After you’ve activated an SQL trace, the Display SQL Trace Activations and Display SQL Trace buttons are displayed. In addition, the rows for which an SQL trace was activated are displayed in green (if the trace is deactivated again, the rows are displayed in yellow).

If you click on the Display SQL Trace Activations button, the system takes you to the dialog window shown in Figure 7.39. Here, you can deactivate the SQL trace again, change the settings, or delete an entry.

Activated SQL Traces

Figure 7.39Activated SQL Traces

The Executions column in Figure 7.39 shows how often an SQL statement has been executed since the activation of the trace. When you select Display SQL Trace, the system takes you to the analysis in Transaction ST05. The system may prompt you for which server the trace records are to be displayed if the trace has been activated for several servers and if the SQL statements have been recorded on more than one server. Transaction ST05 then starts on the server selected and includes all necessary filters to display the selected SQL statement. You don’t need to make any changes to the selection and can run the statement directly. Note that the data recorded may have been overwritten by other Transaction ST05 traces (from the SQL Monitor or directly in Transaction ST05) if no trace records are displayed. It’s therefore important to keep the number of executions to be recorded as low as possible (refer to Figure 7.37).

You can use the SQL Monitor to conduct some interesting analyses. The following examples serve to give you some points of reference:

The SQL Monitor helps you quickly analyze the points at which database tuning is necessary and promising.

Because the data in the SQL Monitor is periodically stored in a database table, no data is displaced here (which is the case with the SQL cache). You can link the data in the SQL Monitor to the results of a static code analysis, thus providing runtime information for the static check results. Consequently, it becomes apparent quickly where an optimization would be most beneficial. We’ll show you how to do this in Section 7.6.

Integrating the SQL trace (Transaction ST05) in the SQL Monitor is a very powerful function that allows you to record only the interesting parts of an application in the SQL trace.

The SQL Monitor is a comprehensive tool for determining an SQL profile for an application or an entire system. For each call point, an entry is created for each table and application. Because the data are written asynchronously to the database tables, performance isn’t negatively affected. Furthermore, no information is lost, and the additional information makes it possible to draw more accurate conclusions in relation to the ABAP program and the context in which it was executed.

7.5.3Runtime Check Monitor

The runtime check monitor (Transaction SRTCM) lets you activate specific checks that are run at the runtime of ABAP programs. You can then analyze the results of these checks.

The transaction currently comprises the following two checks:

The following shows how to activate the monitor and then analyze the data.

Recording

After you’ve started Transaction SRTCM, you receive an overview of the status of available checks (see Figure 7.40).

Transaction SRTCM: Overview

Figure 7.40Transaction SRTCM: Overview

You can view the activation status and further information on the two checks. You can select the information icon to go to the documentation of the checks. To activate a check, click on Activate Globally or Activate for Selected Servers, depending on whether you want to run the check on all servers or specific servers only. In the dialog window that appears, you can specify the time at which the check is to be deactivated again.

Analysis

Click on Display Results (refer to Figure 7.40) to analyze the data. A selection screen appears in which you must select the desired check in the Runtime Check Selection area and then click Display Results (see Figure 7.41). Alternatively, you can call Transaction SRTCMD to start directly from here.

This results in a list of check results (see Figure 7.42). When you click on the link in the Include or Incl.Line columns, you can directly navigate to the ABAP source code. The Changed column indicates whether the ABAP source code was changed between the check and the analysis.

Transaction SRTCM: Selecting Results

Figure 7.41Transaction SRTCM: Selecting Results

Transaction SRTCM: Displaying Results

Figure 7.42Transaction SRTCM: Displaying Results