SQL Execution Analysis - SAP_HANA_Administration_Guide

You can investigate the performance of SQL statement execution with the help of the following functions of the SQL console:

● Execution plan explanation

● Execution plan visualization Related Information

130 P U B L I C

SAP HANA Administration Guide Monitoring SAP HANA Systems

Opening Tables [page 172]

Some monitoring and problem analysis may require you to examine individual tables, for example, the many system views provided by the SAP HANA database. You can open tables and views in different ways. Several viewing options are available depending on what you want to do.

Analyzing SQL Execution with the Plan Explanation [page 338]

You can generate a plan explanation for any SQL statement in the SQL console. You can use this to evaluate the execution plan that the SAP HANA database follows to execute an SQL statement.

Analyzing SQL Execution with the Plan Visualizer [page 338]

To help you understand and analyze the execution plan of an SQL statement, you can generate a graphical view of the plan.

SAP HANA SQL and System Views Reference

8.8.2 Adding User-Defined SQL Statements for System Monitoring

If you have your own SQL statements for monitoring purposes, you can save these statements in the

Administration editor for convenient repeated execution. Statements are saved in an XML file, which you can edit either directly on the System Information tab or offline on your local file system.

Prerequisites

● You have activated the display of user-defined SQL statements for the system in the preferences of the Administration Console under Global Settings.

● If necessary, you have changed the default name and location of the XML file to which user-defined statements are saved when you create them in the Administration editor.

Note

It is also possible to prepare your statements offline in an XML file and to specify this file here. The statements contained in the file then appear automatically in the Administration editor. However, to avoid errors, it is recommended that you create and edit statements in the Administration editor.

For more information about changing the default settings, see Customizing the Administration Console [page 25].

Procedure

1. In the Administration editor, choose the System Information tab.

The predefined SQL statements delivered with SAP HANA are displayed together with any existing statements in the configured XML file (type System and User-Defined respectively).

2. From the context menu, choose Add.

SAP HANA Administration Guide Monitoring SAP HANA Systems

P U B L I C

3. In the User-Defined SQL Statement dialog, specify a logical name and description for the statement, and then enter the statement in the space provided.

4. Save the statement.

Results

The statement is saved to the XML file at the configured location. The syntax is as follows:

<description>Description of my statement</description>

<sql>SQL statement</sql>

</systemtable>

</systemtables>

</systabs>

The statement appears on the System Information tab, where you can execute it by double-clicking, as well as edit and delete it.

Note

You cannot edit or delete predefined system statements.

Related Information

Customizing the Administration Console [page 25]

There are many options available for customizing the Administration Console of the SAP HANA studio.

8.9 Monitoring Disk Space

To ensure that the database can always be restored to its most recent committed state, you must ensure that there is enough space on disk for data and log volumes. You can monitor disk usage, volume size, and other disk activity statistics on the Volumes tab of the Administration editor.

There are two views available on the Volumes tab for monitoring the size of volumes on disk: service and storage type (that is data, log, and trace).

Note

Although trace files are not stored in volumes, they are displayed on the Volumes tab in the Storage view as they consume disk space and therefore need to be monitored.

Table 16: Service View of Volumes

Column Description

Service/Volume The service host and internal port

132 P U B L I C

SAP HANA Administration Guide Monitoring SAP HANA Systems

Column Description

You can expand the host/port to can see the storage area for data and log.

Service The name of the service that has a data and log volume Total Volume Size [MB] Total size of the service's data and log volumes

If you expand the host/port, you can see the size of each volume.

Data Volume Size [MB] Current size of the service's data volume Log Volume Size [MB] Current size of the service's log volume

Path Location of the service's data and log files in the file system Storage Device ID ID of the device on which the data and log files are stored

This can be useful for checking whether or not data and log files are on the same device.

Total Disk Size [MB] Total size of the host's hard disk

Used Disk Size [MB] Amount of disk space used on the host's hard disk as a whole Available Disk Size [%] Available disk space on the host's hard disk

The information shown when you select the Storage view is the same as above. It is simply displayed according to storage type not service. Details of trace files stored on disk are also available in this view.

When you select a row in either view, detailed information about the volume(s) is displayed in the lower part of the screen. In addition to size and usage information, statistics relating to the performance of read/write operations to disk are also available.

Note

Detailed information about nameserver volumes is currently not available.

Table 17: Volume Details View

Tab Page Description

Files This tab page displays the file name and type. It also shows the size of the file and how much of it is currently in use, both in MB and as a percentage of its total size.

The relevance of used size depends on the file type as follows:

● Data files

Used size is the amount of data in the file. As the size of the file is automatically increased with the payload but not automatically decreased, used size and total size may be different.

● Log segment files

Used size equals total size. When a file is full, log entries are written to the next log segment file available. The log segment file's state indicates its availability for reuse. For more information, see the monitoring view M_LOG_SEGMENTS.

● Trace files

Used size is zero for unused trace files and equals total size for used trace files.

SAP HANA Administration Guide Monitoring SAP HANA Systems

P U B L I C

Tab Page Description

Volume I/O Statistics This tab page shows aggregated I/O statistics for the volume since the service was started, for example, number of read/write requests, data throughput, total I/O time, and speed (MB/s). These figures can be useful when analyzing performance problems.

For more information about the meaning of the individual fields, see the monitoring view M_VOLUME_IO_TOTAL_STATISTICS.

Data Volume Superblock Statistics

This tab page displays aggregated statistics on the data volume's superblocks since the service was started.

Superblocks are partitions of the data volume that contain pages of the same page size class.

For more information about the meaning of the individual fields, see monitoring view M_DATA_VOLUME_SUPERBLOCK_STATISTICS.

Data Volume Page Statistics

This tab page displays statistics on the data volume's pages (or blocks) broken down according to page size class. You can analyze how many superblocks are used for the specific size class and also how many pages/blocks are used. The fill ratio enables you to decide whether or not it makes sense to reorganize and release unnecessary superblocks, in other words, shrink the data volume.

For more information about the meaning of the individual fields, see monitoring view M_DATA_VOLUME_PAGE_STATISTICS.

Related Information

SAP HANA SQL and System Views Reference Diagnosis Files [page 348]

Diagnosis files include log and trace files, as well as a mixture of other diagnosis, error, and information files. In the event of problems with the SAP HANA database, you can check these diagnosis files for errors.

8.9.1 About Persistent Data Storage in the SAP HANA Database

To ensure that the database can always be restored to its most recent committed state, changes to data in the database is periodically copied to disk. Logs containing data changes and certain transaction events are also saved regularly to disk. The data and logs of a system are stored in volumes.

During the savepoint operation, the SAP HANA database flushes all changed data from memory to the data volumes. The data belonging to a savepoint represents a consistent state of the data on disk and remains so until the next savepoint operation has completed. Redo log entries are written to the log volumes for all changes to persistent data. In the event of a database restart (for example, after a crash), the data from the last completed savepoint can be read from the data volumes, and the redo log entries written to the log volumes since the last savepoint can be replayed.

You must always ensure that there is enough space on the disk to save data and logs. Otherwise, a disk-full event will occur and your database will stop working.

134 P U B L I C

SAP HANA Administration Guide Monitoring SAP HANA Systems

In document SAP_HANA_Administration_Guide_en[1].pdf (Page 130-135)