2825150 - New function for monitoring statement performance by set "Collect_IQ_Performance_Stats" and "QUERY_PLAN_MIN_TIME" -- SAP IQ

This new monitoring fuction was introduced from IQ16.0 SP11.12.

• Purpose of the statement performance monitoring 

The statement performance monitoring feature is not an exhaustive, complete audit of slow SQL statements (queries), but it is a useful tool for providing an approximation, or high-level summary, of query workload. Statement performance monitoring flags certain outlier statements with execution times exceeding an established baseline.

  But these use cases are extremely misleading use cases since this feature tries to find outlier statements rather than plain slow statements. This feature is not for   auditing queries which executes in > QUERY_PLAN_MIN_TIME and it tries to find outlier statements rather than plain slow statements.

• This feature typically

- Creates a baseline for all the statements ( internal as well as user )
- Creates hashes for these statements and their plans
- As same statements execute with different parameters, if the execution time changes drastically, such statement becomes outlier and statement of interest.
   (* Just because a statement takes more time than QUERY_PLAN_MIN_TIME , does not mean that it is a statement of interest.)
- Saves statement and plan info in buffer. Performance manager code operates on it for deciding what to keep and what to purge.
- Frequently this buffer gets flushed to the disk. "

• Side effects

- You may not see some statement even though its execution time is > QUERY_PLAN_MIN_TIME
- Statements will not have constants
- There may not be a plan in GTSYSPERFCACHEPLAN for outlier statement in GTSYSPERFCACHESTMT
- You may not see a statement in these 2 views till performance buffer gets purged to the disk.
- You may see internal non-user statements as well

• This function is looking for "outlier" queries that exceed what it considers to be a standard deviation, not all those that exceed the MIN_TIME threshold.
  And there are some other internal considerations that may prevent some queries from being considered up-front.

  There are several possible points of why “Not all queries with execution times exceeding the QUERY_PLAN_MIN_TIME threshold are reported.”

  1. Query text/plan may not be recorded for a query even if its runtime is included in the accumulated data; the query text/plan may be collected during a subsequent execution with the similar text and plan.
  2. On a system with a high workload, some entries (any of query information, query text, query plan) may not get recorded due to space limitations on the feature.
  3. An entry may take some time to get processed, so may not be visible for some time after the query was executed.

  In general, the feature is a best-effort way of providing summary information about the workload, more specifically frequently executed queries.

• SAP IQ16.0 SP11.12
• SAP IQ16.1 All

COLLECT_IQ_PERFORMANCE_STAT, QUERY_PLAN_MIN_TIME, GTSYSPERFCACHESTMT, GTSYSPERFCACHEPLAN , sp_top_k_statements, sp_find_top_statements, , KBA , BC-SYB-IQ , Sybase IQ , How To