Usage Scenarios and Best Practices

Perhaps you are asking yourself what is the best strategy for analyzing the SQL Monitor data. In general, you might be guided by the following criteria:

Different views on results:
  • System-oriented view - analysis of the entire system (for example: Which processes appear as especially expensive?)
  • Request-oriented view - analysis focuses on single request entry points (for example: What does the SQL profile for a transaction XYZ look like?)
Selection of suitable performance indicators:
  • Total number of DB executions of an SQL statement
  • Total (DB) execution time for the SQL statement
  • Total number of database records retrieved or modified by the SQL statement

The following list gives an overview of typical usage scenarios and best practices for analyzing SQL Monitor data:

Scenario 1: Get an Overview of Main SQL-driven Requests

Context

Steps recommended:

Procedure

  1. In the selection screen of the SQL Monitor, specify the options for data analysis as follows:
    1. Choose Start of the navigation pathAggregation Next navigation step By RequestEnd of the navigation path.
    2. Choose one of the Order by options to rank the result list to be generated by
    • Total Number of Executions to detect the requests that run most of the SQLs.
    • Total DB Execution Time to identify the requests with the highest SQL execution time.
    • Total DB Records to detect the requests that write or read the most data in the system.
    Selection Screen for Scenario 1
  2. On the first level of your result list, check the top 10 of the rankings and adhere the aggregated value of the performance indicator and the request you are interested in.
    First Level of the Result List
  3. To display the SQL profile of the request, drill down (DB statements link) to the second level of the result list and rank again by the performance indicator of your interest.
  4. Analyze the top entries that contribute most to the total value you found on process level.
    Second Level of the Result List

Scenario 2 a: Identify the Most Expensive or Most Frequently Executed Database Accesses in

Context

Steps recommended:

Procedure

  1. In the selection screen of the SQL Monitor, specify the options for data analysis as follows:
    1. Choose Start of the navigation pathAggregation Next navigation step By Source Code PositionEnd of the navigation path.
    2. Sort the results by performance indicators
    • Total Number of Executions to find the most frequently executed DB accesses or
    • Total DB Execution Time to find the most expensive database accesses.
    • Total DB Execution Timeto detect the processes that write or read the most data in the system.
  2. On the first level of your result list, check the top entries for the performance indicators selected. Check the Minimum/Maximum/Standard Deviation/ Execution Time values of these top entries to figure out, for example, if almost all executions are slow, or if only one SQL statement is extremely slow (because it was hanging at a lock).
  3. To check which processes are driving these top SQL entries, you can drill down to the second level of the result list using the Request Entry Points link.
    Drilling Down by Clicking the Request Entry Point Link
  4. On the second level of the result list, you can focus on the top execution numbers that have a high average execution counter per session (column Executions/Internal Session). High execution/session values often indicate a nested SELECT.

Scenario 2 b: Detect the SQL statements reading or writing most of the database data in the system

Context

Steps recommended:

Procedure

  1. In the selection screen of the SQL Monitor, specify the options for data analysis as follows:
    1. Choose Start of the navigation pathAggregation Next navigation step By Source Code PositionEnd of the navigation path.
    2. Remove the limit value from Maximal Number of Records. In this way, you get a complete list of SQL statements that read/write database data in the system.
    3. Sort the results by Total DB Records.
  2. On the first level of your result list, check the Minimum/Maximum values of the top entries.

    If maximum and minimum always have the same value (and this value equals the total number of lines stored in the DB table accessed), this might indicate a missing WHERE clause in the SQL statement.

    If, on the other hand, the maximum and minimum values differ immensely, this might indicate that the internal table in the clause FOR ALL ENTRIES within the SELECT statement is empty, or the RANGE table is empty from time to time.

Scenario 3: Analyze individual requests for optimization

Context

Steps recommended:

Procedure

  1. In the selection screen of the SQL Monitor, specify the options for data analysis as follows:
    1. Choose Start of the navigation pathAggregation Next navigation step By RequestEnd of the navigation path.
    2. Remove the limit value from Maximal Number of Records. In this way, you get a complete list of requests that run SQL statements in the system.
    3. Sort the results by Total DB Records.
  2. On the first level of your result list, identify the requests with the highest total DB execution time. A high ratio Total DB time/Total time allows you to find requests that have potential for optimization.