The SQL performance analysis helps you investigate which SQL statements from the application are particularly cost-intensive for the database, and why this might be the case.
To collect information about the execution of SQL statements in the database, SQL monitoring needs to be enabled.
As of database version 7.9, SQL monitoring is enabled by default.
SQL monitoring measures runtimes, I/O accesses, and the selectivity of SQL statements, and calculates the total costs for SQL statements that are executed repeatedly. This helps you identify those SQL statements that have a short runtime, but who are executed frequently and thus cause a heavy load in the database.
You are logged on to the database as the database system administrator or as a database user.
Note that to activate or deactivate extended time measurement, you have to be logged on as database system administrator.
Note that to reset SQL monitoring, you need to be logged on as the database system administrator or as a database user of the DBA class.
The database is in the ONLINE operational state.
In the context menu of the database system administrator or the logged-on database user, choose
.Resetting SQL Monitoring
In certain situations, for example after you have made changes to your SQL statements, it can be useful to exclude older executions of SQL statements from the default result set. To do this, choose Reset Monitoring Data.
Note that this does not delete the data that was collected about older executions of these SQL statements, and you can later toggle between both result sets.
Activating or Deactivating Extended Time Measurement
Activating extended time measurement may have a negative impact on the performance of your database.
For a thorough performance analysis, we recommend that you activate extended time measurement. If extended time measurement is activated, the system records collects additional information about SQL statements.
In the Extended Time Measurement section, choose Activate or Deactivate (depending on whether the function is currently deactivated or activated).
Configuring and Starting the Detail Collection
Activating the detail collection may have a negative impact on the performance of your database.
If the detail collection is deactivated (default setting), the system only records summarized values for the execution of SQL statements.
If the detail collection is activated, the system additionally records the following:
Information about the last executions of those SQL statements that meet at least one of these conditions
Parameters used in those SQL statements
Defining Conditions
Define your conditions in the Detail Collection section.
Note that the system only records a certain number of executions and then begins overwriting the oldest entries.
Condition |
Description |
---|---|
Simple |
|
Number of page accesses |
The system records all SQL statements that access the number of pages specified here, or more pages. If an SQL statement accesses a high number of pages, this could indicate an unsuitable search strategy or an overly large result set. |
Runtime of the statement in ms |
The system records all SQL statements with a runtime that is equal or larger than the time specified here. |
Selectivity of the statement |
The system records all SQL statements with a selectivity that is equal or lower than the percentage specified here. The selectivity of an SQL statement is the ratio between the number of records that fulfill the conditions of the SQL statement and the number of records that the system has to access when executing the SQL statement. |
Advanced |
|
Constraint |
For support purposes only Enter your constraint using the same syntax as for SQL constraints. |
Starting the Detail Collection
Choose Apply.
Changing Detail Collection Settings
In the Detail Collection section, change your settings.
Choose whether you want to delete the data that was collected with the old settings:
Reset Monitoring: Data that was collected by the SQL monitoring will be deleted
Clear Detail Collection Data: Data that was collected by the detail collection will be deleted
Choose Apply.
Stopping the Detail Collection
In the Detail Collection section, choose Stop Detail Collection.
At the bottom of the page, enter the number of SQL statements that you want to display and choose Get Bottleneck Candidates. To display the SQL statements that were recorded by the system, proceed as described under Displaying Bottleneck Candidates.
SAP Note 819641 (FAQ: MaxDB Performance)
Database Administration, Analyzing the Database Performance