SQL execution plan

A query execution plan is the optimized set of steps used to access data in a SQL relational database management system. It is retrieved from the database cache on demand and is not stored in AppMon.

By examining it in AppMon, find answers if, why and where a query is slow and indices are used or not ‐ or, setting timings in relation, a different component is the culprit.

You can access execution plans from the context menu Show SQL Execution Plan of a SQL statement on the Database dashlet or a PurePath.

SQL execution plan limitations

  • Stored procedures and functions are supported only for MSSQL. For other databases, statements that start with call, exec, or {call should have the Show SQL Execution Plan option disabled.
  • If the DB cache has been cleared, prior execution plans are not available.
  • Not supported for the following Oracle Database settings:
    • cursor_sharing=force
    • cursor_sharing=similar
  • In order to request an execution plan the user must have the following permissions:
    • The DB permission to view SQL statements
    • The AppMon permission Show Database Monitoring.
  • Using the jTDS driver for MSSQL enabling query preprocessing (JtdsStatement.setEscapeProcessing() method) may cause problems with looking up query execution plans as the query text gets modified.
  • In the Database dashlet the presented SQLs have their whitespace normalized. Disable whitespace normalization from the SQL query's context menu (see screenshot below)
  • To optimize performance, DB-related sensors such as JDBC, ADO.NET, and PHP store only the first 512 characters of a query by defaut. Truncated queries do not match explain plans. You can modify this value in the JDBC and ADO.NET sensor configurations, in the agent section of a system profile. (see screenshot below)

Denormalize sql
JDBC Sensor properties