Several techniques of DB2 database performance optimization

The simplest and most effective--bufferpool

A buffer pool is a storage area in memory that is used to temporarily read and change database pages (including table rows or index entries). The purpose of the buffer pool is to improve the performance of the database system. Accessing data from memory is much faster than accessing data from disk. As a result, the fewer times the database manager needs to read from or write to the disk, the better the performance. Configuring one or more buffer pools is the most important aspect of tuning because most of the data (excluding large objects and long field data) for applications connected to the database is performed in the buffer pool.

By default, the application uses the buffer pool IBMDEFAULTBP, which is created when the database is created. When Syscat. When the npages value of the buffer pool in the Bufferpools directory table is-1, the DB2 database configuration parameter BUFFPAGE controls the size of the buffer pool. Otherwise, the BUFFPAGE parameter is ignored and the buffer pool is created with the number of pages specified by the Npages parameter.

It is recommended that you change the npages to 1 for applications that use only one buffer pool, so that BUFFPAGE can control the size of the buffer pool. This makes it easier to update and report buffer pool size and other DB2 database configuration parameters.

Make sure that you can use the BUFFPAGE parameter in the database configuration to control the buffer pool size, and then set the parameter to the appropriate value. It is safe to set this parameter to a reasonable large value based on the size of the database and the nature of the application. Typically, the default value for this parameter is very small and may not meet the requirements.

DB2 "Get snapshot to all Bufferpools"

In the snapshot output of a database snapshot or buffer pool snapshot, look for the following "logical reads" and "physical reads" so that the buffer pool hit ratio can be computed to help tune the buffer pool:

The buffer pool hit ratio indicates that the database manager does not need to load a page from the disk (that is, the page is already in the buffer pool) to handle the percentage of the page request. The higher the hit ratio of the buffer pool, the lower the frequency of using disk I/O. Calculate the buffer pool hit ratio as follows:

(1-(Buffer pool data physical reads + buffer pool Index physical reads)/

(Buffer pool data logical reads + Pool index logical reads))

) * 100%

This calculation takes into account all the pages (indexes and data) of the buffer pool cache. Ideally, the ratio should be over 95% and as close to 100% as possible. To increase the buffer pool hit ratio, try the following methods:

1, increase the buffer pool size.

2. Consider allocating multiple buffer pools, if possible, allocating a buffer pool for each table space to which the large table is frequently accessed, assigning a buffer pool to a small set of tables, and then try using a different size buffer pool to see which combination provides optimal performance.

3. If the allocated memory does not help improve performance, avoid allocating too much memory to the buffer pool. The size of the buffer pool should be determined based on the snapshot information taken from the test environment.

4, too small buffer pool will produce excessive, unnecessary physical I/O. Too large a buffer pool causes the system to be at risk of operating system page scheduling and consumes unnecessary CPU cycles to manage overallocated memory. The right buffer pool size is on a balance between "too small" and "too big". The appropriate size exists at the point where the return will begin to decrease.

--sql to get the best performance

A bad SQL statement can completely destroy everything. A relatively simple SQL statement can also mess up a well tuned database and machine. For many of these statements, there is no DB2 UDB configuration parameter at the bottom of the day (or in the file) to correct the high cost of the bad SQL statements.

Worse, DBAs are often constrained by the fact that SQL cannot be changed (possibly because it is provided by the application vendor). This leaves the DBA with only three paths to go:

1. Change or add index

2. Change the cluster

3. Change Catalog Statistics

A robust application consists of thousands of different SQL statements. The frequency with which these statements are executed varies depending on the functionality of the application and the day-to-day business needs. The actual cost of an SQL statement is the cost it performs once, multiplied by the number of times it executes.

The major task facing each DBA is to identify the challenges of statements with the highest "real cost" and to reduce the cost of these statements.

The resource cost of executing an SQL statement can be calculated through the native DB2 Explain utility, some third-party vendor-supplied tools, or DB2 UDB SQL Event Monitor data. However, the frequency of statement execution can only be understood through careful and time-consuming analysis of data from DB2 UDB SQL Event Monitor.

Optimal performance requires not only the removal of High-cost SQL statements, but also the need to ensure that the appropriate physical infrastructure is appropriate. The best performance can be achieved when all the adjustment knobs are set properly, the memory is effectively allocated to the pool and the heap, and I/O is evenly distributed to each disk.