P

Usage

The value of this element matches a value from column PKGID of view SYSCAT.PACKAGES.

Usage

You may use this element to help identify the application program and the SQL statement that is executing.

Usage

Use this element to help identify the package and the SQL statement that is currently executing.

Usage

Too many page reorganizations can result in less than optimal insert performance. You can use the REORG TABLE utility to reorganize a table and eliminate fragmentation. You can also use the APPEND parameter for the ALTER TABLE statement to indicate that all inserts are appended at the end of a table to avoid page reorganizations.

In situations where updates to rows causes the row length to increase, the page may have enough space to accommodate the new row, but a page reorg may be required to defragment that space. If the page does not have enough space for the new larger row, an overflow record is created causing overflow_accesses during reads. You can avoid both situations by using fixed length columns instead of varying length columns.

Usage

Page reclaims related to internally maintained object space maps are counted separately.

Usage

Page reclaims related to internally maintained object space maps are counted separately.

Usage

If block-based buffer pool is enabled, this element reports the total number of pages read by block I/O. Otherwise, this element returns 0.

To calculate the average number of pages sequentially prefetched per block-based I/O, divide the value of the pages_from_block_ios monitor element by the value of the block_ios monitor element. If this value is much less than the BLOCKSIZE option you have defined for the block-based buffer pool in the CREATE BUFFERPOOL or ALTER BUFFERPOOL statement, then block-based I/O is not being used to its full advantage. One possible cause for this is a mismatch between the extent size for the table space being sequentially prefetched and the block size of the block-based buffer pool.

Usage

Usage

Usage

Use this element along with the parent_uow_id element and appl_id element to uniquely identify the parent activity of the activity described in this activity record.

Usage

Use this element along with the parent_activity_id element and appl_id element to uniquely identify the parent activity of the activity described in this activity record.

Usage

Most event monitors do not output their results until database deactivation. You can use the FLUSH EVENT MONITOR <monitorName> statement to force monitor values to the event monitor output writer. This allows you to force event monitor records to the writer without needing to stop and restart the event monitor. This element indicates whether an event monitor record was the result of flush operation and so is a partial record.

Flushing an event monitor does not cause its values to be reset. This means that a complete event monitor record is still generated when the event monitor is triggered.

Usage

Use this element to determine how much actual time is spent at this data source processing statements in pass-through mode.

Usage

Use with the PHASE_START_EVENT_TIMESTAMP and member elements to associate the phase stop record with the corresponding start record.

Usage

• If the event is the stopping of a utility phase or processing stage (UTILPHASESTOP), this is the time of the corresponding start of the utility phase (UTILPHASESTART), otherwise empty.

Usage

 
    1 - (Package Cache Inserts / Package Cache Lookups)

Usage

 
    1 - (Package Cache Inserts / Package Cache Lookups)

The package cache hit ratio tells you whether or not the package cache is being used effectively. If the hit ratio is high (more than 0.8), the cache is performing well. A smaller hit ratio may indicate that the package cache should be increased.

You will need to experiment with the size of the package cache to find the optimal number for the pckcachesz configuration parameter. For example, you might be able to use a smaller package cache size if there is no increase in the pkg_cache_inserts element when you decrease the size of the cache. Decreasing the package cache size frees up system resources for other work. It is also possible that you could improve overall system performance by increasing the size of the package cache if by doing so, you decrease the number of pkg_cache_inserts. This experimentation is best done under full workload conditions.

You can use this element with ddl_sql_stmts to determine whether or not the execution of DDL statements is impacting the performance of the package cache. Sections for dynamic SQL statements can become invalid when DDL statements are executed. Invalid sections are implicitly prepared by the system when next used. The execution of a DDL statement could invalidate a number of sections and the resulting additional processing time required when preparing those sections could significantly impact performance. In this case, the package cache hit ratio reflects the implicit recompilation of invalid sections. It does not reflect the insertion of new sections into the cache, so increasing the size of the package cache will not improve overall performance. You might find it less confusing to tune the cache for an application on its own before working in the full environment.

It is necessary to determine the role that DDL statements are playing in the value of the package cache hit ratio before deciding on what action to take. If DDL statements rarely occur, then cache performance may be improved by increasing its size. If DDL statements are frequent, then improvements may require that you limit the use of DDL statements (possibly to specific time periods).

The static_sql_stmts and dynamic_sql_stmts counts can be used to help provide information about the quantity and type of sections being cached.

Usage

Use this element with the pkg_cache_size_top monitor element to determine whether the size of the package cache needs to be increased to avoid overflowing.

Usage

If the package cache overflowed, then this element contains the largest size reached by the package cache during the overflow.

Check the pkg_cache_num_overflows monitor element to determine if such a condition occurred.

   maximum package cache size / 4096

Usage

Use this monitor element with the stmtid and semantic_env_id monitor elements to detect changes in access plan that might affect performance.

Usage

 pool_async_col_reads / pool_async_col_read_reqs

This average can help you determine the average read I/O size used by the prefetcher.

Usage

  pool_col_writes - pool_async_col_writes

By comparing the ratio of asynchronous to synchronous writes, you can gain insight into how well the buffer pool page cleaners are performing. This ratio can be helpful when you are tuning the num_iocleaners configuration parameter.

Usage

GBP = ( pool_async_data_gbp_l_reads - pool_async_data_gbp_p_reads ) / pool_async_data_gbp_l_reads

Usage

GBP = ( pool_async_data_gbp_l_reads - pool_async_data_gbp_p_reads ) / pool_async_data_gbp_l_reads

Usage

GBP = ( pool_async_data_gbp_l_reads - pool_async_data_gbp_p_reads ) / pool_async_data_gbp_l_reads

Usage

GBP = ( pool_async_data_gbp_l_reads - pool_async_data_gbp_p_reads ) / pool_async_data_gbp_l_reads

Usage

 pool_async_data_reads / pool_async_data_read_reqs

This average can help you determine the average read I/O size used by the prefetcher. This data can also be helpful in understanding the large block I/O requirements of the measured workload.

Usage

# of sync data reads = pool_data_p_reads + pool_temp_data_p_reads - pool_async_data_reads

(pool_async_data_reads + pool_async_index_reads)/
(pool_data_p_reads + pool_temp_data_p_reads + pool_index_p_reads + pool_temp_index_p_reads)

Asynchronous reads are performed by database manager prefetchers.

Usage

GBP = ( pool_async_index_gbp_l_reads - pool_async_index_gbp_p_reads ) / pool_async_index_gbp_l_reads

Usage

GBP = ( pool_async_index_gbp_l_reads - pool_async_index_gbp_p_reads ) / pool_async_index_gbp_l_reads

Usage

GBP = ( pool_async_index_gbp_l_reads - pool_async_index_gbp_p_reads ) / pool_async_index_gbp_l_reads

Usage

GBP = ( pool_async_index_gbp_l_reads - pool_async_index_gbp_p_reads ) / pool_async_index_gbp_l_reads

Usage

# of sync index reads = pool_index_p_reads + pool_temp_index_p_reads - pool_async_index_reads

(pool_async_index_reads) / (pool_index_p_reads + pool_temp_index_p_reads)

Asynchronous reads are performed by database manager prefetchers.

Usage

  pool_index_writes - pool_async_index_writes

By comparing the ratio of asynchronous to synchronous writes, you can gain insight into how well the buffer pool page cleaners are performing. This ratio can be helpful when you are tuning the num_iocleaners configuration parameter.

Usage

 pool_write_time - pool_async_write_time

 pool_async_write_time
  / (pool_async_data_writes 
    + pool_async_index_writes)

These calculations can be used to understand the I/O work being performed.

Usage

GBP = ( pool_async_xda_gbp_l_reads - pool_async_xda_gbp_p_reads ) / pool_async_xda_gbp_l_reads

Usage

GBP = ( pool_async_xda_gbp_l_reads - pool_async_xda_gbp_p_reads ) / pool_async_xda_gbp_l_reads

Usage

GBP = ( pool_async_xda_gbp_l_reads - pool_async_xda_gbp_p_reads ) / pool_async_xda_gbp_l_reads

Usage

GBP = ( pool_async_xda_gbp_l_reads - pool_async_xda_gbp_p_reads ) / pool_async_xda_gbp_l_reads

Usage

 
 pool_xda_p_reads + pool_temp_xda_p_reads - pool_async_xda_reads

By comparing the ratio of asynchronous to synchronous reads, you can gain insight into how well the prefetchers are working. This element can be helpful when you are tuning the num_ioservers configuration parameter.

Asynchronous reads are performed by database manager prefetchers.

Usage

(pool_col_lbp_pages_found - pool_async_col_lbp_pages_found) / pool_col_l_reads

(pool_col_gbp_l_reads - pool_col_gbp_p_reads) / pool_col_gbp_l_reads

Usage

(pool_col_lbp_pages_found - pool_async_col_lbp_pages_found) / pool_col_l_reads

(pool_col_gbp_l_reads - pool_col_gbp_p_reads) / pool_col_gbp_l_reads

Usage

Column-organized monitor elements can help you understand what portion of the I/O is being driven by access to column-organized tables when a workload includes a mixture of both row-organized and column-organized tables.

(pool_col_lbp_pages_found - pool_async_col_lbp_pages_found) / pool_col_l_reads

Increasing buffer pool size will generally improve the hit ratio, but you will reach a point of diminishing return. Ideally, if you could allocate a buffer pool large enough to store your entire database, then once the system is up and running you would get a hit ratio of 100%. However, this is unrealistic in most cases. The significance of the hit ratio really depends on the size of your data, and the way it is accessed. A very large database where data is accessed evenly would have a poor hit ratio. There is little you can do with very large tables.

To improve hit ratios for smaller, frequently accessed tables and indexes, assign them to individual buffer pools.

Usage

(pool_col_lbp_pages_found - pool_async_col_lbp_pages_found) / pool_col_l_reads

(pool_col_gbp_l_reads - pool_col_gbp_p_reads) / pool_col_gbp_l_reads

Column-organized monitor elements can help you understand what portion of the I/O is being driven by access to column-organized tables when a workload includes a mixture of both row-organized and column-organized tables.

Usage

If a buffer pool data page is written to disk for a high percentage of the value of the pool_col_p_reads monitor element, you may be able to improve performance by increasing the number of buffer pool pages available for the database.

The system does not always write a page to make room for a new one. If the page has not been updated, it can simply be replaced. This replacement is not counted for this element.

The data page can be written by an asynchronous page-cleaner agent before the buffer pool space is required, as reported by the pool_async_col_writes monitor element. These asynchronous page writes are included in the value of this element in addition to synchronous page writes.

If all applications are updating the database, increasing the size of the buffer pool may not have much impact on performance since most of the buffer pool pages contain updated data, which must be written to disk. However, if the updated pages can be used by other units of work before being written out, the buffer pool can save a write and a read, which will improve your performance.

Usage

(pool_data_lbp_pages_found - pool_async_data_lbp_pages_found) / pool_data_l_reads

(pool_data_gbp_l_reads - pool_data_gbp_p_reads) / pool_data_gbp_l_reads

Usage

(pool_data_lbp_pages_found - pool_async_data_lbp_pages_found) / pool_data_l_reads

(pool_data_gbp_l_reads - pool_data_gbp_p_reads) / pool_data_gbp_l_reads

Usage

(pool_data_lbp_pages_found - pool_async_data_lbp_pages_found) / pool_data_l_reads

(pool_data_gbp_l_reads - pool_data_gbp_p_reads) / pool_data_gbp_l_reads

Usage

(pool_data_lbp_pages_found - pool_async_data_lbp_pages_found) / pool_data_l_reads

(pool_data_gbp_l_reads - pool_data_gbp_p_reads) / pool_data_gbp_l_reads

Usage

((pool_data_lbp_pages_found
- pool_async_data_lbp_pages_found) / (pool_data_l_reads + pool_temp_data_l_reads))
× 100 

Increasing buffer pool size will generally improve the hit ratio, but you will reach a point of diminishing return. Ideally, if you could allocate a buffer pool large enough to store your entire database, then once the system is up and running you would get a hit ratio of 100%. However, this is unrealistic in most cases. The significance of the hit ratio really depends on the size of your data, and the way it is accessed. A very large database where data is accessed evenly would have a poor hit ratio. There is little you can do with very large tables.

To improve hit ratios for smaller, frequently accessed tables and indexes, assign them to individual buffer pools.

Usage

(pool_async_data_reads + pool_async_index_reads)/
(pool_data_p_reads + pool_temp_data_p_reads + pool_index_p_reads + pool_temp_index_p_reads)

Usage

If a buffer pool data page is written to disk for a high percentage of the value of the pool_data_p_reads monitor element, you may be able to improve performance by increasing the number of buffer pool pages available for the database.

The system does not always write a page to make room for a new one. If the page has not been updated, it can simply be replaced. This replacement is not counted for this element.

The data page can be written by an asynchronous page-cleaner agent before the buffer pool space is required, as reported by the pool_async_data_writes monitor element. These asynchronous page writes are included in the value of this element in addition to synchronous page writes.

If all applications are updating the database, increasing the size of the buffer pool may not have much impact on performance since most of the buffer pool pages contain updated data, which must be written to disk. However, if the updated pages can be used by other units of work before being written out, the buffer pool can save a write and a read, which will improve your performance.

Usage

        pool_drty_pg_steal_clns
     / (pool_drty_pg_steal_clns
      + pool_drty_pg_thrsh_clns
      + pool_lsn_gap_clns)

If this ratio is low, it may indicate that you have defined too many page cleaners. If your chngpgs_thresh configuration parameter is set too low, you may be writing out pages that you will dirty later. Aggressive cleaning defeats one purpose of the buffer pool, that is to defer writing to the last possible moment.

If this ratio is high, it may indicate that you have not defined enough page cleaners. Not having enough page cleaners increases recovery time after failures.

Usage

   1 - 
    (
    POOL_FAILED_ASYNC_DATA_REQS + 
    POOL_FAILED_ASYNC_INDEX_REQS + 
    POOL_FAILED_ASYNC_XDA_REQS + 
    POOL_FAILED_ASYNC_COL_REQS + 
    POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
    POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
    POOL_FAILED_ASYNC_TEMP_XDA_REQS +
    POOL_FAILED_ASYNC_TEMP_COL_REQS 
    ) 
    ÷ 
    (
      (
      POOL_FAILED_ASYNC_DATA_REQS + 
      POOL_FAILED_ASYNC_INDEX_REQS + 
      POOL_FAILED_ASYNC_XDA_REQS + 
      POOL_FAILED_ASYNC_COL_REQS + 
      POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
      POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
      POOL_FAILED_ASYNC_TEMP_XDA_REQS +
      POOL_FAILED_ASYNC_TEMP_COL_REQS 
      )
    + 
      (
      POOL_QUEUED_ASYNC_DATA_REQS + 
      POOL_QUEUED_ASYNC_INDEX_REQS + 
      POOL_QUEUED_ASYNC_XDA_REQS + 
      POOL_QUEUED_ASYNC_COL_REQS + 
      POOL_QUEUED_ASYNC_TEMP_DATA_REQS + 
      POOL_QUEUED_ASYNC_TEMP_INDEX_REQS + 
      POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
      POOL_QUEUED_ASYNC_TEMP_COL_REQS 
      )
    ) × 100

Usage

   1 - 
    (
    POOL_FAILED_ASYNC_DATA_REQS + 
    POOL_FAILED_ASYNC_INDEX_REQS + 
    POOL_FAILED_ASYNC_XDA_REQS + 
    POOL_FAILED_ASYNC_COL_REQS + 
    POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
    POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
    POOL_FAILED_ASYNC_TEMP_XDA_REQS +
    POOL_FAILED_ASYNC_TEMP_COL_REQS 
    ) 
    ÷ 
    (
      (
      POOL_FAILED_ASYNC_DATA_REQS + 
      POOL_FAILED_ASYNC_INDEX_REQS + 
      POOL_FAILED_ASYNC_XDA_REQS + 
      POOL_FAILED_ASYNC_COL_REQS + 
      POOL_FAILED_ASYNC_TEMP_DATA_REQS + 
      POOL_FAILED_ASYNC_TEMP_INDEX_REQS + 
      POOL_FAILED_ASYNC_TEMP_XDA_REQS +
      POOL_FAILED_ASYNC_TEMP_COL_REQS 
      )
    + 
      (
      POOL_QUEUED_ASYNC_DATA_REQS + 
      POOL_QUEUED_ASYNC_INDEX_REQS + 
      POOL_QUEUED_ASYNC_XDA_REQS + 
      POOL_QUEUED_ASYNC_COL_REQS + 
      POOL_QUEUED_ASYNC_TEMP_DATA_REQS + 
      POOL_QUEUED_ASYNC_TEMP_INDEX_REQS + 
      POOL_QUEUED_ASYNC_TEMP_XDA_REQS +
      POOL_QUEUED_ASYNC_TEMP_COL_REQS 
      )
    ) × 100