System Monitor Guide and Reference

Database Configuration

The following elements provide information particularly helpful for database performance tuning.

Buffer Pool Activity

The database server reads and updates all data from a buffer pool. Data is copied from disk to a buffer pool as it is required by applications.

Pages are placed in a buffer pool:

by the agent. This is synchronous I/O.
by the I/O servers (prefetchers). This is asynchronous I/O.

Pages are written to disk from a buffer pool:

by the agent, synchronously
by page cleaners, asynchronously

If the server needs to read a page of data, and that page is already in the buffer pool, then the ability to access that page is much faster than if the page had to be read from disk. It is desirable to hit as many pages as possible in the buffer pool. Avoiding disk I/O is the main issue when trying to improve the performance of your server And so, proper configuration of the buffer pools are probably the most important consideration for performance tuning.

Buffer Pool Hit Ratio

The buffer pool hit ratio indicates the percentage of time that the database manager did not need to load a page from disk in order to service a page request. That is, the page was already in the buffer pool. The greater the buffer pool hit ratio, the lower the frequency of disk I/O.

The buffer pool hit ratio can be calculated as follows:

     (1 - ((pool_data_p_reads + pool_index_p_reads) /
       (pool_data_l_reads + pool_index_l_reads))) * 100%

This calculation takes into account all of the pages (index and data) that are cached by the buffer pool.

For a large database, increasing the buffer pool size may have minimal effect on the buffer pool hit ratio. Its number of data pages may be so large, that the statistical chances of a hit are not improved increasing its size. But you might find that tuning the index buffer pool hit ratio achieves the desired result. This can be achieved using two methods:

Split the data and indices into two different buffer pools and tune them separately.
Use one bufferpool, but increase its size until the index hit ratio stops increasing. The index buffer pool hit ratio can be calculated as follows:
```
      (1 - ((pool_index_p_reads) / (pool_index_l_reads))) * 100%
```

The first method is often more effective, but because it requires indices and data to reside in different tablespaces, it may not be an option for existing databases. It also requires tuning two bufferpools instead of one, which can be a more difficult task, particularly when memory is constrained.

Prefetchers

You should also consider the impact that prefetchers may be having on the hit ratio. Prefetchers read data pages into the buffer pool anticipating their need by an application (asynchronously). In most situations, these pages are read just before they are needed (the desired case). However, prefetchers can cause unnecessary I/O by reading pages into the buffer pool that will not be used. For example, an application starts reading through a table. This is detected and prefetching starts, but the application fills an application buffer and stops reading. Meanwhile, prefetching has been done for a number of additional pages. I/O has occurred for pages that will not be used and the buffer pool is partially taken up with those pages.

Page Cleaners

Page cleaners monitor the buffer pool and asynchronously write pages to disk. Their goals are:

Ensure that agents will always find free pages in the buffer pool. If an agent does not find free pages in the buffer pool, it must clean them itself, and the associated application will have a poorer response.
Speed database recovery, if a system crash occurs. The more pages that have been written to disk, the smaller the number of log file records that must be processed to recover the database.

Although dirty pages are written out to disk, the pages are not removed from the buffer pool right away, unless the space is needed to read in new pages.
Note: Buffer pool information is typically gathered at a table space level, but the facilities of the database system monitor can roll this information up to the buffer pool and database levels. Depending on your type of analysis, you may need to examine this data at any or all of these levels.

The following elements provide information about buffer pool activity. For an overview how the database manager uses buffer pools, see the Administration Guide.

Buffer Pool Data Logical Reads
Buffer Pool Data Physical Reads
Buffer Pool Data Writes
Buffer Pool Index Logical Reads
Buffer Pool Index Physical Reads
Buffer Pool Index Writes
Total Buffer Pool Physical Read Time
Total Buffer Pool Physical Write Time
Database Files Closed
Buffer Pool Asynchronous Data Reads
Buffer Pool Asynchronous Data Writes
Buffer Pool Asynchronous Index Writes
Buffer Pool Asynchronous Index Reads
Buffer Pool Asynchronous Read Time
Buffer Pool Asynchronous Write Time
Buffer Pool Asynchronous Read Requests
Buffer Pool Log Space Cleaners Triggered
Buffer Pool Victim Page Cleaners Triggered
Buffer Pool Threshold Cleaners Triggered
Buffer Pool Information
Bufferpool Name
Time Waited for Prefetch

Buffer Pool Data Logical Reads

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space Connection	Logical Data Grouping db_event tablespace_event conn_event
Element Name Element Type	pool_data_l_reads counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Database Activation Timestamp" "Connection Request Start Timestamp" "Buffer Pool Data Physical Reads" "Buffer Pool Data Writes" "Buffer Pool Index Logical Reads" "Buffer Pool Index Physical Reads"

Description: Indicates the number of logical read requests for data pages that have gone through the buffer pool.

Usage: This count includes accesses to data that is:

Already in the buffer pool when the database manager needs to process the page
Read into the buffer pool before the database manager can process the page.

In conjunction with "Buffer Pool Data Physical Reads", you can calculate the data page hit ratio for the buffer pool using the following formula:

 
 1 - (buffer pool data physical reads / buffer pool data logical reads)

In conjunction with "Buffer Pool Data Physical Reads", "Buffer Pool Index Physical Reads", and "Buffer Pool Index Logical Reads", you can calculate the overall buffer pool hit ratio using the following formula:

 
 1 - ((buffer pool data physical reads + buffer pool index physical reads)
     / (buffer pool data logical reads + buffer pool index logical 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. In such case, you would focus your attention on smaller, frequently accessed tables, and on the indices. Perhaps, assigning them to an individual buffer pools, for which you can aim for higher hit ratios.

Buffer Pool Data Physical Reads

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space Connection	Logical Data Grouping db_event tablespace_event conn_event
Element Name Element Type	pool_data_p_reads counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Database Activation Timestamp" "Connection Request Start Timestamp" "Buffer Pool Data Logical Reads" "Buffer Pool Data Writes" "Buffer Pool Index Logical Reads" "Buffer Pool Index Physical Reads" "Buffer Pool Asynchronous Data Reads"

Description: The number of read requests that required I/O to get data pages into the buffer pool.

Usage: See "Buffer Pool Data Logical Reads" and "Buffer Pool Asynchronous Data Reads" for information about how to use this element.

Buffer Pool Data Writes

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space Connection	Logical Data Grouping db_event tablespace_event conn_event
Element Name Element Type	pool_data_writes counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Database Activation Timestamp" "Connection Request Start Timestamp" "Buffer Pool Data Logical Reads" "Buffer Pool Data Physical Reads" "Total Buffer Pool Physical Write Time" "Buffer Pool Asynchronous Data Writes"

Description: Indicates the number of times a buffer pool data page was physically written to disk.

Usage: If a buffer pool data page is written to disk for a high percentage of the "Buffer Pool Data Physical Reads", you may be able to improve performance by increasing the number of buffer pool pages available for the database.

A buffer pool data page is written to disk for the following reasons:

To free a page in the buffer pool so another page can be read
To flush the buffer pool.

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. These asynchronous page writes are included in the value of this element in addition to synchronous page writes (see "Buffer Pool Asynchronous Data Writes").

When calculating this percentage, disregard the number of physical reads required to initially fill the buffer pool. To determine the number of pages written:

Run your application (to load the buffer)
Note the value of this element
Run your application again
Subtract the value recorded in step 2 from the new value of this element.

In order to prevent the buffer pool from being deallocated between the runnings of your application, you should either;

activate the database with the ACTIVATE DATABASE command
have an idle application connected to the database.

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.

See the Administration Guide for more information about buffer pool size.

Buffer Pool Index Logical Reads

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space Connection	Logical Data Grouping db_event tablespace_event conn_event
Element Name Element Type	pool_index_l_reads counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Database Activation Timestamp" "Connection Request Start Timestamp" "Buffer Pool Index Physical Reads" "Buffer Pool Index Writes" "Buffer Pool Data Physical Reads" "Buffer Pool Data Writes" "Buffer Pool Data Logical Reads"

Description: Indicates the number of logical read requests for index pages that have gone through the buffer pool.

Usage: This count includes accesses to index pages that are:

Already in the buffer pool when the database manager needs to process the page
Read into the buffer pool before the database manager can process the page.

In conjunction with "Buffer Pool Index Physical Reads", you can calculate the index page hit ratio for the buffer pool using one of the following:

 
 1 - (buffer pool index physical reads / buffer pool index logical reads)

To calculate the overall buffer pool hit ratio, see "Buffer Pool Data Logical Reads".

If the hit ratio is low, increasing the number of buffer pool pages may improve performance. See the Administration Guide for more information about buffer pool size.

Buffer Pool Index Physical Reads

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space Connection	Logical Data Grouping db_event tablespace_event conn_event
Element Name Element Type	pool_index_p_reads counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Database Activation Timestamp" "Connection Request Start Timestamp" "Buffer Pool Index Logical Reads" "Buffer Pool Index Writes" "Buffer Pool Data Logical Reads" "Buffer Pool Data Physical Reads"

Description: Indicates the number of physical read requests to get index pages into the buffer pool.

Usage: See "Buffer Pool Index Logical Reads" for information about how to use this element.

Buffer Pool Index Writes

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space Connection	Logical Data Grouping db_event tablespace_event conn_event
Element Name Element Type	pool_index_writes counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Database Activation Timestamp" "Connection Request Start Timestamp" "Buffer Pool Index Logical Reads" "Buffer Pool Index Physical Reads" "Buffer Pool Asynchronous Index Writes"

Description: Indicates the number of times a buffer pool index page was physically written to disk.

Usage: Like a data page, a buffer pool index page is written to disk for the following reasons:

To free a page in the buffer pool so another page can be read
To flush the buffer pool.

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 index page can be written by an asynchronous page-cleaner agent before the buffer pool space is required. These asynchronous index page writes are included in the value of this element in addition to synchronous index page writes (see "Buffer Pool Asynchronous Index Writes").

If a buffer pool index page is written to disk for a high percentage of the "Buffer Pool Index Physical Reads", you may be able to improve performance by increasing the number of buffer pool pages available for the database.

When calculating this percentage, disregard the number of physical reads required to initially fill the buffer pool. To determine the number of pages written:

Run your application (to load the buffer)
Note the value of this element
Run your application again
Subtract the value recorded in step 2 from the new value of this element.

In order to prevent the buffer pool from being deallocated between the runnings of your application, you should either:

activate the database with the ACTIVATE DATABASE command
have an idle application connected to the database.

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 pages contain updated data which must be written to disk.

See the Administration Guide for more information about buffer pool size.

Total Buffer Pool Physical Read Time

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space Connection	Logical Data Grouping db_event tablespace_event conn_event
Element Name Element Type	pool_read_time counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Buffer Pool Data Physical Reads" "Buffer Pool Index Physical Reads" "Database Activation Timestamp" "Connection Request Start Timestamp" "Buffer Pool Asynchronous Read Time"

Description: Provides the total amount of elapsed time spent processing read requests that caused data or index pages to be physically read from disk to buffer pool.

Usage: You can use this element with "Buffer Pool Data Physical Reads" and "Buffer Pool Index Physical Reads" to calculate the average page-read time. This average is important since it may indicate the presence of an I/O wait, which in turn may indicate that you should be moving data to a different device.

At the database and table space levels, this element includes the value of "Buffer Pool Asynchronous Read Time".

Total Buffer Pool Physical Write Time

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Poo Buffer Pooll Buffer Pool
Resettable	Yes
Event Type Database Table Space Connection	Logical Data Grouping db_event tablespace_event conn_event
Element Name Element Type	pool_write_time counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Buffer Pool Data Writes" "Buffer Pool Index Writes" "Database Activation Timestamp" "Connection Request Start Timestamp"

Description: Provides the total amount of time spent physically writing data or index pages from the buffer pool to disk.

Usage: You can use this element with "Buffer Pool Data Writes" and "Buffer Pool Index Writes" to calculate the average page-write time. This average is important since it may indicate the presence of an I/O wait, which in turn may indicate that you should be moving data to a different device.

At the database and table space levels, this element includes the value of "Buffer Pool Asynchronous Write Time".

Database Files Closed

Snapshot Level Database Table Space	Logical Data Grouping dbase tablespace bp_info	Monitor Switch Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space	Logical Data Grouping db_event tablespace_event
Element Name Element Type	files_closed counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized"

Description: The total number of database files closed.

Usage: The database manager opens files for reading and writing into and out of the buffer pool. The maximum number of database files open by an application at any time is controlled by the maxfilop configuration parameter. If the maximum is reached, one file will be closed before the new file is opened. Note that the actual number of files opened may not equal the number of files closed.

You can use this element to help you determine the best value for the maxfilop configuration parameter (see the Administration Guide for more information).

Buffer Pool Asynchronous Data Reads

Snapshot Level Database Table Space	Logical Data Grouping dbase tablespace bp_info	Monitor Switch Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space	Logical Data Grouping db_event tablespace_event
Element Name Element Type	pool_async_data_reads counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Buffer Pool Asynchronous Read Time" "Buffer Pool Data Physical Reads" "Buffer Pool Asynchronous Read Requests" "Direct Reads From Database"

Description: The number of pages read asynchronously into the buffer pool.

Usage: You can use this element with "Buffer Pool Data Physical Reads" to calculate the number of physical reads that were performed synchronously (that is, physical data page reads that were performed by database manager agents). Use the following formula:

 
 buffer pool data physical reads - buffer pool asynchronous data 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 (see the Administration Guide).

Asynchronous reads are performed by database manager prefetchers. For information about these prefetchers, see the Administration Guide.

Buffer Pool Asynchronous Data Writes

Snapshot Level Database Table Space	Logical Data Grouping dbase tablespace bp_info	Monitor Switch Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space	Logical Data Grouping db_event tablespace_event
Element Name Element Type	pool_async_data_writes counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Buffer Pool Asynchronous Index Writes" "Buffer Pool Data Writes" "Buffer Pool Asynchronous Write Time" "Buffer Pool Log Space Cleaners Triggered" "Buffer Pool Victim Page Cleaners Triggered" "Buffer Pool Threshold Cleaners Triggered" "Direct Writes to Database"

Description: The number of times a buffer pool data page was physically written to disk by either an asynchronous page cleaner, or a prefetcher. A prefetcher may have written dirty pages to disk to make space for the pages being prefetched.

Usage: You can use this element with "Buffer Pool Data Writes" to calculate the number of physical write requests that were performed synchronously (that is, physical data page writes that were performed by database manager agents). Use the following formula:

  buffer pool data writes - buffer pool asynchronous data 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.

For more information about asynchronous page cleaners, see the Administration Guide.

Buffer Pool Asynchronous Index Writes

Snapshot Level Database Table Space	Logical Data Grouping dbase tablespace bp_info	Monitor Switch Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space	Logical Data Grouping db_event tablespace_event
Element Name Element Type	pool_async_index_writes counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Buffer Pool Asynchronous Data Writes" "Buffer Pool Asynchronous Index Reads" "Buffer Pool Index Writes" "Buffer Pool Asynchronous Write Time" "Buffer Pool Log Space Cleaners Triggered" "Buffer Pool Victim Page Cleaners Triggered" "Buffer Pool Threshold Cleaners Triggered" "Direct Writes to Database"

Description: The number of times a buffer pool index page was physically written to disk by either an asynchronous page cleaner, or a prefetcher. A prefetcher may have written dirty pages to disk to make space for the pages being prefetched.

Usage: You can use this element with "Buffer Pool Index Writes" to calculate the number of physical index write requests that were performed synchronously. That is, physical index page writes that were performed by database manager agents. Use the following formula:

  buffer pool index writes - buffer pool asynchronous index writes

For more information about asynchronous page cleaners, see the Administration Guide.

Buffer Pool Asynchronous Index Reads

Snapshot Level Database Table Space	Logical Data Grouping dbase tablespace bp_info	Monitor Switch Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space	Logical Data Grouping db_event tablespace_event
Element Name Element Type	pool_async_index_reads counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Buffer Pool Asynchronous Data Writes" "Buffer Pool Asynchronous Index Writes" "Buffer Pool Index Physical Reads" "Buffer Pool Asynchronous Read Time" "Buffer Pool Log Space Cleaners Triggered" "Buffer Pool Victim Page Cleaners Triggered" "Buffer Pool Threshold Cleaners Triggered" "Direct Reads From Database"

Description: The number of index pages read asynchronously into the buffer pool by a prefetcher.

Usage: You can use this element with "Buffer Pool Index Physical Reads" to calculate the number of physical reads that were performed synchronously (that is, physical index page reads that were performed by database manager agents). Use the following formula:

 
 buffer pool index physical reads - buffer pool asynchronous index reads

Asynchronous reads are performed by database manager prefetchers. For information about these prefetchers, see the Administration Guide.

Buffer Pool Asynchronous Read Time

Snapshot Level Database Table Space	Logical Data Grouping dbase tablespace bp_info	Monitor Switch Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space	Logical Data Grouping db_event tablespace_event
Element Name Element Type	pool_async_read_time counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Buffer Pool Asynchronous Data Reads" "Total Buffer Pool Physical Read Time" "Buffer Pool Asynchronous Read Requests" "Direct Read Time"

Description: The total elapsed time spent reading by database manager prefetchers.

Usage: You can use this element to calculate the elapsed time for synchronous reading, using the following formula:

  total buffer pool physical read time - buffer pool asynchronous read time

You can also use this element to calculate the average asynchronous read time using the following formula:

  buffer pool asynchronous read time / buffer pool asynchronous data reads

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

Buffer Pool Asynchronous Write Time

Snapshot Level Database Table Space	Logical Data Grouping dbase tablespace bp_info	Monitor Switch Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space	Logical Data Grouping db_event tablespace_event
Element Name Element Type	pool_async_write_time counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Buffer Pool Asynchronous Data Writes" "Buffer Pool Asynchronous Index Writes" "Total Buffer Pool Physical Write Time" "Buffer Pool Asynchronous Read Requests" "Direct Write Time"

Description: The total elapsed time spent writing data or index pages from the buffer pool to disk by database manager page cleaners.

Usage: To calculate the elapsed time spent writing pages synchronously, use the following formula:

 total buffer pool physical write time - buffer pool asynchronous write time

You can also use this element to calculate the average asynchronous read time using the following formula:

 buffer pool asynchronous write time
  / (buffer pool asynchronous data writes 
    + buffer pool asynchronous index writes)

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

Buffer Pool Asynchronous Read Requests

Snapshot Level Database Table Space	Logical Data Grouping dbase tablespace bp_info	Monitor Switch Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Table Space	Logical Data Grouping db_event tablespace_event
Element Name Element Type	pool_async_data_read_reqs counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Buffer Pool Asynchronous Data Reads"

Description: The number of asynchronous read requests.

Usage: To calculate the average number of data pages read per asynchronous request, use the following formula:

 buffer pool asynchronous data reads / buffer pool asynchronous read requests

This average can help you determine the amount of asynchronous I/O done in each interaction with the prefetcher.

Buffer Pool Log Space Cleaners Triggered

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Buffer Pool
Resettable	Yes
Event Type Database	Logical Data Grouping db_event
Element Name Element Type	pool_lsn_gap_clns counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Buffer Pool Victim Page Cleaners Triggered" "Buffer Pool Threshold Cleaners Triggered"

Description: The number of times a page cleaner was invoked because the logging space used had reached a predefined criterion for the database.

Usage: This element can be used to help evaluate whether you have enough space for logging, and whether you need more log files or larger log files.

The page cleaning criterion is determined by the setting for the softmax configuration parameter. Page cleaners are triggered if the oldest page in the buffer pool contains an update described by a log record that is older than the current log position by the criterion value. See the Administration Guide for more information.

Buffer Pool Victim Page Cleaners Triggered

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Buffer Pool
Resettable	Yes
Event Type Database	Logical Data Grouping db_event
Element Name Element Type	pool_drty_pg_steal_clns counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Buffer Pool Log Space Cleaners Triggered" "Buffer Pool Threshold Cleaners Triggered"

Description: The number of times a page cleaner was invoked because a synchronous write was needed during the victim buffer replacement for the database.

Usage: Using the following formula, you may calculate what percentage of all cleaner invocations are represented by this element:

       buffer pool victim page cleaners triggered
     / ( buffer pool victim page cleaners triggered
       + buffer pool threshold cleaners triggered
       + buffer pool log space cleaners triggered)

If this ratio is low, it may indicate that you have defined too many page cleaners. If your chngpgs_thresh 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 too few page cleaners defined. Too few page cleaners will increase recovery time after failures (see the Administration Guide).
Note: Although dirty pages are written out to disk, the pages are not removed from the buffer pool right away, unless the space is needed to read in new pages.

Buffer Pool Threshold Cleaners Triggered

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Buffer Pool
Resettable	Yes
Event Type Database	Logical Data Grouping db_event
Element Name Element Type	pool_drty_pg_thrsh_clns counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Buffer Pool Log Space Cleaners Triggered"

Description: The number of times a page cleaner was invoked because a buffer pool had reached the dirty page threshold criterion for the database.

Usage: The threshold is set by the chngpgs_thresh configuration parameter. It is a percentage applied to the buffer pool size. When the number of dirty pages in the pool exceeds this value, the cleaners are triggered.

If this value is set too low, pages might be written out too early, requiring them to be read back in. If set too high, then too many pages may accumulate, requiring users to write out pages synchronously. See the Administration Guide for more information.

Buffer Pool Information

Snapshot Level Table Space	Logical Data Grouping bufferpool	Monitor Switch Buffer Pool
Resettable	No
Event Type Table Space	Logical Data Grouping bufferpool_event
Element Name Element Type	bp_info information
Related Information	"Resetting Monitor Data"

Description: Data management counters for a buffer pool.

Usage: Activity performed for a buffer pool.

Bufferpool Name

Snapshot Level Table Space	Logical Data Grouping bufferpool	Monitor Switch Basic
Resettable	No
Element Name Element Type	bp_name information
Related Information	None

Description: The name of the buffer pool.

Usage: A new database has a default buffer pool called IBMDEFAULTBP with a size determined by the platform. Each database requires at least one buffer pool. However, depending on your needs you may choose to create several buffer pools, each of a different size, for a single database. The CREATE, ALTER, and DROP BUFFERPOOL statements allow you to create, change, or remove a buffer pool.

Time Waited for Prefetch

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Buffer Pool Buffer Pool
Resettable	No
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	prefetch_wait_time counter
Related Information	None

Description: The time an application spent waiting for an I/O server (prefetcher) to finish loading pages into the buffer pool.

Usage: This element can be used to experiment with changing the number of I/O servers, and I/O server sizes.

Extended Storage

Extended storage provides a secondary level of storage for bufferpools. This allows a user to access memory beyond the maximum allowed for each process. Extended storage consists of segments that will be allocated in addition to the bufferpools. The extended storage will assign pages to segments that are attached or detached, as needed. The number and size of segments are configurable. Attachment is allowed to only one segment at a given time.

There is one extended storage for all buffer pools, and each buffer pool can be configured to use it or not. See the Administration Guide for more information.

Extended storage should only be used on systems with very large amount of real memory. These are systems that have more memory than can be attached to by a single process.

Using Extended Storage Counters

If you have extended storage set on for a buffer pool, all pages removed from the buffer pool will be written to extended storage. Each of these writes has a cost associated with it. Some of these pages may never be required or they may be forced out of extended storage before they are ever read back into the buffer pool.

You can calculate the extended storage read/write ratio as follows:

     (data + index copied from extended storage)
     / (data + index copied to extended storage)

Where the numerator in this equation is pages from extended storage to buffer pool and the denominator is pages from bufferpool to extended storage.

The top portion of this equation represents a performance saving. When a page is transferred from extended storage to buffer pool, you save a system I/O call. However, you still incur the cost of attaching to the extended memory segment, copying the page, and detaching from the segment. The bottom part represents the cost of transferring a page to extended storage, that is, attaching to the segment, copying the page, and detaching.

The higher the ratio, the more likely you are to benefit from extended storage. In general, extended storage is particularly useful if I/O activity is very high on your system.

There is a crossover point where the cost of copying pages to be removed from the buffer pool to extended storage equals the savings from reading pages from extended storage, instead of having to read them from disk. This crossover point is affected by:

cost of an I/O on your system
cost of copying data in memory and accessing shared memory segments

It is difficult to establish an exact crossover point. To establish a baseline, you must experiment by enabling extended storage for different buffer pools, and determine whether it improves your overall database performance. This can be measured by using application benchmarks. For instance, you may want to monitor transaction rates and execution time. See the Administration Guide for information on benchmarking.

Once you have established that extended storage is beneficial for some buffer pools. You want to measure the read/write ratio to obtain a baseline. This ratio is most important during database creation and initial setup. After that, you want to monitor this ratio to ensure that it is not deviating from the initial baseline.

The following elements provide information about buffer pools and extended storage. For more information on how the database manager uses extended storage, see the Administration Guide.

Buffer Pool Data Pages to Extended Storage
Buffer Pool Index Pages to Extended Storage
Buffer Pool Data Pages from Extended Storage
Buffer Pool Index Pages from Extended Storage

Buffer Pool Data Pages to Extended Storage

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Connection Table Space	Logical Data Grouping db_event conn_event tablespace_event
Element Name Element Type	pool_data_to_estore counter
Related Information	"Resetting Monitor Data" "Buffer Pool Index Pages to Extended Storage" "Buffer Pool Data Pages from Extended Storage" "Buffer Pool Index Pages from Extended Storage"

Description: Number of buffer pool data pages copied to extended storage.

Usage: Pages are copied from the buffer pool to extended storage, when they are selected as victim pages. This copying is required to make space for new pages in the buffer pool.

Buffer Pool Index Pages to Extended Storage

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Connection Table Space	Logical Data Grouping db_event conn_event tablespace_event
Element Name Element Type	pool_index_to_estore counter
Related Information	"Resetting Monitor Data" "Buffer Pool Data Pages to Extended Storage" "Buffer Pool Data Pages from Extended Storage" "Buffer Pool Index Pages from Extended Storage"

Description: Number of buffer pool index pages copied to extended storage.

Usage: Pages are copied from the buffer pool to extended storage, when they are selected as victim pages. This copying is required to make space for new pages in the buffer pool.

Buffer Pool Data Pages from Extended Storage

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Connection Table Space	Logical Data Grouping db_event conn_event tablespace_event
Element Name Element Type	pool_data_from_estore counter
Related Information	"Resetting Monitor Data" "Buffer Pool Data Pages to Extended Storage" "Buffer Pool Index Pages to Extended Storage" "Buffer Pool Index Pages from Extended Storage"

Description: Number of buffer pool data pages copied from extended storage.

Usage: Required pages are copied from extended storage to the buffer pool, if they are not in the buffer pool, but are in extended storage. This copying may incur the cost of connecting to the shared memory segment, but saves the cost of a disk read.

Buffer Pool Index Pages from Extended Storage

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Connection Table Space	Logical Data Grouping db_event conn_event tablespace_event
Element Name Element Type	pool_index_from_estore counter
Related Information	"Resetting Monitor Data" "Buffer Pool Data Pages to Extended Storage" "Buffer Pool Index Pages to Extended Storage" "Buffer Pool Data Pages from Extended Storage"

Description: Number of buffer pool index pages copied from extended storage.

Usage: Required index pages are copied from extended storage to the buffer pool, if they are not in the buffer pool, but are in extended storage. This copying may incur the cost of connecting to the shared memory segment, but saves the cost of a disk read.

Non-buffered I/O Activity

The following elements provide information about I/O activity that does not use the buffer pool:

Direct Reads From Database
Direct Writes to Database
Direct Read Requests
Direct Write Requests
Direct Read Time
Direct Write Time

Direct Reads From Database

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Connection Table Space	Logical Data Grouping db_event conn_event tablespace_event
Element Name Element Type	direct_reads counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Direct Read Requests" "Direct Read Time" "Direct Writes to Database"

Description: The number of read operations that do not use the buffer pool.

Usage: Use the following formula to calculate the average number of sectors that are read by a direct read:

 
 direct reads from database / direct read requests

When using system monitors to track I/O, this data element helps you distinguish database I/O from non-database I/O on the device.

Direct reads are performed in units, the smallest being a 512-byte sector. They are used when:

Reading LONG VARCHAR columns
Reading LOB (large object) columns
Performing a backup

Direct Writes to Database

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Connection Table Space	Logical Data Grouping db_event conn_event tablespace_event
Element Name Element Type	direct_writes counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Direct Write Requests" "Direct Write Time" "Direct Reads From Database"

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

Usage: Use the following formula to calculate the average number of sectors that are written by a direct write.

 
 direct writes to database / direct write requests

When using system monitors to track I/O, this data element helps you distinguish database I/O from non-database I/O on the device.

Direct writes are performed in units, the smallest being a 512-byte sector. They are used when:

Writing LONG VARCHAR columns
Writing LOB (large object) columns
Performing a restore
Performing a load.

Direct Read Requests

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Connection Table Space	Logical Data Grouping db_event conn_event tablespace_event
Element Name Element Type	direct_read_reqs counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Direct Reads From Database" "Direct Read Time" "Direct Write Requests"

Description: The number of requests to perform a direct read of one or more sectors of data.

Usage: Use the following formula to calculate the average number of sectors that are read by a direct read:

 
 direct reads from database / direct read requests

Direct Write Requests

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Connection Table Space	Logical Data Grouping db_event conn_event tablespace_event
Element Name Element Type	direct_write_reqs counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Direct Writes to Database" "Direct Write Time" "Direct Read Requests"

Description: The number of requests to perform a direct write of one or more sectors of data.

Usage: Use the following formula to calculate the average number of sectors that are written by a direct write:

 
 direct writes to database / direct write requests

Direct Read Time

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Connection Table Space	Logical Data Grouping db_event conn_event tablespace_event
Element Name Element Type	direct_read_time counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Direct Reads From Database" "Direct Read Requests" "Direct Write Time"

Description: The elapsed time (in milliseconds) required to perform the direct reads.

Usage: Use the following formula to calculate the average direct read time per sector:

 
 direct read time / direct reads from database

A high average time may indicate an I/O conflict.

Direct Write Time

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace bp_info appl	Monitor Switch Buffer Pool Buffer Pool Buffer Pool Buffer Pool
Resettable	Yes
Event Type Database Connection Table Space	Logical Data Grouping db_event conn_event tablespace_event
Element Name Element Type	direct_write_time counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Direct Writes to Database" "Direct Write Requests" "Direct Read Time"

Description: The elapsed time (in milliseconds) required to perform the direct writes.

Usage: Use the following formula to calculate the average direct write time per sector:

 
 direct write time / direct writes to database

A high average time may indicate an I/O conflict.

Catalog Cache

The catalog cache stores table descriptors for tables, views, and aliases. A descriptor stores information about a table, view, or alias in a condensed internal format. When a transaction references a table, it causes an insert of a table descriptor into the cache, so that subsequent transactions referencing that same table can use that descriptor and avoid reading from disk. (Transactions reference a table descriptor when compiling an SQL statement.)

The following database system monitor elements are used for catalog caches:

Catalog Cache Lookups
Catalog Cache Inserts
Catalog Cache Overflows
Catalog Cache Heap Full

Catalog Cache Lookups

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	cat_cache_lookups counter
Related Information	"Catalog Cache Inserts" "Catalog Cache Overflows" "Catalog Cache Heap Full"

Description: The number of times that the catalog cache was referenced to obtain table descriptor information.

Usage: This element includes both successful and unsuccessful accesses to the catalog cache. The catalog cache is referenced whenever a table, view, or alias name is processed during the compilation of an SQL statement.

To calculate the catalog cache hit ratio use the following formula:

 
  (1 - (cat_cache_inserts / cat_cache_lookups))

indicates how well the catalog cache is avoiding catalog accesses. If the ratio is high (more than 0.8), then the cache is performing well. A smaller ratio might suggest that the catalogcache_sz should be increased. You should expect a large ratio immediately following the first connection to the database.

The execution of Data Definition Language (DDL) SQL statements involving a table, view, or alias will evict the table descriptor information for that object from the catalog cache causing it to be re-inserted on the next reference. Therefore, the heavy use of DDLs may also increase the ratio.

See the Administration Guide for more information on the Catalog Cache Size configuration parameter.

Catalog Cache Inserts

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	cat_cache_inserts counter
Related Information	"Catalog Cache Lookups" "Catalog Cache Overflows" "Catalog Cache Heap Full" "Data Definition Language (DDL) SQL Statements"

Description: The number of times that the system tried to insert table descriptor information into the catalog cache.

Usage: Table descriptor information is usually inserted into the cache following a failed lookup to the catalog cache while processing a table, view, or alias reference in an SQL statement. The catalog cache inserts value includes attempts to insert table descriptor information that fail due to catalog cache overflow and heap full conditions.

See Catalog Cache Lookups for more catalog cache information.

Catalog Cache Overflows

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	cat_cache_overflows counter
Related Information	"Catalog Cache Lookups" "Catalog Cache Inserts"

Description: The number of times that an insert into the catalog cache failed due the catalog cache being full.

Usage: The catalog cache space is filled with table descriptor information.

The cache entries for transactions that compile SQL statements, either by issuing dynamic SQL statements or by binding a package, will not be eligible to be removed from the cache until that transaction has either been committed or rolled back. Catalog cache space is reclaimed by evicting table descriptor information for tables, views, or aliases that are not currently in use by any transaction. Once a transaction has experienced a catalog cache overflow, all subsequent attempts by the same transaction to insert table descriptor information into the catalog cache will also result in an overflow.
Note: A transaction involved in an overflow will proceed, but its descriptor information is not inserted into the cache.

If catalog cache overflows is large, the catalog cache may be too small for the workload. Enlarging the catalog cache may improve its performance. If the workload includes transactions which compile a large number of SQL statements referencing many tables, views, and aliases in a single unit of work, then compiling fewer SQL statements in a single transaction may improve the performance of the catalog cache. Or if it includes binding of packages containing many SQL statements referencing many tables, views or aliases, you can try splitting packages so that they include fewer SQL statements to improve performance.

Catalog Cache Heap Full

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	cat_cache_heap_full counter
Related Information	"Package Cache Inserts" "Data Definition Language (DDL) SQL Statements" "Dynamic SQL Statements Attempted" "Static SQL Statements Attempted"

Description: The number of times that an insert into the catalog cache failed due to a heap-full condition in the database heap.

Usage: The catalog cache draws its storage dynamically from the database heap and even if the cache storage has not reached its limit, inserts into the catalog cache may fail due to a lack of space in the database heap.

If the catalog cache heap full count is not zero, then this insert failure condition can be corrected by increasing the database heap size or reducing the catalog cache size.

Package Cache

The package and section information required for the execution of dynamic and static SQL statements are placed in the package cache as required. This information is required whenever a dynamic or static statement is being executed. The package cache exists at a database level. This means that agents with similar environments can share the benefits of another agent's work. For static SQL statements, this can mean avoiding catalog access. For dynamic SQL statements, this can mean avoiding the cost of compilation.

The following database system monitor elements are used for package caches:

Package Cache Lookups
Package Cache Inserts
Package Cache Overflows
Maximum Package Cache Size
Section Lookups
Section Inserts

Package Cache Lookups

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	pkg_cache_lookups counter
Related Information	"Package Cache Inserts" "Section Lookups" "Section Inserts" "Static SQL Statements Attempted" "Dynamic SQL Statements Attempted" "Data Definition Language (DDL) SQL Statements"

Description: The number of times that an application looked for a section or package in the package cache. At a database level, it indicates the overall number of references since the database was started, or monitor data was reset.
Note: This counter includes the cases where the section is already loaded in the cache and when the section has to be loaded into the cache.

Usage: To calculate the package cache hit ratio use the following formula:

 
    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 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 data 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 package cache inserts. This experimentation is best done under full workload conditions.

You can use this data 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 extra overhead incurred when preparing those sections could significantly impact performance. In this case, the package cache hit ratio reflects the implicit recompilation of invalid sections and not 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 on the quantity and type of sections being cached.

See the Administration Guide for more information on the Package Cache Size (pckcachesz) configuration parameter.
Note: You may want to use this information at the database level to calculate the average package cache hit ratio all each applications. You should look at this information at an application level to find out the exact package cache hit ratio for a given application. It may not be worthwhile to increase the size of the package cache in order to satisfy the cache requirements of an application that only executes infrequently.

Package Cache Inserts

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	pkg_cache_inserts counter
Related Information	"Package Cache Lookups" "Section Lookups" "Section Inserts"

Description: The total number of times that a requested section was not available for use and had to be loaded into the package cache. This count includes any implicit prepares performed by the system.

Usage: In conjunction with "Package Cache Lookups", you can calculate the package cache hit ratio using the following formula:

 
    1 - (Package Cache Inserts / Package Cache Lookups)

See Package Cache Lookups for information on using this element.

Package Cache Overflows

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Basic
Resettable	Yes
Event Type Database	Logical Data Grouping db_event
Element Name Element Type	pkg_cache_num_overflows counter
Related Information	"Package Cache Inserts" "Data Definition Language (DDL) SQL Statements" "Dynamic SQL Statements Attempted" "Static SQL Statements Attempted"

Description: The number of times that the package cache overflowed the bounds of its allocated memory.

Usage: Use this element with pkg_cache_size_top to determine whether the size of the package cache needs to be increased to avoid overflowing. Overflows of the package cache can cause unnecessary lock escalations, resulting in loss of concurrency, or out of memory errors from the other heaps allocated out of the database shared memory, as well as performance degradation.

Maximum Package Cache Size

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Basic
Resettable	No
Event Type Database	Logical Data Grouping db_event
Element Name Element Type	pkg_cache_size_top water mark
Related Information	"Package Cache Overflows"

Description: The largest size reached by the package cache.

Usage: This element indicates the maximum number of bytes the package cache required for the workload run against the database since it was activated.

If the package cache overflowed, then this element contains the largest size reached by the package cache during the overflow. Check Package Cache Overflows to determine if such a condition occurred.

When the package cache overflows, memory is temporarily borrowed from other entities in database shared memory (for example, lock list or database heap). This can result in memory shortage errors from these entities or performance degradation from concurrency reduction due to unnecessary lock escalations. You can determine the minimum size of the package cache required by your workload by:

   maximum package cache size / 4096

Rounding the result up to a whole number, indicates the minimum number of 4K pages required by the package cache to avoid overflow.

Section Lookups

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	appl_section_lookups counter
Related Information	"Package Cache Lookups" "Package Cache Inserts" "Section Inserts"

Description: Lookups of SQL sections by an application from its SQL work area.

Usage: Each agent has access to a unique SQL work area where the working copy of any executable section is kept. In partitioned databases, this work area is shared by all non-SMP agents. In other environments and with SMP agents, each agent has its own unique SQL work area.

This counter indicates how many times the SQL work area was accessed by agents for an application. It is a cumulative total of all lookups on all SQL work heaps for agents working for this application.

You can use this element in conjunction with Section Inserts to tune the size of the heap used for the SQL work area. In partitioned databases this size is controlled by the app_ctl_heap_sz configuration parameter. SQL work area size in other database environments uses the the applheapsz configuration parameter. The size of the SQL work area for SMP agents is controlled by applheapsz in all environments.

Section Inserts

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	appl_section_inserts counter
Related Information	"Package Cache Lookups" "Package Cache Inserts" "Section Lookups"

Description: Inserts of SQL sections by an application from its SQL work area.

Usage: The working copy of any executable section is stored in a unique SQL work area. This is a count of when a copy was not available and had to be inserted. See Section Lookups for more information on using sections.

Database Heap

The following database system monitor elements are used for database heaps:

Maximum Database Heap Allocated

Maximum Database Heap Allocated

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Basic
Resettable	No
Event Type Database	Logical Data Grouping db_event
Element Name Element Type	db_heap_top water mark
Related Information	None

Description: The largest amount of database heap allocated and used by the database, since the first application connected to the database (in bytes).

Usage: You may use this element to evaluate the setting of the dbheap configuration parameter, which is described in the Administration Guide. The dbheap parameter limits the amount of storage that can be allocated for database heap.

If the value of this element is the same as the dbheap parameter, it is quite likely that an application has received an error indicating that there was not enough storage available.

Logging

The following database system monitor elements are used only when circular logging is being used. That is, they are not used if either the logretain or userexit configuration parameter is enabled.

Maximum Secondary Log Space Used
Maximum Total Log Space Used
Secondary Logs Allocated Currently

The following database system monitor elements are used for all types of logging:

Number of Log Pages Read
Number of Log Pages Written
Unit of Work Log Space Used
Total Log Space Used
Total Log Available

For more information about logging and log configuration parameters, see the Administration Guide.

Maximum Secondary Log Space Used

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Basic
Resettable	No
Event Type Database	Logical Data Grouping db_event
Element Name Element Type	sec_log_used_top water mark
Related Information	"Unit of Work Log Space Used" "Secondary Logs Allocated Currently" "Maximum Total Log Space Used"

Description: The maximum amount of secondary log space used (in bytes).

Usage: You may use this element in conjunction with "Secondary Logs Allocated Currently" and "Maximum Total Log Space Used" to show your current dependency on secondary logs. If this value is high, you may need larger log files, or more primary log files, or more frequent COMMIT statements within your application.

As a result, you may need to adjust the following configuration parameters:

logfilsz
logprimary
logsecond
logretain

The value will be zero if the database does not have any secondary log files. This would be the case if there were none defined.

For more information, see the Administration Guide.
Note: While the database system monitor information is given in bytes, the configuration parameters are set in pages, which are each 4K bytes.

Maximum Total Log Space Used

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Basic
Resettable	No
Event Type Database	Logical Data Grouping db_event
Element Name Element Type	tot_log_used_top water mark
Related Information	"Unit of Work Log Space Used" "Secondary Logs Allocated Currently" "Maximum Secondary Log Space Used"

Description: The maximum amount of total log space used (in bytes).

Usage: You can use this element to help you evaluate the amount of primary log space that you have allocated. Comparing the value of this element with the amount of primary log space you have allocated can help you to evaluate your configuration parameter settings. Your primary log space allocation can be calculated using the following formula:

 
     logprimary x logfilsiz x 4096 (see note below)

You can use this element in conjunction with "Maximum Secondary Log Space Used" and "Secondary Logs Allocated Currently" to show your current dependency on secondary logs.

This value includes space used in both primary and secondary log files, and is only returned if circular logging is used. (That is, it is not returned if either the logretain or userexit configuration parameter is enabled.)

As a result, you may need to adjust the following configuration parameters:

logfilsz
logprimary
logsecond
logretain

Secondary Logs Allocated Currently

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Basic
Resettable	No
Element Name Element Type	sec_logs_allocated gauge
Related Information	"Unit of Work Log Space Used" "Maximum Secondary Log Space Used" "Maximum Total Log Space Used"

Description: The total number of secondary log files that are currently being used for the database.

Usage: You may use this element in conjunction with "Maximum Secondary Log Space Used" and "Maximum Total Log Space Used" to show your current dependency on secondary logs. If this value is consistently high, you may need larger log files, or more primary log files, or more frequent COMMIT statements within your application.

As a result, you may need to adjust the following configuration parameters:

logfilsz
logprimary
logsecond
logretain

For more information, see the Administration Guide.

Number of Log Pages Read

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Basic
Resettable	Yes
Event Type Database	Logical Data Grouping db_event
Element Name Element Type	log_reads counter
Related Information	"When Counters are Initialized" "Number of Log Pages Written"

Description: The number of log pages read from disk by the logger.

Usage: You can use this element with an operating system monitor to quantify the amount of I/O on a device that is attributable to database activity.

Number of Log Pages Written

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Basic
Resettable	Yes
Event Type Database	Logical Data Grouping db_event
Element Name Element Type	log_writes counter
Related Information	"When Counters are Initialized" "Number of Log Pages Read"

Description: The number of log pages written to disk by the logger.

Usage: You may use this element with an operating system monitor to quantify the amount of I/O on a device that is attributable to database activity.
Note: When log pages are written to disk, the last page might not be full. In such cases, the partial log page remains in the log buffer, and additional log records are written to the page. Therefore log pages might be written to disk by the logger more than once. You should not use this data element to measure the number of pages produced by DB2.

Unit of Work Log Space Used

Snapshot Level Application	Logical Data Grouping appl	Monitor Switch Unit of Work
Resettable	No
Event Type Transaction	Logical Data Grouping xaction_event
Element Name Element Type	uow_log_space_used gauge
Related Information	"Resetting Monitor Data" "Secondary Logs Allocated Currently" "Maximum Secondary Log Space Used" "Maximum Total Log Space Used"

Description: The amount of log space (in bytes) used in the current unit of work of the monitored application.

Usage: You may use this element to understand the logging requirements at the unit of work level.

Total Log Space Used

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Basic
Resettable	No
Element Name Element Type	total_log_used gauge
Related Information	"Unit of Work Log Space Used" "Secondary Logs Allocated Currently" "Maximum Total Log Space Used" "Application with Oldest Transaction"

Description: The total amount of active log space currently used (in bytes) in the database.

Usage: Use this element in conjunction with Total Log Available to determine whether you may need to adjust the following configuration parameters to avoid running out of log space:

logfilsz
logprimary
logsecond

Total Log Available

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Basic
Resettable	No
Element Name Element Type	total_log_available water mark
Related Information	"Unit of Work Log Space Used" "Secondary Logs Allocated Currently" "Total Log Space Used" "Application with Oldest Transaction"

Description: The amount of active log space in the database that is not being used by uncommitted transactions (in bytes).

Usage: Use this element in conjunction with Total Log Space Used to determine whether you may need to adjust the following configuration parameters to avoid running out of log space:

logfilsz
logprimary
logsecond

For more information, see the Administration Guide.

If this value goes down to 0, SQL0964N will be returned. You may need to increase the above configuration parameters, or end the oldest transaction by COMMIT, ROLLBACK or FORCE APPLICATION.
Note: While the database system monitor information is given in bytes, the configuration parameters are set in pages, which are each 4K bytes.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]