Thresholds for exceptions and efficient statements
For static workloads, efficient statement thresholds can effectively reduce the thread cost of monitoring with Apptune. These thresholds include an execution count (1000 by default) with low values for CPU usage, getpage count, and synchronous I/O count. Apptune monitors the statements as follows:
At the start of each Apptune interval, Apptune fully monitors each static statement for a specified number of executions (by default, 1,000).
Apptune performs the following actions:
- Watches the statement on each subsequent execution (for negative SQL codes)
- Monitors (samples) the statement
- Counts non-monitored (filtered) executions
- If any execution exceeds any of the three thresholds, Apptune considers the statement to be inefficient. If no threshold is exceeded, Apptune considers the statement to be efficient.
- Apptune extrapolates reported activity based on monitoring results and filtered execution counts.
- If low thresholds are subsequently exceeded on any sampled execution, Apptune resumes full monitoring and considers the statement to be inefficient until the next interval .
At the plan or program level, you can see how many executions the product monitored and filtered by using the APPSTAT command with the ENTRIES parameter.
Dynamic statement executions are not eligible for consideration as efficient.
Efficiency filtering allows you to identify, collect and extrapolate SQL statements that are unlikely to hit any exception criteria.
For static workloads, efficient statement thresholds can effectively reduce the thread cost of monitoring with Apptune. These thresholds include an execution count (1000 by default) along with low values for CPU usage, getpage count, and synchronous I/O count.
| For more information, view the Quick Course Creating and Monitoring Exceptions. |
(BMC.DB2.SPE2501)(BMC.DB2.SPE2404)You can view and modify the thresholds for exceptions or efficient statements by expanding the Thresholds for Exceptions and Efficient Statements section on the filter panel (as shown in the following figure):
-------------------------------------------------------------------------------
VMFILTER - Test filter
Command ===> Scroll ===> CSR
Filter: Off More:
- Program Plan User ID Corr ID ConnType Client Label Db2
- prog!ab! PLA!33* !!!MDB* CORR!XYZ* TSO LABEL* DNY5
Application name . . . . . . . . . . . . .
Client application . . . . . . . . . . . >
Client user ID . . . . . . . . . . . . . > RAJA*
Client workstation . . . . . . . . . . . >
Client correlation . . . . . . . . . . . >
Client accounting. . . . . . . . . . . . >
Collect advanced metrics (Y,N,S=Static,D=Dynamic). . . . Y
If not collected for statement, skip all for thread . . . N
Standard mode: is this row eligible for advanced metrics?: N
Standard Mode: collect SQL errors as specified below . . . Y
Standard Mode: collect exceptions as specified below . . . Y
+ Collection Options (Y=Yes, N=No)
+ Collection Keys (Y=Yes, N=No)
+ Resource-Saving Options (Y=Yes, N=No, R=Replace)
- Thresholds for Exceptions and Efficient Statements
Exception thresholds for Warning , Critical (Range/forces collection)
Elapsed time . 0 , 0 (0 - 9999999 milliseconds)
CPU time . . . . . . . . 0 , 0 (0 - 9999999 milliseconds)
Getpage requests . . . . 0 , 0 (0 - 9999999/Buffer Pool)
Synchronous I/Os . . . . 0 , 0 (0 - 9999999/Stmt counts)
Deadlocks. . . . . . . . Y (N=No, Y=Yes: forces Lock data to Y)
Timeouts . . . . . . . . Y (N=No, Y=Yes: forces Lock data to Y)
Issue Exception WTOs . . N (N=No, Y=Yes)
Thread cancels . . . . . Y (N=No, Y=Yes)
Multiple exceptions per SQL execution. . . Y (N=No, Y=Yes)
Exceptions limit per stmt, per interval. . 0 (0=no limit)
Exceptions threshold CPU time type . . . . A (G=GP, Z=zIIP, A=All)
Efficient statements (static): detect, then sample and extrapolate
(requires Advanced Mode)
Per interval, monitor 0 executions to detect (0=monitor all)
Execution is efficient if less than 999000 microseconds CPU (0-999000)
and less than 1000 getpages (0-1000)
and less than 1000 synchronous I/Os (0-1000)
+ SQL Error Options
Show hostvars on exceptions and SQL errors . . . . Y (Y=Yes, N=No)
********************************* End of List ********************************
You can set exception thresholds for the options in the Filter exception options table and the Efficiency filtering (static) table. An exception record is created for every exception encountered.
Filter exception options
Exception value | Description |
|---|---|
Elapsed time | Specify the number of milliseconds to elapse for an SQL call before an exception record is created. Valid values are any number in the range 0–9999999. A value of 0 indicates that exception records are not created based on elapsed time. |
CPU time | Specify the amount of CPU time to elapse in milliseconds for an SQL call before an exception record is created. Valid values are any number in the range 0–9999999. A value of 0 indicates that exception records are not created based on CPU time. |
Getpages request | Specify the getpage count to reach for an SQL call before an exception record is created. Valid values are any number in the range 0–99999. A value of 0 indicates that exception records are not created based on the number of getpages. If you specify a value greater than 0, the product automatically changes the buffer pool collection option to Y. |
Synchronous I/Os | Specify the synchronous I/O count to reach for an SQL call before an exception record is created. Valid values are any number in the range 0–99999. A value of 0 indicates that exception records are not created based on the number of synchronous I/Os. If you specify a value greater than 0, the product automatically changes the SQL statement counts collection option to Y. |
Deadlocks | Specify whether to create an exception record when a deadlock occurs. Valid values are Y (Yes) and N (No). A value of N indicates that exception records are not created based on the occurrence of deadlocks. If you specify Y, the product automatically changes the lock data collection options to Y. |
Timeouts | Specify whether to create an exception record when a timeout occurs. Valid values are Y (Yes) and N (No). A value of N indicates that exception records are not created based on the occurrence of timeouts. If you specify Y, the product automatically changes the lock data collection option to Y. |
Issue exception WTOs | Specify whether to generate a console write-to-operator (WTO) message for each exception. Valid values are Y (Yes) and N (No). You can use these messages to drive console-based automation. If this value is Y, a maximum of one exception WTO message is issued per statement per interval. |
Thread Cancels | Specify whether to create an exception record when a thread cancel occurs. Valid values are Y (Yes) and N (No). A value of N indicates that exception records are not created based on the occurrence of a thread cancels. |
Multiple exceptions per SQL execution | Specify whether to create exception record for each threshold breach during one SQL execution. Valid values are Y (Yes) and N (No). If this value is N, a maximum of one exception record is created for every SQL execution. |
Exceptions limit per stmt, per interval | Specify the maximum number of exception records that can be created for a statement entry per interval. The value of 0 specifies that all the exceptions should be recorded for a statement entry during an interval. |
Exceptions threshold CPU time type | Specify the CPU type applicable on CPU time exception threshold. Valid values are A (All types of CPU), G (General CP), and Z (zIIP CPU). |
Efficiency filtering (static)
Efficiency value | Description |
|---|---|
Per interval executions | Specify the number of times to execute a static statement in an Apptune interval after which, if no exceptions have occurred, for the remainder of the interval, its executions are counted and performance data is extrapolated based on the monitored executions. |
Microseconds | If any static execution reaches this level of CPU consumption, the statement is not considered efficient and is monitored throughout the interval. |
Getpages | If any static execution issues this many getpages or more, the statement is not considered efficient and is monitored throughout the interval. |
Synchronous I/O | If any static execution issues this many sync I/Os or more, the statement is not considered efficient and is monitored throughout the interval. |
Use the Exception Analysis report in Workload Analysis to examine the exception records. Zoom in to the Exceptions—SQL Text report to see host variable values.
Exception processing is especially useful in the following circumstances:
- Your system and application software is well tuned, but you need to be aware of any rogue statements that might degrade performance or response time.
- You have SQL statements with host variable values that must be externalized to analyze and debug the associated application software programs.
Exception processing (if specified) causes production of exception records (BMC IFCID 004) and host variable records (BMC IFCID 010).