Monitoring metrics for column-organized tables

Monitor elements to assess buffer pool efficiency

You can use a set of monitor elements to monitor data page I/O for column-organized tables separately from row-organized tables. You can use these monitor elements to understand what portion of the I/O is being driven by access to column-organized tables when a workload impacts both row-organized and column-organized tables. These elements can also help you to tune the system, for example, by helping you to decide whether to place column-organized tables in separate table spaces, or whether to use a separate buffer pool.

Monitor elements to monitor prefetch requests for data in column-organized tables

The prefetch logic for queries that access column-organized tables is used to asynchronously fetch only those pages that each thread will read for each column that is accessed during query execution. If the pages for a particular column are consistently available in the buffer pool, prefetching for that column is disabled until the pages are being read synchronously, at which time prefetching for that column is enabled again.

Although the number of pages that a thread can prefetch simultaneously is limited by the prefetch size of the table space that is being accessed, several threads can prefetch pages simultaneously.

Monitor elements can help you to track the volume of requests for data in column-organized tables that are submitted to prefetchers and the number of pages that prefetchers skipped reading because the pages were in memory. Efficient prefetching of data in column-organized tables is important for mitigating the I/O costs of data scans.

Monitor elements to measure column data size

A column-organized table is associated with a new table object where the column data is stored.

The first two elements accurately reflect the logical and physical size of the column-organized data object but are more expensive to determine because they must calculate the space that is being used. These elements are reported by the ADMIN_GET_TAB_INFO table function. The COL_OBJECT_L_PAGES element is similar to the existing DATA_OBJECT_L_PAGES element and provides a cheaper alternative to estimating size (the number of pages multiplied by the page size), although this estimate might be inaccurate.

Monitor element to report information about data organization

The monitor element, TAB_ORGANIZATION, reports information about the organization of data in a table and is returned by the MON_GET_TABLE table function.

Monitor elements to measure time spent

Time-spent monitor elements provide information about how the database manager spends time processing column-organized tables. The time-spent elements are broadly categorized into wait time elements and processing time elements.

Those three monitor elements count the total time that is spent in column-organized data processing across all column-organized processing subagents. The TOTAL_COL_TIME element represents the total elapsed time over all column-organized processing subagents. The TOTAL_COL_PROC_TIME element represents the subset of this total elapsed time in which the column-organized processing subagents were not idle on a measured wait time (for example, lock wait or IO). The TOTAL_COL_EXECUTIONS element represents the total number of times that data in column-organized tables was accessed during statement execution.

The parent element of TOTAL_COL_TIME is TOTAL_SECTION_TIME. The parent element of TOTAL_COL_PROC_TIME is TOTAL_SECTION_PROC_TIME. The parent elements are the same in both the request and activity dimensions.

Monitor elements for the hashed GROUP BY operator

Monitor elements for columnar vector memory

Monitor interfaces to get request metrics that are aggregated along different dimensions

Monitor interfaces to get activity metrics

Activity metrics are a subset of request metrics that are measured during execution of an SQL statement.

Monitor interfaces to get database object metrics

Object metrics interfaces report monitor elements for a specific database object or for an entire database.

• The MON_GET_DATABASE, MON_GET_DATABASE_DETAILS, MON_GET_BUFFERPOOL, and MON_GET_TABLESPACE table functions return the following monitor elements:
  • POOL_COL_L_READS
  • POOL_TEMP_COL_L_READS
  • POOL_COL_P_READS
  • POOL_TEMP_COL_P_READS
  • POOL_COL_LBP_PAGES_FOUND
  • POOL_COL_WRITES
  • POOL_ASYNC_COL_READS
  • POOL_ASYNC_COL_READ_REQS
  • POOL_ASYNC_COL_WRITES
  • POOL_ASYNC_COL_LBP_PAGES_FOUND
  • POOL_COL_GBP_L_READS
  • POOL_COL_GBP_P_READS
  • POOL_COL_GBP_INVALID_PAGES
  • POOL_COL_GBP_INDEP_PAGES_FOUND_IN_LBP
  • POOL_ASYNC_COL_GBP_L_READS
  • POOL_ASYNC_COL_GBP_P_READS
  • POOL_ASYNC_COL_GBP_INVALID_PAGES
  • POOL_ASYNC_COL_GBP_INDEP_PAGES_FOUND_IN_LBP
  • POOL_QUEUED_ASYNC_COL_REQS
  • POOL_QUEUED_ASYNC_TEMP_COL_REQS
  • POOL_QUEUED_ASYNC_COL_PAGES
  • POOL_QUEUED_ASYNC_TEMP_COL_PAGES
  • POOL_FAILED_ASYNC_COL_REQS
  • POOL_FAILED_ASYNC_TEMP_COL_REQS
  • SKIPPED_PREFETCH_COL_P_READS
  • SKIPPED_PREFETCH_TEMP_COL_P_READS
  • SKIPPED_PREFETCH_UOW_COL_P_READS
  • SKIPPED_PREFETCH_UOW_TEMP_COL_P_READS
  The data type for each of these elements is BIGINT. These elements are reported when the OBJECT METRICS control is set to BASE.
• The MON_GET_TABLE table function returns the following monitor elements:
  • COL_OBJECT_L_PAGES
  • NUM_COLUMNS_REFERENCED
  • OBJECT_COL_L_READS
  • OBJECT_COL_P_READS
  • OBJECT_COL_GBP_L_READS
  • OBJECT_COL_GBP_P_READS
  • OBJECT_COL_GBP_INVALID_PAGES
  • OBJECT_COL_LBP_PAGES_FOUND
  • OBJECT_COL_GBP_INDEP_PAGES_FOUND_IN_LBP
  • SECTION_EXEC_WITH_COL_REFERENCES
  • TAB_ORGANIZATION
  The data type for each of these elements is BIGINT. The TAB_ORGANIZATION and COL_OBJECT_L_PAGES elements are always reported. The other elements are reported when the OBJECT METRICS control is set to EXTENDED.
• The MON_GET_TABLE_USAGE_LIST table function returns the following monitor elements:
  • OBJECT_COL_L_READS
  • OBJECT_COL_P_READS
  • OBJECT_COL_GBP_L_READS
  • OBJECT_COL_GBP_P_READS
  • OBJECT_COL_GBP_INVALID_PAGES
  • OBJECT_COL_LBP_PAGES_FOUND
  • OBJECT_COL_GBP_INDEP_PAGES_FOUND_IN_LBP
  The data type for each of these elements is BIGINT. These elements are reported when the OBJECT METRICS control is set to EXTENDED.
• The ADMIN_GET_TAB_INFO table function returns the following monitor elements:
  • COL_OBJECT_L_SIZE
  • COL_OBJECT_P_SIZE
• The following existing metrics return NULL because they do not apply to column-organized tables:
  • LARGE_RIDS
  • LARGE_SLOTS

Administrative view interfaces to get hit ratio metrics

Administrative views are predefined views that are built on top of monitor interfaces that perform common calculations on monitor data.

Changed administrative procedures generate reports that contain monitor data

Changed monitor interface gets information about the agents that run within a particular service class