Skip to main contentIBM dmctop Wiki

IO / Table Spaces

Purpose

Table spaces view shows the performance of I/O operations for each table space including hit ratios and number of pages read and written for all buffer pools and for various data types and database usage.

Screenshot

image

Metric shown

Gauge

Data hit%

Data page hit ratio. Formulas for calculating buffer pool hit ratios

= DATA_HIT_RATIO_NUMER / DATA_HIT_RATIO_DENOM

Source:

DATA_HIT_RATIO_NUMER = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_LBP_PAGES_FOUND - POOL_ASYNC_DATA_LBP_PAGES_FOUND)

DATA_HIT_RATIO_DENOM = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_L_READS + POOL_TEMP_DATA_L_READS)

Idx hit%

Index page hit ratio. Formulas for calculating buffer pool hit ratios

= IDX_HIT_RATIO_NUMER / IDX_HIT_RATIO_NUMER

Source:

IDX_HIT_RATIO_NUMER = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_INDEX_LBP_PAGES_FOUND - POOL_ASYNC_INDEX_LBP_PAGES_FOUND)

IDX_HIT_RATIO_DENOM = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_INDEX_L_READS + POOL_TEMP_INDEX_L_READS)

Col hit%

Column-organized page hit ratio. Formulas for calculating buffer pool hit ratios

= COL_HIT_NUMERATOR / COL_HIT_DENOMINATOR

Source:

COL_HIT_NUMERATOR = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_COL_LBP_PAGES_FOUND - POOL_ASYNC_COL_LBP_PAGES_FOUND)

COL_HIT_DENOMINATOR = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_COL_L_READS + POOL_TEMP_COL_L_READS)

Temp hit%

Page hit ratio in temporary table spaces.

= TEMP_HIT_RATIO_NUMER / TEMP_HIT_RATIO_DENOM

Source:

TEMP_HIT_RATIO_NUMER = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(SUM(POOL_TEMP_DATA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_COL_L_READS))-(SUM(POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS + POOL_TEMP_XDA_P_READS + POOL_TEMP_COL_P_READS))

TEMP_HIT_RATIO_DENOM = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_TEMP_DATA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_COL_L_READS)

XML hit%

XML storage object (XDA) page hit ratio. Formulas for calculating buffer pool hit ratios

= XDA_HIT_NUMERATOR / XDA_HIT_DENOMINATOR

Source:

XDA_HIT_NUMERATOR = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_XDA_LBP_PAGES_FOUND - POOL_ASYNC_XDA_LBP_PAGES_FOUND)

XDA_HIT_DENOMINATOR = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_XDA_L_READS + POOL_TEMP_XDA_L_READS)

All hit ratio%

Overall hit ratio. Formulas for calculating buffer pool hit ratios

= ALL_HIT_RATIO_NUMER / POOL_L_READS

Source:

ALL_HIT_RATIO_NUMER = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_LBP_PAGES_FOUND + POOL_INDEX_LBP_PAGES_FOUND + POOL_XDA_LBP_PAGES_FOUND + POOL_COL_LBP_PAGES_FOUND - POOL_ASYNC_DATA_LBP_PAGES_FOUND - POOL_ASYNC_INDEX_LBP_PAGES_FOUND - POOL_ASYNC_XDA_LBP_PAGES_FOUND - POOL_ASYNC_COL_LBP_PAGES_FOUND)

POOL_L_READS = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_L_READS + POOL_INDEX_L_READS + POOL_XDA_L_READS + POOL_COL_L_READS + POOL_TEMP_DATA_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_COL_L_READS)

Avg hit ratio%

= Average overall hit ratio. Formulas for calculating buffer pool hit ratios

= AVG_HIT_RATIO_NUMER / AVG_HIT_RATIO_DENOM

Source:

AVG_HIT_RATIO_NUMER = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM((POOL_DATA_LBP_PAGES_FOUND + POOL_INDEX_LBP_PAGES_FOUND + POOL_XDA_LBP_PAGES_FOUND + POOL_COL_LBP_PAGES_FOUND - POOL_ASYNC_DATA_LBP_PAGES_FOUND - POOL_ASYNC_INDEX_LBP_PAGES_FOUND - POOL_ASYNC_XDA_LBP_PAGES_FOUND - POOL_ASYNC_COL_LBP_PAGES_FOUND) / (POOL_DATA_L_READS + POOL_INDEX_L_READS + POOL_XDA_L_READS + POOL_COL_L_READS + POOL_TEMP_DATA_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_COL_L_READS))

AVG_HIT_RATIO_DENOM = 1

Grid

IO type

The ratio of different type of page read and write.

= <IO_TYPE_R / IO_TOTAL>r | <IO_TYPE_W / IO_TOTAL >w | <IO_TYPE_D / IO_TOTAL >d | <IO_TYPE_I / IO_TOTAL >i | <IO_TYPE_T / IO_TOTAL >t IO_TOTAL = IO_TYPE_R + IO_TYPE_W + IO_TYPE_D + IO_TYPE_I + IO_TYPE_T

Source:

IO_TYPE_R = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_P_READS + POOL_INDEX_P_READS + POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS + POOL_XDA_P_READS + POOL_TEMP_XDA_P_READS + POOL_COL_P_READS + POOL_TEMP_COL_P_READS + DIRECT_READS)

IO_TYPE_W = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_WRITES + POOL_INDEX_WRITES + POOL_XDA_WRITES + POOL_COL_WRITES + DIRECT_WRITES)

IO_TYPE_D = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_L_READS)

IO_TYPE_I = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_INDEX_L_READS)

IO_TYPE_T = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_TEMP_DATA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_COL_L_READS)

DB Size/Capacity

The percentage of database usage.

= <DB_SIZE> / <DB_CAPACITY>

Source:

DB_SIZE = SYSTOOLS.STMG_DBSIZE_INFO.DB_SIZE

DB_CAPACITY = SYSTOOLS.STMG_DBSIZE_INFO.DB_CAPACITY

Abnormal

The number of abnormal tablespace.

SELECT COUNT(*) AS ABNORMAL_TOTAL
FROM   TABLE(MON_GET_TABLESPACE(NULL,#MEMBER#))
WHERE  TBSP_STATE <> 'NORMAL'

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).TBSP_STATE

Temp space used

The total space page size that are currently used in table spaces.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(TBSP_USED_PAGES * TBSP_PAGE_SIZE)

Logical reads

The number of pages read from the local buffer pool (logical).

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_L_READS + POOL_INDEX_L_READS + POOL_XDA_L_READS + POOL_COL_L_READS + POOL_TEMP_DATA_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_COL_L_READS)

Physical reads

The number of pages read in from the table space containers (physical).

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_P_READS + POOL_INDEX_P_READS + POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS + POOL_XDA_P_READS + POOL_TEMP_XDA_P_READS + POOL_COL_P_READS + POOL_TEMP_COL_P_READS)

Write

The total number of write operations.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_WRITES + POOL_INDEX_WRITES + POOL_XDA_WRITES + POOL_COL_WRITES + DIRECT_WRITES)

Table

Tablespace name

The name of a table space.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).TBSP_NAME

Logical reads

The number of pages read from the local buffer pool (logical).

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_L_READS + POOL_INDEX_L_READS + POOL_XDA_L_READS + POOL_COL_L_READS + POOL_TEMP_DATA_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_COL_L_READS)

Physical reads

The number of pages read in from the table space containers (physical).

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_P_READS + POOL_INDEX_P_READS + POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS + POOL_XDA_P_READS + POOL_TEMP_XDA_P_READS + POOL_COL_P_READS + POOL_TEMP_COL_P_READS)

Hit ratio%

Overall hit ratio. Formulas for calculating buffer pool hit ratios

= ALL_HIT_RATIO_NUMER / POOL_L_READS

Source:

ALL_HIT_RATIO_NUMER = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_LBP_PAGES_FOUND + POOL_INDEX_LBP_PAGES_FOUND + POOL_XDA_LBP_PAGES_FOUND + POOL_COL_LBP_PAGES_FOUND - POOL_ASYNC_DATA_LBP_PAGES_FOUND - POOL_ASYNC_INDEX_LBP_PAGES_FOUND - POOL_ASYNC_XDA_LBP_PAGES_FOUND - POOL_ASYNC_COL_LBP_PAGES_FOUND)

POOL_L_READS = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_L_READS + POOL_INDEX_L_READS + POOL_XDA_L_READS + POOL_COL_L_READS + POOL_TEMP_DATA_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_COL_L_READS)

Async reads%

The percentage of pages which have been asynchronously read from the table space containers (physical).

= ASYNC_READS / POOL_L_READS

Source:

ASYNC_READS = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_ASYNC_DATA_READS + POOL_ASYNC_INDEX_READS + POOL_ASYNC_XDA_READS + POOL_ASYNC_COL_READS)

POOL_L_READS = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_L_READS + POOL_INDEX_L_READS + POOL_XDA_L_READS + POOL_COL_L_READS + POOL_TEMP_DATA_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_COL_L_READS)

Pages aread

The average number of asynchronous read per request.

= ASYNC_READS / ASYNC_READ_REQ

Source:

ASYNC_READS = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_ASYNC_DATA_READS + POOL_ASYNC_INDEX_READS + POOL_ASYNC_XDA_READS + POOL_ASYNC_COL_READS)

ASYNC_READ_REQ = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_ASYNC_DATA_READ_REQS + POOL_ASYNC_INDEX_READ_REQS + POOL_ASYNC_XDA_READ_REQS + POOL_ASYNC_COL_READ_REQS)

Writes

The total number of write operations.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_WRITES + POOL_INDEX_WRITES + POOL_XDA_WRITES + POOL_COL_WRITES + DIRECT_WRITES)

Async reads

The total number of pages which have been asynchronously read from the table space containers (physical).

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_ASYNC_DATA_READS + POOL_ASYNC_INDEX_READS + POOL_ASYNC_XDA_READS + POOL_ASYNC_COL_READS)

Async writes

The number of times a page was physically written to disk by either an asynchronous page cleaner, or a prefetcher.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_ASYNC_DATA_WRITES + POOL_ASYNC_INDEX_WRITES + POOL_ASYNC_XDA_WRITES + POOL_ASYNC_COL_WRITES)

Direct writes

The number of write operations that do not use the buffer pool.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(DIRECT_WRITES)

Data writes

The number of times a buffer pool data page was physically written to disk.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_WRITES)

Index writes

The number of times a buffer pool index page was physically written to disk.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_INDEX_WRITES)

Avg_reads dir_reqs

The average number of read operations that do not use the buffer pool per request.

= DIRECT_READS / DIRECT_READ_REQS

Source:

DIRECT_READS = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(DIRECT_READS)

DIRECT_READ_REQS = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(DIRECT_READ_REQS)

Avg rdtime

The average amount of time spent for read operations.

= READ_TIME_MS / TOTAL_READS

Source:

READ_TIME_MS = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_READ_TIME + DIRECT_READ_TIME)

TOTAL_READS = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_P_READS + POOL_INDEX_P_READS + POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS + DIRECT_READS)

Avg wrtime

The average amount of time spent for write operations.

= WRITE_TIME_MS / TOTAL_WRITES

Source:

WRITE_TIME_MS = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_WRITE_TIME + DIRECT_WRITE_TIME)

TOTAL_WRITES = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(DIRECT_WRITES + POOL_DATA_WRITES + POOL_INDEX_WRITES)

Tbsp type

Table space content type. A text identifier based on defines in sqlmon.h, and is one of:

  • ANY
  • LARGE
  • SYSTEMP
  • USRTEMP

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).TBSP_CONTENT_TYPE)

# of dbp

The number of buffer pool that is being monitored.

SELECT COUNT(*) AS NUM_DBP
FROM TABLE(
  MON_GET_TABLESPACE(
    NULL, 
    #MEMBER#)) AS MTSP
LEFT OUTER JOIN SYSCAT.BUFFERPOOLS AS BP ON MTSP.TBSP_CUR_POOL_ID = BP.BUFFERPOOLID 
LEFT OUTER JOIN TABLE(
      MON_GET_BUFFERPOOL(
        NULL,

Avg prefsize

The average size of pages which have been asynchronously read from the table space containers (physical) per request.

= READ_BYTES / ASYNC_READ_REQ

Source:

READ_BYTES = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(TBSP_PAGE_SIZE * (POOL_ASYNC_DATA_READS + POOL_ASYNC_INDEX_READS + POOL_ASYNC_XDA_READS + POOL_ASYNC_COL_READS))

ASYNC_READ_REQ = MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_ASYNC_DATA_READ_REQS + POOL_ASYNC_INDEX_READ_REQS + POOL_ASYNC_XDA_READ_REQS + POOL_ASYNC_COL_READ_REQS)

I/o skew

The I/O skew from average.

= 1 - AVG(POOL_L_READS)/MAX(POOL_L_READS)

Source:

POOL_L_READS = MON_GET_TABLESPACE(NULL,#MEMBER#).(POOL_DATA_L_READS + POOL_INDEX_L_READS + POOL_XDA_L_READS + POOL_COL_L_READS + POOL_TEMP_DATA_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_COL_L_READS)

Tbspace status

The current state of a table space.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).TBSP_STATE

Tbspace type

Table space type. A text identifier based on defines in sqlutil.h, and is one of:

  • DMS
  • SMS

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).TBSP_TYPE

Fsc on

Indicate whether file system caching is intended for a table space.

If MAX(FS_CACHING) = 0, then ‘YES’, else ‘NO’

Source:

FS_CACHING = MON_GET_TABLESPACE(NULL,#MEMBER#).FS_CACHING

File closed

The total number of database files closed.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(FILES_CLOSED)

Victim buffers

The number of times an agent did not have a preselected victim buffer available.

Source: MON_GET_BUFFERPOOL(NULL, #MEMBER#).SUM(POOL_NO_VICTIM_BUFFER)

Unread prefpages

The number of pages that the prefetcher read into the bufferpool that were never used.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(UNREAD_PREFETCH_PAGES)

Xda l_reads

The number of pages for XML storage objects (XDAs) read from the buffer pool (logical).

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_XDA_L_READS + (POOL_TEMP_XDA_L_READS)

Xda p_reads

The number of pages for XML storage objects (XDAs) which have read from the table space containers (physical).

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_XDA_P_READS + POOL_TEMP_XDA_P_READS)

Xda l_writes

The number of times a buffer pool data page for an XML storage object (XDA) was physically written to disk.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_XDA_WRITES)

Prefetch size

The total page size the prefetcher gets from the disk at a time.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(TBSP_PAGE_SIZE * TBSP_PREFETCH_SIZE)

Io

The total number of IO operations.

Source: MON_GET_TABLESPACE(NULL,#MEMBER#).SUM(POOL_DATA_WRITES + POOL_INDEX_WRITES + POOL_XDA_WRITES + POOL_COL_WRITES + DIRECT_WRITES + POOL_DATA_L_READS + POOL_INDEX_L_READS + POOL_TEMP_DATA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_XDA_L_READS + POOL_TEMP_XDA_L_READS + POOL_TEMP_COL_L_READS + POOL_COL_L_READS)

Previous
IO: Prefetchers
Next
IO: Table