MON_GET_PKG_CACHE_STMT table function - Get package cache statement metrics

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

Read syntax diagramSkip visual syntax diagramMON_GET_PKG_CACHE_STMT(section_type ,executable_id,search_args ,member)

The schema is SYSPROC.

Authorization

One of the following authorities is required to execute the routine:
  • EXECUTE privilege on the routine
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority

Default PUBLIC privilege

None

Routine 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. 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 active database members. If the null value is specified, -1 is set.

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
LOCK_WAITS_GLOBAL BIGINT lock_waits_global - Lock waits global
LOCK_WAIT_TIME_GLOBAL BIGINT lock_wait_time_global - Lock wait time global
LOCK_TIMEOUTS_GLOBAL BIGINT lock_timeouts_global - Lock timeouts global
LOCK_ESCALS_MAXLOCKS BIGINT lock_escals_maxlocks - Number of maxlocks lock escalations
LOCK_ESCALS_LOCKLIST BIGINT lock_escals_locklist - Number of locklist lock escalations
LOCK_ESCALS_GLOBAL BIGINT lock_escals_global - Number of global lock escalations
RECLAIM_WAIT_TIME BIGINT reclaim_wait_time - Reclaim wait time
SPACEMAPPAGE_RECLAIM_WAIT_TIME BIGINT spacemappage_reclaim_wait_time - Space map page reclaim wait time
CF_WAITS BIGINT cf_waits - Number of cluster caching facility waits
CF_WAIT_TIME BIGINT cf_wait_time - cluster caching facility wait time
POOL_DATA_GBP_L_READS BIGINT pool_data_gbp_l_reads - Group buffer pool data logical reads
POOL_DATA_GBP_P_READS BIGINT pool_data_gbp_p_reads - Group buffer pool data physical reads
POOL_DATA_LBP_PAGES_FOUND BIGINT pool_data_lbp_pages_found - Local buffer pool found data pages
POOL_DATA_GBP_INVALID_PAGES BIGINT pool_data_gbp_invalid_pages - Group buffer pool invalid data pages
POOL_INDEX_GBP_L_READS BIGINT pool_index_gbp_l_reads - Group buffer pool index logical reads
POOL_INDEX_GBP_P_READS BIGINT pool_index_gbp_p_reads - Group buffer pool index physical reads
POOL_INDEX_LBP_PAGES_FOUND BIGINT pool_index_lbp_pages_found - Local buffer pool index pages found
POOL_INDEX_GBP_INVALID_PAGES BIGINT pool_index_gbp_invalid_pages - Group buffer pool invalid index pages
POOL_XDA_GBP_L_READS BIGINT pool_xda_gbp_l_reads - Group buffer pool XDA data logical read requests
POOL_XDA_GBP_P_READS BIGINT pool_xda_gbp_p_reads - Group buffer pool XDA data physical read requests
POOL_XDA_LBP_PAGES_FOUND BIGINT pool_xda_lbp_pages_found - Local buffer pool XDA data pages found
POOL_XDA_GBP_INVALID_PAGES BIGINT pool_xda_gbp_invalid_pages - Group buffer pool invalid XDA data pages
AUDIT_EVENTS_TOTAL BIGINT audit_events_total - Total audit events
AUDIT_FILE_WRITES_TOTAL BIGINT audit_file_writes_total - Total Audit files written
AUDIT_FILE_WRITE_WAIT_TIME BIGINT audit_file_write_wait_time - Audit file write wait time
AUDIT_SUBSYSTEM_WAITS_TOTAL BIGINT audit_subsystem_waits_total - Total audit subsystem waits
AUDIT_SUBSYSTEM_WAIT_TIME BIGINT audit_subsystem_wait_time - Audit subsystem wait time
DIAGLOG_WRITES_TOTAL BIGINT diaglog_writes_total - Diag log total writes
DIAGLOG_WRITE_WAIT_TIME BIGINT diaglog_write_wait_time - Diag log write time
FCM_MESSAGE_RECVS_TOTAL BIGINT fcm_message_recvs_total - FCM message recvs total
FCM_MESSAGE_RECV_VOLUME BIGINT fcm_message_recv_volume - FCM message recv volume
FCM_MESSAGE_RECV_WAIT_TIME BIGINT fcm_message_recv_wait_time - FCM message recv wait time
FCM_MESSAGE_SENDS_TOTAL BIGINT fcm_message_sends_total - FCM message sends total
FCM_MESSAGE_SEND_VOLUME BIGINT fcm_message_send_volume - FCM message send volume
FCM_MESSAGE_SEND_WAIT_TIME BIGINT fcm_message_send_wait_time - FCM message send wait time
FCM_TQ_RECVS_TOTAL BIGINT fcm_tq_recvs_total - FCM tablequeue recvs total
FCM_TQ_RECV_VOLUME BIGINT fcm_tq_recv_volume - FCM tablequeue recv volume
FCM_TQ_RECV_WAIT_TIME BIGINT fcm_tq_recv_wait_time - FCM tablequeue recv wait time
FCM_TQ_SENDS_TOTAL BIGINT fcm_tq_sends_total - FCM tablequeue send total
FCM_TQ_SEND_VOLUME BIGINT fcm_tq_send_volume - FCM tablequeue send volume
FCM_TQ_SEND_WAIT_TIME BIGINT fcm_tq_send_wait_time - FCM tablequeue send wait time
NUM_LW_THRESH_EXCEEDED BIGINT num_lw_thresh_exceeded - Number of thresholds exceeded
THRESH_VIOLATIONS BIGINT thresh_violations - Number of threshold violations
TOTAL_APP_SECTION_EXECUTIONS BIGINT total_app_section_executions - Total section executions
TOTAL_ROUTINE_USER_CODE_PROC_TIME BIGINT total_routine_user_code_proc_time - Total routine user code processing time
TOTAL_ROUTINE_USER_CODE_TIME BIGINT total_routine_user_code_time - Total routine user code time
TQ_TOT_SEND_SPILLS BIGINT tq_tot_send_spills - Total number of table queue buffers overflowed
EVMON_WAIT_TIME BIGINT evmon_wait_time - Event monitor wait time
EVMON_WAITS_TOTAL BIGINT evmon_waits_total - Event monitor total waits
TOTAL_EXTENDED_LATCH_WAIT_TIME BIGINT total_extended_latch_wait_time - Total extended latch wait time
TOTAL_EXTENDED_LATCH_WAITS BIGINT total_extended_latch_waits - Total extended latch waits
MAX_COORD_STMT_EXEC_TIME BIGINT max_coord_stmt_exec_time - Maximum coordinator statement execution time
MAX_COORD_STMT_EXEC_TIMESTAMP TIMESTAMP max_coord_stmt_exec_timestamp - Maximum coordinator statement execution timestamp monitor element
TOTAL_DISP_RUN_QUEUE_TIME BIGINT total_disp_run_queue_time - Total dispatcher run queue time
QUERY_DATA_TAG_LIST VARCHAR(32) query_data_tag_list - Query data tag list
TOTAL_STATS_FABRICATION_TIME BIGINT total_stats_fabrication_time - Total statistics fabrication time
TOTAL_STATS_FABRICATIONS BIGINT total_stats_fabrications - Total statistics fabrications
TOTAL_SYNC_RUNSTATS_TIME BIGINT total_sync_runstats_time - Total synchronous RUNSTATS time
TOTAL_SYNC_RUNSTATS BIGINT total_sync_runstats - Total synchronous RUNSTATS activities
TOTAL_PEDS BIGINT total_peds - Total partial early distincts
DISABLED_PEDS BIGINT disabled_peds - Disabled partial early distincts
POST_THRESHOLD_PEDS BIGINT post_threshold_peds - Partial early distincts threshold
TOTAL_PEAS BIGINT total_peas - Total partial early aggregations
POST_THRESHOLD_PEAS BIGINT post_threshold_peas - Partial early aggregation threshold
TQ_SORT_HEAP_REQUESTS BIGINT tq_sort_heap_requests - Table queue sort heap requests
TQ_SORT_HEAP_REJECTIONS BIGINT tq_sort_heap_rejections - Table queue sort heap rejections
POOL_QUEUED_ASYNC_DATA_REQS BIGINT pool_queued_async_data_reqs - Data prefetch requests monitor element
POOL_QUEUED_ASYNC_INDEX_REQS BIGINT pool_queued_async_index_reqs - Index prefetch requests monitor element
POOL_QUEUED_ASYNC_XDA_REQS BIGINT pool_queued_async_xda_reqs - XDA prefetch requests monitor element
POOL_QUEUED_ASYNC_TEMP_DATA_REQS BIGINT pool_queued_async_temp_data_reqs - Data prefetch requests for temporary table spaces monitor element
POOL_QUEUED_ASYNC_TEMP_INDEX_REQS BIGINT pool_queued_async_temp_index_reqs - Index prefetch requests for temporary table spaces monitor element
POOL_QUEUED_ASYNC_TEMP_XDA_REQS BIGINT pool_queued_async_temp_xda_reqs - XDA data prefetch requests for temporary table spaces monitor element
POOL_QUEUED_ASYNC_OTHER_REQS BIGINT pool_queued_async_other_reqs - Non-prefetch requests monitor element
POOL_QUEUED_ASYNC_DATA_PAGES BIGINT pool_queued_async_data_pages - Data pages prefetch requests monitor element
POOL_QUEUED_ASYNC_INDEX_PAGES BIGINT pool_queued_async_index_pages - Index pages prefetch requests monitor element
POOL_QUEUED_ASYNC_XDA_PAGES BIGINT pool_queued_async_xda_pages - XDA pages prefetch requests monitor element
POOL_QUEUED_ASYNC_TEMP_DATA_PAGES BIGINT pool_queued_async_temp_data_pages - Data pages prefetch requests for temporary table spaces monitor element
POOL_QUEUED_ASYNC_TEMP_INDEX_PAGES BIGINT pool_queued_async_temp_index_pages - Index pages prefetch requests for temporary table spaces monitor element
POOL_QUEUED_ASYNC_TEMP_XDA_PAGES BIGINT pool_queued_async_temp_xda_pages - XDA data pages prefetch requests for temporary table spaces monitor element
POOL_FAILED_ASYNC_DATA_REQS BIGINT pool_failed_async_data_reqs - Failed data prefetch requests monitor element
POOL_FAILED_ASYNC_INDEX_REQS BIGINT pool_failed_async_index_reqs - Failed index prefetch requests monitor element
POOL_FAILED_ASYNC_XDA_REQS BIGINT pool_failed_async_xda_reqs - Failed XDA prefetch requests monitor element
POOL_FAILED_ASYNC_TEMP_DATA_REQS BIGINT pool_failed_async_temp_data_reqs - Failed data prefetch requests for temporary table spaces monitor element
POOL_FAILED_ASYNC_TEMP_INDEX_REQS BIGINT pool_failed_async_temp_index_reqs - Failed index prefetch requests for temporary table spaces monitor element
POOL_FAILED_ASYNC_TEMP_XDA_REQS BIGINT pool_failed_async_temp_xda_reqs - Failed XDA prefetch requests for temporary table spaces monitor element
POOL_FAILED_ASYNC_OTHER_REQS BIGINT pool_failed_async_other_reqs - Failed non-prefetch requests monitor element
PREFETCH_WAIT_TIME BIGINT prefetch_wait_time - Time waited for prefetch
PREFETCH_WAITS BIGINT prefetch_waits - Prefetcher wait count monitor element
POOL_DATA_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT pool_data_gbp_indep_pages_found_in_lbp - Group buffer pool independent data pages found in local buffer pool monitor element
POOL_INDEX_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT pool_index_gbp_indep_pages_found_in_lbp - Group buffer pool independent index pages found in local buffer pool monitor element
POOL_XDA_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT pool_xda_gbp_indep_pages_found_in_lbp - Group buffer pool XDA independent pages found in local buffer pool monitor element
NUM_WORKING_COPIES BIGINT Number of working copies.
IDA_SEND_WAIT_TIME BIGINT ida_send_wait_time - Time spent waiting to send data monitor element

The total amount of time spent waiting to send data to an in-database analytics process.

IDA_SENDS_TOTAL BIGINT ida_sends_total - Number of times data sent monitor element

The total number of times data was sent to an in-database analytics process.

IDA_SEND_VOLUME BIGINT ida_send_volume - Total data volume sent monitor element

The total volume of data sent from the database server to an in-database analytics process. The value is reported in bytes.

IDA_RECV_WAIT_TIME BIGINT ida_recv_wait_time - Time spent waiting to receive data monitor element

The total amount of time spent waiting to receive data from an in-database analytics process.

IDA_RECVS_TOTAL BIGINT ida_recvs_total - Number of times data received monitor element

The total number of times data was received from an in-database analytics process.

IDA_RECV_VOLUME BIGINT ida_recv_volume - Total data volume received monitor element

The total volume of data the database server received from an in-database analytics process. The value is reported in bytes.

STMTNO INTEGER stmtno - Statement number monitor element
NUM_ROUTINES INTEGER num_routines - Number of routines
ROWS_DELETED BIGINT rows_deleted - Rows deleted monitor element
ROWS_INSERTED BIGINT rows_inserted - Rows inserted monitor element
ROWS_UPDATED BIGINT rows_updated - Rows updated monitor element
TOTAL_HASH_JOINS BIGINT total_hash_joins - Total Hash Joins monitor element
TOTAL_HASH_LOOPS BIGINT total_hash_loops - Total Hash Loops monitor element
HASH_JOIN_OVERFLOWS BIGINT hash_join_overflows - Hash Join Overflows monitor element
HASH_JOIN_SMALL_OVERFLOWS BIGINT hash_join_small_overflows - Hash Join Small Overflows monitor element
POST_SHRTHRESHOLD_HASH_JOINS BIGINT post_shrthreshold_hash_joins - Post threshold hash joins monitor element
TOTAL_OLAP_FUNCS BIGINT total_olap_funcs - Total OLAP Functions monitor element
OLAP_FUNC_OVERFLOWS BIGINT olap_func_overflows - OLAP Function Overflows monitor element
INT_ROWS_DELETED BIGINT int_rows_deleted - Internal Rows Deleted monitor element
INT_ROWS_INSERTED BIGINT int_rows_inserted - Internal Rows Inserted monitor element
INT_ROWS_UPDATED BIGINT int_rows_updated - Internal Rows Updated monitor element
POOL_COL_L_READS BIGINT pool_col_l_reads - Buffer pool column-organized logical reads
POOL_TEMP_COL_L_READS BIGINT pool_temp_col_l_reads - Buffer pool column-organized temporary logical reads
POOL_COL_P_READS BIGINT pool_col_p_reads - Buffer pool column-organized physical reads
POOL_TEMP_COL_P_READS BIGINT pool_temp_col_p_reads - Buffer pool column-organized temporary physical reads
POOL_COL_LBP_PAGES_FOUND BIGINT pool_col_lbp_pages_found - Buffer pool column-organized LBP pages found
POOL_COL_WRITES BIGINT pool_col_writes - Buffer pool column-organized writes
POOL_COL_GBP_L_READS BIGINT pool_col_gbp_l_reads - Buffer pool column-organized GBP logical reads
POOL_COL_GBP_P_READS BIGINT pool_col_gbp_p_reads - Buffer pool column-organized GBP physical reads
POOL_COL_GBP_INVALID_PAGES BIGINT pool_col_gbp_invalid_pages - Buffer pool column-organized GBP invalid data pages
POOL_COL_GBP_INDEP_PAGES_FOUND_IN_LBP BIGINT pool_col_gbp_indep_pages_found_in_lbp - Buffer pool column-organized GBP independent pages found in local buffer pool
POOL_QUEUED_ASYNC_COL_REQS BIGINT pool_queued_async_col_reqs - Column-organized prefetch requests
POOL_QUEUED_ASYNC_TEMP_COL_REQS BIGINT pool_queued_async_temp_col_reqs - Column-organized temporary prefetch requests
POOL_QUEUED_ASYNC_COL_PAGES BIGINT pool_queued_async_col_pages - Column-organized page prefetch requests
POOL_QUEUED_ASYNC_TEMP_COL_PAGES BIGINT pool_queued_async_temp_col_pages - Column-organized page temporary prefetch requests
POOL_FAILED_ASYNC_COL_REQS BIGINT pool_failed_async_col_reqs - Failed column-organized prefetch requests
POOL_FAILED_ASYNC_TEMP_COL_REQS BIGINT pool_failed_async_temp_col_reqs - Failed column-organized temporary prefetch requests
TOTAL_COL_TIME BIGINT total_col_time - Total column-organized time
TOTAL_COL_PROC_TIME BIGINT total_col_proc_time - Total column-organized processing time
TOTAL_COL_EXECUTIONS BIGINT total_col_executions - Total column-organized executions
COMM_EXIT_WAIT_TIME BIGINT comm_exit_wait_time - Communication buffer exit wait time
COMM_EXIT_WAITS BIGINT comm_exit_waits - Communication buffer exit number of waits
POST_THRESHOLD_HASH_JOINS BIGINT post_threshold_hash_joins - Hash Join Threshold monitor element
POOL_DATA_CACHING_TIER_L_READS BIGINT Reserved for future use.
POOL_INDEX_CACHING_TIER_L_READS BIGINT Reserved for future use.
POOL_XDA_CACHING_TIER_L_READS BIGINT Reserved for future use.
POOL_COL_CACHING_TIER_L_READS BIGINT Reserved for future use.
POOL_DATA_CACHING_TIER_PAGE_WRITES BIGINT Reserved for future use.
POOL_INDEX_CACHING_TIER_PAGE_WRITES BIGINT Reserved for future use.
POOL_XDA_CACHING_TIER_PAGE_WRITES BIGINT Reserved for future use.
POOL_COL_CACHING_TIER_PAGE_WRITES BIGINT Reserved for future use.
POOL_DATA_CACHING_TIER_PAGE_UPDATES BIGINT Reserved for future use.
POOL_INDEX_CACHING_TIER_PAGE_UPDATES BIGINT Reserved for future use.
POOL_XDA_CACHING_TIER_PAGE_UPDATES BIGINT Reserved for future use.
POOL_COL_CACHING_TIER_PAGE_UPDATES BIGINT Reserved for future use.
POOL_CACHING_TIER_PAGE_READ_TIME BIGINT Reserved for future use.
POOL_CACHING_TIER_PAGE_WRITE_TIME BIGINT Reserved for future use.
POOL_DATA_CACHING_TIER_PAGES_FOUND BIGINT Reserved for future use.
POOL_INDEX_CACHING_TIER_PAGES_FOUND BIGINT Reserved for future use.
POOL_XDA_CACHING_TIER_PAGES_FOUND BIGINT Reserved for future use.
POOL_COL_CACHING_TIER_PAGES_FOUND BIGINT Reserved for future use.
POOL_DATA_CACHING_TIER_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_INDEX_CACHING_TIER_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_XDA_CACHING_TIER_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_COL_CACHING_TIER_GBP_INVALID_PAGES BIGINT Reserved for future use.
POOL_DATA_CACHING_TIER_GBP_INDEP_PAGES_FOUND BIGINT Reserved for future use.
POOL_INDEX_CACHING_TIER_GBP_INDEP_PAGES_FOUND BIGINT Reserved for future use.
POOL_XDA_CACHING_TIER_GBP_INDEP_PAGES_FOUND BIGINT Reserved for future use.
POOL_COL_CACHING_TIER_GBP_INDEP_PAGES_FOUND BIGINT Reserved for future use.
TOTAL_HASH_GRPBYS BIGINT total_hash_grpbys - Total hash group by operations
HASH_GRPBY_OVERFLOWS BIGINT hash_grpby_overflows - Hash group by overflows
POST_THRESHOLD_HASH_GRPBYS BIGINT post_threshold_hash_grpbys - Hash group by threshold
POST_THRESHOLD_OLAP_FUNCS BIGINT post_threshold_olap_funcs - OLAP function threshold monitor element
EXT_TABLE_RECV_WAIT_TIME BIGINT ext_table_recv_wait_time - Total agent wait time for external table readers monitor element
EXT_TABLE_RECVS_TOTAL BIGINT ext_table_recvs_total - Total row batches received from external table readers monitor element
EXT_TABLE_RECV_VOLUME BIGINT ext_table_recv_volume - Total data received from external table readers monitor element
EXT_TABLE_READ_VOLUME BIGINT ext_table_read_volume - Total data read by external table readers monitor element
EXT_TABLE_SEND_WAIT_TIME BIGINT ext_table_send_wait_time - Total agent wait time for external table writers monitor element
EXT_TABLE_SENDS_TOTAL BIGINT ext_table_sends_total - Total row batches sent to external table writers monitor element
EXT_TABLE_SEND_VOLUME BIGINT ext_table_send_volume - Total data sent to external table writers monitor element
EXT_TABLE_WRITE_VOLUME BIGINT ext_table_write_volume - Total data written by external table writers monitor element
SEMANTIC_ENV_ID BIGINT semantic_env_id - Query semantic compilation environment ID monitor element
STMTID BIGINT stmtid - Query statement ID monitor element
PLANID BIGINT planid - Query plan ID monitor element
PREP_WARNING INTEGER prep_warning - Prepare warning SQLCODE monitor element
PREP_WARNING_REASON INTEGER prep_warning_reason - Prepare warning SQLCODE reason identifier monitor element
POST_THRESHOLD_COL_VECTOR_CONSUMERS BIGINT post_threshold_col_vector_consumers - Post threshold columnar_vector consumers
TOTAL_COL_VECTOR_CONSUMERS BIGINT total_col_vector_consumers - Total columnar_vector consumers
ACTIVE_HASH_GRPBYS_TOP BIGINT active_hash_grpbys_top - Active hash Group By operations high watermark
ACTIVE_HASH_JOINS_TOP BIGINT active_hash_joins_top - Active hash joins operations high watermark
ACTIVE_OLAP_FUNCS_TOP BIGINT active_olap_funcs_top - Active OLAP functions operations high watermark
ACTIVE_PEAS_TOP BIGINT active_peas_top - Active partial early aggregations operations high watermark
ACTIVE_PEDS_TOP BIGINT active_peds_top - Active partial early distinct operations high watermark
ACTIVE_SORT_CONSUMERS_TOP BIGINT active_sort_consumers_top - Active sort consumers high watermark
ACTIVE_SORTS_TOP BIGINT active_sorts_top - Active Sorts high watermark
ACTIVE_COL_VECTOR_CONSUMERS_TOP BIGINT active_col_vector_consumers_top - Active columnar_vector consumers high watermark
SORT_CONSUMER_HEAP_TOP BIGINT sort_consumer_heap_top - Individual private sort heap consumer high watermark
SORT_CONSUMER_SHRHEAP_TOP BIGINT sort_consumer_shrheap_top - Individual shared sort heap consumer high watermark
SORT_HEAP_TOP BIGINT sort_heap_top - Sort private heap high watermark
SORT_SHRHEAP_TOP BIGINT sort_shrheap_top - Sort share heap high watermark
TOTAL_INDEX_BUILD_TIME BIGINT total_index_build_time - Total time spent building indexes due to index creation
TOTAL_INDEX_BUILD_PROC_TIME BIGINT total_index_build_proc_time - Total non-wait time spent building indexes due to index creation
TOTAL_INDEXES_BUILT BIGINT total_indexes_built - Total number of indexes built
FCM_TQ_RECV_WAITS_TOTAL BIGINT fcm_tq_recv_waits_total - Number of times spent waiting to receive the next buffer monitor element
FCM_MESSAGE_RECV_WAITS_TOTAL BIGINT fcm_message_recv_waits_total - Number of times spent waiting for FCM reply message monitor element
FCM_TQ_SEND_WAITS_TOTAL BIGINT fcm_tq_send_waits_total - Number of times spent waiting to send the next buffer monitor element
FCM_MESSAGE_SEND_WAITS_TOTAL BIGINT fcm_message_send_waits_total - Number of times spent blocking on an FCM message send monitor element
FCM_SEND_WAITS_TOTAL BIGINT fcm_send_waits_total - Number of times spent blocking on an FCM send operation monitor element
FCM_RECV_WAITS_TOTAL BIGINT fcm_recv_waits_total - Number of times spent waiting to receive data through FCM monitor element
COL_VECTOR_CONSUMER_OVERFLOWS BIGINT col_vector_consumer_overflows - Columnar vector consumer overflows monitor element.
TOTAL_COL_SYNOPSIS_TIME BIGINT total_col_synopsis_time - Total column-organized synopsis table time monitor element
TOTAL_COL_SYNOPSIS_PROC_TIME BIGINT total_col_synopsis_proc_time - Total column-organized synopsis table processing time monitor element
TOTAL_COL_SYNOPSIS_EXECUTIONS BIGINT total_col_synopsis_executions - Total column-organized synopsis table executions monitor element
COL_SYNOPSIS_ROWS_INSERTED BIGINT col_synopsis_rows_inserted - Column-organized synopsis table rows inserted monitor element
ADM_OVERFLOWS BIGINT adm_overflows - Number of times an activity fit as an overflow monitor element
ADM_BYPASS_ACT_TOTAL BIGINT adm_bypass_act_total - Number of times admission control was bypassed monitor element
LOB_PREFETCH_WAIT_TIME BIGINT lob_prefetch_wait_time - LOB prefetch wait time monitor element
LOB_PREFETCH_REQS BIGINT lob_prefetch_reqs - LOB prefetcher request count monitor element
FED_ROWS_DELETED BIGINT fed_rows_deleted - Rows deleted by a federation system monitor element
FED_ROWS_INSERTED BIGINT fed_rows_inserted - Rows inserted by a federation system monitor element
FED_ROWS_UPDATED BIGINT fed_rows_updated - Rows updated by a federation system monitor element
FED_ROWS_READ BIGINT fed_rows_read - Rows read by a federation system monitor element
FED_WAIT_TIME BIGINT fed_wait_time - Time spent by a federation server monitor element
FED_WAITS_TOTAL BIGINT fed_waits_total - Total number of execution times for a federation server monitor element
STMT_TEXT CLOB(2M) 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.
MAX_COORD_STMT_EXEC_TIME_ARGS BLOB(10M) max_coord_stmt_exec_time_args - Maximum coordinator statement execution time arguments
STMT_COMMENTS BLOB(10K) stmt_comments - Comments in SQL statement text
ESTIMATED_SORT_SHRHEAP_TOP BIGINT estimated_sort_shrheap_top - Estimated shared sort heap consumption monitor element
ESTIMATED_SORT_CONSUMERS_TOP BIGINT estimated_sort_consumers_top - Estimated concurrently executing sort memory consumers monitor element
ESTIMATED_RUNTIME BIGINT estimated_runtime - Estimated execution time for a DML statement monitor element monitor element
AGENTS_TOP BIGINT  
LAST_EXEC_ERROR INTEGER last_exec_error - Last error in statement monitor element
LAST_EXEC_ERROR_SQLERRMC VARCHAR(70) last_exec_error_sqlerrmc - tokens of the last statement error monitor element
LAST_EXEC_ERROR_TIMESTAMP TIMESTAMP last_exec_error_timestamp - time of last statement error monitor element
LAST_EXEC_WARNING INTEGER last_exec_warning - Last warning in the statement monitor element
LAST_EXEC_WARNING_SQLERRMC VARCHAR(70) last_exec_warning_SQLERRMC - Tokens for last statement warning monitor element
LAST_EXEC_WARNING_TIMESTAMP TIMESTAMP last_exec_warning_timestamp - Time of last statement warning monitor element
NUM_EXEC_WITH_ERROR BIGINT num_exec_with_error - Number of executions with errors monitor element
NUM_EXEC_WITH_WARNING BIGINT num_exec_with_warning - Number of executions with warnings monitor element
TENANT_ID BIGINT tenant_id - Tenant identifier monitor element

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.

Depending on the package cache size and contents, retrieving the full information available from the MON_GET_PKG_CACHE_STMT routine might require a substantial amount of memory. To reduce the amount of memory that is allocated to retrieve the results, use selective parameter inputs and limit the columns that are selected. Columns that require large amounts of memory to retrieve include the STMT_TEXT and the COMP_ENV_DESC columns.

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 select the STMT_TEXT and the COMP_ENV_DESC columns.

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 is an example of 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.
With the earlier output, you 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.