DB2 Version 9.7 for Linux, UNIX, and Windows

MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache

The MON_GET_PKG_CACHE_STMT table function returns a point-in-time view of both static and dynamic SQL statements in the database package cache.

Syntax


>>-MON_GET_PKG_CACHE_STMT--(--section_type--,------------------->

>--executable_id--,--search_args--,--member--)-----------------><

The schema is SYSPROC.

Table function parameters

section_type

An optional input argument (either "D" or "S") of type CHAR(1) that specifies information type for the returned statement. If the argument is NULL or the empty string, information is returned for all SQL statements. Not case sensitive: "D" stands for dynamic; "S" for static.

executable_id

An optional input argument of type VARCHAR (32) for bit data that specifies a unique section of the database package cache. If a null value is specified, information is returned for all SQL statements. Note that when the executable_id is specified, the section_type argument is ignored. For example, if an executable_id is specified for a dynamic statement, the dynamic statement details will be returned by this table function even if section_type is specified as static ("S").

search_args

An optional input parameter of type CLOB(1K), that allows you to specify one or more optional search argument strings. For example:

'<modified_within>5</modified_within><update_boundary_time>myPkgEvmon
    </update_boundary_time>'

The available search argument tags are as follows:

'<modified_within>X</modified_within>'
Returns only those statement entries that have either been inserted into the cache or executed within the last X minutes (where X is a positive integer value). If the argument is not specified, all entries in the cache are returned.
'<update_boundary_time>evmon_name</update_boundary_time>'
Updates the event monitor boundary timestamp to the current time for the package cache event monitor specified by evmon_name. If this event monitor specifies where updated_since_boundary_time as an output criteria in its WHERE clause, only package cache entries that subsequently have their metrics updated are captured when evicted from the package cache. This operation only has an effect if the specified package cache event monitor is active when the command is issued.

Each input argument can be specified only once. The search argument tags must be specified in lowercase.

member

An optional input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all database members. If the null value is specified, -1 is set.

Authorization

EXECUTE privilege on the MON_GET_PKG_CACHE_STMT function.

Example

List all the dynamic SQL statements from the database package cache ordered by the average CPU time.

db2 SELECT MEMBER,
      SECTION_TYPE , 
      TOTAL_CPU_TIME/NUM_EXEC_WITH_METRICS as  
      AVG_CPU_TIME,EXECUTABLE_ID
      FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) as T 
        WHERE T.NUM_EXEC_WITH_METRICS <> 0 ORDER BY AVG_CPU_TIME

The following example is a sample output from this query.

MEMBER SECTION_TYPE AVG_CPU_TIME         EXECUTABLE_ID                                                      
------ ------------ -------------------- -------------------------------------------------------------------
     0 D                             754 x'01000000000000007A0000000000000000000000020020081126171554951791'
     0 D                            2964 x'0100000000000000790000000000000000000000020020081126171533551120'
     0 D                            5664 x'01000000000000007C0000000000000000000000020020081126171720728997'
     0 D                            5723 x'01000000000000007B0000000000000000000000020020081126171657272914'
     0 D                            9762 x'01000000000000007D0000000000000000000000020020081126172409987719'

5 record(s) selected.

Note: It takes a longer time period to build the compilation environment and to transfer statement text (which can be as large as 2 MB) between members. To improve performance when retrieving a list of all the statements from the package cache, do not to select the STMT_TEXT and the COMP_ENV_DESC columns.

With the above output, we can use the executable_id to find out the details about the most expensive statement (in terms of the average CPU time):

db2 SELECT STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT
      (null, x'01000000000000007D0000000000000000000000020020081126172409987719', null, -2))

STMT_TEXT
-------------------------------------------------------------------------------------------
SELECT * FROM EMPLOYEE

As another example, assume a user named Alex has a connection associated to workload A which has the COLLECT ACTIVITY METRICS set. Another user, Brent, is associated to workload B that has the COLLECT ACTIVITY METRICS set to NONE. In addition, the database mon_act_metrics configuration parameter is set to NONE. When Brent executes the query:

SELECT count(*) FROM syscat.tables

all metrics are returned as 0 and the value of num_exec_with_metrics is also 0. Then Alex executes the same statement afterwards, but the metrics are collected this time for the execution of the statement and num_exec_with_metrics increments. So, after Brent and Alex execute that statement, the result of this query:

SELECT num_executions, num_exec_with_metrics, SUBSTR(stmt_text,1,50) AS stmt_text 
    FROM TABLE (MON_GET_PKG_CACHE_STMT('d', null, null, -1)) AS tf 
    WHERE stmt_text LIKE 'SELECT count%'

shows that the SELECT statement ran twice and one of the execution times had the activity metrics collected.

NUM_EXECUTIONS NUM_EXEC_WITH_METRICS STMT_TEXT           
-------------- --------------------- --------------------
             2                     1 SELECT count(*) FROM syscat.tables

  1 record(s) selected.

Usage notes

The MON_GET_PKG_CACHE_STMT table function returns a point-in-time view of both static and dynamic SQL statements in the database package cache. This allows you to examine the aggregated metrics for a particular SQL statement, allowing you to quickly determine the reasons for poor query performance. The metrics returned are aggregates of the metrics gathered during each execution of the statement.

It also allows you to compare the behavior of an individual cached section, relative to the other statements, to assist in identifying the most expensive section or statements (in terms of the execution costs).

The activity metrics reported by this function are rolled up to the global package cache at the end of the execution of the activity.

Metrics collection for the execution of any statement is controlled through the COLLECT ACTIVITY METRICS clause on workloads, or the mon_act_metrics database configuration parameter at the database level. Metrics are only collected for executions of the statement if the statement was submitted by a connection associated with a workload or database for which activity metrics are enabled. The num_exec_with_metrics element returned by the MON_GET_PKG_CACHE_STMT function indicates how many executions of the statement have had metrics collected and have contributed to the aggregate metrics reported. If no metrics are collected for any execution of the statement, then the num_exec_with_metrics element is 0 and all metric values are returned as 0.

The output that is returned by MON_GET_PKG_CACHE_STMT is not directly comparable to the output obtained through the dynamic SQL snapshot. The following are differences between the two interfaces:

MON_GET_PKG_CACHE_STMT reports data per individual section in the cache, whereas the dynamic SQL snapshot aggregates all sections for the statement. Therefore, MON_GET_PKG_CACHE provides more granularity. It distinguishes statements that have separate entries in the cache because of a combination of environment differences.
MON_GET_PKG_CACHE_STMT captures the metrics during query plan execution. The dynamic SQL snapshot might include some of the work that is done when the section is loaded, depending on how the statement is run from the application side.

Information returned

Table 1. Information returned for MON_GET_PKG_CACHE_STMT
Column Name	Data Type	Description or corresponding monitor element
MEMBER	SMALLINT	member- Database member
SECTION_TYPE	CHAR(1)	section_type - Section type indicator.
INSERT_TIMESTAMP	TIMESTAMP	insert_timestamp - Statement insert timestamp
EXECUTABLE_ID	VARCHAR(32) FOR BIT DATA	executable_id - Executable ID.
PACKAGE_SCHEMA	VARCHAR(128)	package_schema - Package schema . This output is valid for static SQL statements only. A NULL value is returned if the statement is dynamic.
PACKAGE_NAME	VARCHAR(128)	package_name - Package name . This output is valid for static SQL statements only. A NULL value is returned if the statement is dynamic.
PACKAGE_VERSION_ID	VARCHAR(64)	package_version_id - Package version. This output is valid for static SQL statements only. A NULL value is returned if the statement is dynamic or if you did not specify the package version for static statement. An empty string will be returned for static statement if the package version identifier was not specified by you when the package was created.
SECTION_NUMBER	BIGINT	section_number - Section number. A NULL value is returned if the statement is dynamic.
EFFECTIVE_ISOLATION	CHAR(2)	effective_isolation - Effective isolation. This is the isolation value in effect for the section; it can be different from what it was originally requested at compilation time.
NUM_EXECUTIONS	BIGINT	num_executions - Statement executions
NUM_EXEC_WITH_METRICS	BIGINT	num_exec_with_metrics - Number of executions with metrics collected.
PREP_TIME	BIGINT	prep_time - Preparation time Note that PREP_TIME is only valid for dynamic SQL statements. PREP_TIME is reported as 0 for static SQL statements.
TOTAL_ACT_TIME	BIGINT	total_act_time - Total activity time
TOTAL_ACT_WAIT_TIME	BIGINT	total_act_wait_time - Total activity wait time
TOTAL_CPU_TIME	BIGINT	total_cpu_time - Total CPU time
POOL_READ_TIME	BIGINT	pool_read_time - Total buffer pool physical read time
POOL_WRITE_TIME	BIGINT	pool_write_time - Total buffer pool physical write time
DIRECT_READ_TIME	BIGINT	direct_read_time - Direct Read Time
DIRECT_WRITE_TIME	BIGINT	direct_write_time - Direct write time
LOCK_WAIT_TIME	BIGINT	lock_wait_time - Time waited on locks
TOTAL_SECTION_SORT_TIME	BIGINT	total_section_sort_time - Total section sort time
TOTAL_SECTION_SORT_PROC_TIME	BIGINT	total_section_sort_proc_time - Total section sort processing time
TOTAL_SECTION_SORTS	BIGINT	total_section_sorts - Total section sorts
LOCK_ESCALS	BIGINT	lock_escals - Number of lock escalations
LOCK_WAITS	BIGINT	lock_waits - Lock waits
ROWS_MODIFIED	BIGINT	rows_modified - Rows modified
ROWS_READ	BIGINT	rows_read - Rows read
ROWS_RETURNED	BIGINT	rows_returned - Rows returned
DIRECT_READS	BIGINT	direct_reads - Direct reads from database
DIRECT_READ_REQS	BIGINT	direct_read_reqs - Direct read requests
DIRECT_WRITES	BIGINT	direct_writes - Direct writes to database
DIRECT_WRITE_REQS	BIGINT	direct_write_reqs - Direct write requests
POOL_DATA_L_READS	BIGINT	pool_data_l_reads - Buffer pool data logical reads
POOL_TEMP_DATA_L_READS	BIGINT	pool_temp_data_l_reads - Buffer pool temporary data logical reads
POOL_XDA_L_READS	BIGINT	pool_xda_l_reads - Buffer Pool XDA Data Logical Reads
POOL_TEMP_XDA_L_READS	BIGINT	pool_temp_xda_l_reads - Buffer pool temporary XDA data logical reads
POOL_INDEX_L_READS	BIGINT	pool_index_l_reads - Buffer pool index logical reads
POOL_TEMP_INDEX_L_READS	BIGINT	pool_temp_index_l_reads - Buffer pool temporary index logical reads
POOL_DATA_P_READS	BIGINT	pool_data_p_reads - Buffer pool data physical reads
POOL_TEMP_DATA_P_READS	BIGINT	pool_temp_data_p_reads - Buffer pool temporary data physical reads
POOL_XDA_P_READS	BIGINT	pool_xda_p_reads - Buffer pool XDA data physical reads
POOL_TEMP_XDA_P_READS	BIGINT	pool_temp_xda_p_reads - Buffer pool temporary XDA data physical reads
POOL_INDEX_P_READS	BIGINT	pool_index_p_reads - Buffer pool index physical reads
POOL_TEMP_INDEX_P_READS	BIGINT	pool_temp_index_p_reads - Buffer pool temporary index physical reads
POOL_DATA_WRITES	BIGINT	pool_data_writes - Buffer pool data writes
POOL_XDA_WRITES	BIGINT	pool_xda_writes - Buffer pool XDA data writes
POOL_INDEX_WRITES	BIGINT	pool_index_writes - Buffer pool index writes
TOTAL_SORTS	BIGINT	total_sorts - Total Sorts
POST_THRESHOLD_SORTS	BIGINT	post_threshold_sorts - Post threshold sorts
POST_SHRTHRESHOLD_SORTS	BIGINT	post_shrthreshold_sorts - Post shared threshold sorts
SORT_OVERFLOWS	BIGINT	sort_overflows - Sort overflows
WLM_QUEUE_TIME_TOTAL	BIGINT	wlm_queue_time_total - Workload manager total queue time
WLM_QUEUE_ASSIGNMENTS_TOTAL	BIGINT	wlm_queue_assignments_total - Workload manager total queue assignments
DEADLOCKS	BIGINT	deadlocks - Deadlocks detected
FCM_RECV_VOLUME	BIGINT	fcm_recv_volume - FCM recv volume
FCM_RECVS_TOTAL	BIGINT	fcm_recvs_total - FCM recvs total
FCM_SEND_VOLUME	BIGINT	fcm_send_volume - FCM send volume
FCM_SENDS_TOTAL	BIGINT	fcm_sends_total - FCM sends total
FCM_RECV_WAIT_TIME	BIGINT	fcm_recv_wait_time - FCM recv wait time
FCM_SEND_WAIT_TIME	BIGINT	fcm_send_wait_time - FCM send wait time
LOCK_TIMEOUTS	BIGINT	lock_timeouts - Number of lock timeouts
LOG_BUFFER_WAIT_TIME	BIGINT	log_buffer_wait_time - Log buffer wait time
NUM_LOG_BUFFER_FULL	BIGINT	num_log_buffer_full - Number of full log buffers
LOG_DISK_WAIT_TIME	BIGINT	log_disk_wait_time - Log disk wait time
LOG_DISK_WAITS_TOTAL	BIGINT	log_disk_waits_total - Log disk waits total
LAST_METRICS_UPDATE	TIMESTAMP	last_metrics_update - Metrics last update timestamp
NUM_COORD_EXEC	BIGINT	num_coord_exec - Number of executions by coordinator agent
NUM_COORD_EXEC_WITH_METRICS	BIGINT	num_coord_exec_with_metrics - Number of executions by coordinator agent
VALID	CHAR(1)	valid - Section validity indicator.
TOTAL_ROUTINE_TIME	BIGINT	total_routine_time - Total routine time
TOTAL_ROUTINE_INVOCATIONS	BIGINT	total_routine_invocations - Total routine invocations
ROUTINE_ID	BIGINT	routine_id - Routine ID monitor element
STMT_TYPE_ID	VARCHAR(32)	stmt_type_id - Statement type identifier
QUERY_COST_ESTIMATE	BIGINT	query_cost_estimate - Query cost estimate
STMT_PKG_CACHE_ID	BIGINT	stmt_pkgcache_id - Statement package cache identifier
COORD_STMT_EXEC_TIME	BIGINT	coord_stmt_exec_time - Execution time for statement by coordinator agent
STMT_EXEC_TIME	BIGINT	stmt_exec_time - Statement execution time
TOTAL_SECTION_TIME	BIGINT	total_section_time - Total section time
TOTAL_SECTION_PROC_TIME	BIGINT	total_section_proc_time - Total section processing time
TOTAL_ROUTINE_NON_SECT_TIME	BIGINT	total_routine_non_sect_time - Non-section routine execution time
TOTAL_ROUTINE_NON_SECT_PROC_TIME	BIGINT	total_routine_non_sect_proc_time - Non-section processing time
IDA_SEND_WAIT_TIME	BIGINT	ida_send_wait_time - Time spent waiting to send data
IDA_SENDS_TOTAL	BIGINT	ida_sends_total - Number of times data sent
IDA_SEND_VOLUME	BIGINT	ida_send_volume - Total data volume sent
IDA_RECV_WAIT_TIME	BIGINT	ida_recv_wait_time - Time spent waiting to receive data
IDA_RECVS_TOTAL	BIGINT	ida_recvs_total - Number of times data received
IDA_RECV_VOLUME	BIGINT	ida_recv_volume - Total data volume received
STMT_TEXT	CLOB(2MB)	stmt_text - SQL statement text
COMP_ENV_DESC	BLOB(10K)	comp_env_desc - Compilation environment handle. You can use the existing COMPILATION_ENV table function to get the detailed compilation environment of the specific statement if needed.
ADDITIONAL_DETAILS	BLOB(100K)	Reserved for future additional metrics.